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.t.wilson@xxxxxxxxx
