Search Postgresql Archives

Re: select distinct and index usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux