I have a large table, with >1 million rows, which I would like to see page
by page. It is being displayed in the oid order.
Without any index a cost for such query is huge, since it is sequential
scan of about 300MB of data.
"explain select * from events order by oid limit 10 offset 0"
NOTICE: QUERY PLAN:
Limit (cost=69.83..69.83 rows=10 width=130)
-> Sort (cost=69.83..69.83 rows=1000 width=130)
-> Seq Scan on events (cost=0.00..20.00 rows=1000 width=130)
The cost is really higher, but the important fact is, that sequential scan
is required. Do the obvious: create an index:
"create index events_oid on events(oid)"
Now the same query is instantaneous, since index is used:
"explain select * from events order by oid limit 10 offset 0"
NOTICE: QUERY PLAN:
Limit (cost=0.00..38.56 rows=10 width=130)
-> Index Scan using events_oid on events (cost=0.00..3953085.63
rows=1025245 width=130)
so, let's see further down the table, the offset is shifted to 1M, we still
want to see just 10 entries.
"explain select * from events order by oid limit 10 offset 1000000"
NOTICE: QUERY PLAN:
Limit (cost=424863.54..424863.54 rows=10 width=130)
-> Sort (cost=424863.54..424863.54 rows=1025245 width=130)
-> Seq Scan on events (cost=0.00..35645.45 rows=1025245 width=130)
Bummer. This is very slow again, sequential scan again. Why the index is
not used for this query? Use of index would make it very fast!
Does anybody know what is going on?
Thanks,
Kacper