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 по дате отправления: