Why is row_number () faster than using offset?

Question

While experimenting a bit with window functions in PostgreSQL 9.3, I came across a rather interesting case. In direct conflict with the answers to OFFSET vs. ROW_NUMBER () I found that window functions are actually faster than OFFSET.

Using offset takes ~ 2500 ms:

select part_no, description
from inventory
order by part_no
limit 1000 offset 400000

Using row_number (), takes ~ 450ms:

select *
from (select part_no, description, row_number() OVER () 
    from inventory
    order by part_no) AS ss
where row_number >= 400001
limit 1000

This (recently analyzed) table contains about 450,000 rows, and part_no is indexed. EXPLAIN indicates that index scans are performed in case of row_number () and sequential scans in case of OFFSET.

OFFSET, row_number() vs unindexed . ( ).

            -------indexed-------    ------unindexed------
offset by   OFFSET   row_number()    OFFSET   row_number()
==========================================================
400000      2500ms          450ms     500ms          650ms
40000         80ms           60ms     850ms          650ms
4000          30ms           30ms     390ms          650ms

, ; - , ( + )?

Answer 1

. :

row_number() OVER (ORDER BY part_no)

. ORDER BY . :

SELECT part_no, description
FROM  (
   SELECT part_no, description, row_number() OVER (ORDER BY part_no) AS rn
   FROM   inventory) AS ss
WHERE  rn > 400000
ORDER  BY rn
LIMIT  1000;

:

SELECT part_no, description
FROM  (
   SELECT part_no, description, row_number() OVER (ORDER BY part_no) AS rn
   FROM   inventory) AS ss
WHERE  rn BETWEEN 400000 AND 401000
ORDER  BY rn;

, , , 4 , Postgres . , 9,3? ...