Re: select distinct and index usage
От | Alban Hertroys |
---|---|
Тема | Re: select distinct and index usage |
Дата | |
Msg-id | CEA72FEB-BF7D-4665-A69B-55509073B8C9@solfertje.student.utwente.nl обсуждение исходный текст |
Ответ на | select distinct and index usage ("David Wilson" <david.t.wilson@gmail.com>) |
Ответы |
Re: select distinct and index usage
|
Список | pgsql-general |
On Apr 7, 2008, at 1:32 AM, David Wilson wrote: > 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) The databases estimates seem consistent with yours, so why is it doing this? Could you provide an EXPLAIN ANALYSE? It shows the actual numbers next to the estimates, although I figure that query might take a while... Pg estimates the costs quite high too. It's almost as if there isn't an index on that column and it has no other way then doing a sequential scan... Could you show us the table definition and its indexes? What version of Pg is this? It may be that your index on vals.datestamp doesn't fit into memory; what are the relevant configuration parameters for your database? Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47f9b995927662100729983!
В списке pgsql-general по дате отправления: