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