select distinct and index usage
| От | David Wilson | 
|---|---|
| Тема | select distinct and index usage | 
| Дата | |
| Msg-id | e7f9235d0804061632s4e06b14dt665108428cacff19@mail.gmail.com обсуждение исходный текст | 
| Ответы | Re: select distinct and index usage Re: select distinct and index usage Re: select distinct and index usage | 
| Список | 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.t.wilson@gmail.com
		
	В списке pgsql-general по дате отправления: