Re: index on large table

On Tue, 12 Mar 2002, Kacper Nowicki wrote:

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

What gets shown for explain with set enable_seqscan=off? If you're using
7.2, try explain analyze both ways as well.

The row grabbing with the index would be slower than the sequence scan,
but most of the cost seems to be going into the sort. Another thing to
try would be raising sort_mem I guess.