[GENERAL] query with offset stops using index scan

Hello everybody,

 

I have some weird behaviour with a pretty simple query, which I use in a web front end to browse through pages of data.

 

SELECT

            foo.id, get_processing_status(foo.id) AS status, foo.name, foo.valid_until

FROM

            foo

 WHERE foo.active  AND foo.valid_until < 1220186528 AND NOT foo.locked

ORDER BY foo.id DESC

LIMIT  25

OFFSET 100

 

This very query works quite quickly, and the query plan looks like this:

 

"Limit  (cost=36.04..45.05 rows=25 width=63)"

"  ->  Index Scan Backward using foo_pkey on foo  (cost=0.00..511.35 rows=1419 width=63)"

"        Filter: (active AND (valid_until < 1220186528) AND (NOT locked))"

 

Now, if I increase OFFSET slowly, it works all the same way, until OFFSET reaches the value of 750. Then, the planner refuses to use an index scan and does a plain seq scan+sort, which makes the query about 10-20 times slower:

 

"Limit  (cost=272.99..273.05 rows=25 width=63)"

"  ->  Sort  (cost=271.11..274.66 rows=1419 width=63)"

"        Sort Key: id"

"        ->  Seq Scan on foo (cost=0.00..196.82 rows=1419 width=63)"

"              Filter: (active AND (valid_until < 1220186528) AND (NOT locked))"

 

I use 8.1.4, and I did a vacuum full analyze before running the queries.

 

What might be the issue here? Could a reindex on the pkey help?

 

Kind Regards

 

Stanisalv Raskin