|
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 |
