Re: select distinct and index usage

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!