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