select distinct and index usage

From: "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: select distinct and index usage
Date: 2008-04-06 23:32:58
Message-ID: e7f9235d0804061632s4e06b14dt665108428cacff19@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a reasonably large table (~75m rows,~18gb) called "vals". It
includes an integer datestamp column with approximately 4000 unique
entries across the rows; there is a normal btree index on the
datestamp column. When I attempt something like "select distinct
datestamp from vals", however, explain tells me it's doing a
sequential scan:

explain select distinct datestamp from vals;
QUERY PLAN
--------------------------------------------------------------------------------------
Unique (cost=15003047.47..15380004.83 rows=4263 width=4)
-> Sort (cost=15003047.47..15191526.15 rows=75391472 width=4)
Sort Key: datestamp
-> Seq Scan on vals v (cost=0.00..1531261.72 rows=75391472 width=4)

On another table in the same database with a much smaller number of
total rows (~15m rows), I have the exact same situation- but in this
case the index on the datestamp column *is* used:

explain select distinct datestamp from sdays;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..974675.99 rows=4254 width=4)
-> Index Scan using sdays_datestamp_idx on sdays
(cost=0.00..932822.79 rows=16741280 width=4)

Any help on why the index isn't being used, or how I can set up the
index/query to make use of the index rather than doing an 18gb
sequential scan, would be very much appreciated.

--
- David T. Wilson
Princeton Satellite Systems
david(dot)t(dot)wilson(at)gmail(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Bog 2008-04-07 01:23:31 Re: pl/pgsql RECORD data type, how to access to the values
Previous Message Rudolph 2008-04-06 20:43:05 Preparing full text search input for use in PostgreSQL 8.3