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