index on large table

From: Kacper Nowicki <kacper(dot)nowicki(at)wp(dot)pl>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: index on large table
Date: 2002-03-12 22:06:31
Message-ID: 4.3.2.7.2.20020312135848.00e50a40@10.0.1.3
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2002-03-12 22:20:57 Re: cannot initdb
Previous Message Hunter Hillegas 2002-03-12 22:04:49 Re: Tuning 7.2? Different than 7.1.3?