Re: select distinct and index usage - Mailing list pgsql-general

On Mon, Apr 7, 2008 at 7:57 PM, Gregory Stark <stark@enterprisedb.com> wrote:
>
>  You could try changing it to the equivalent GROUP BY query. The planner,
>  unfortunately, doesn't know they're equivalent and has two separate sets of
>  plans available. In this case where there are only 4,000 distinct values out
>  of 75M original records you might find a HashAggregate plan, which the planner
>  doesn't know can be used for DISTINCT, best. You might have to raise work_mem
>  before the planner feels a hash will fit.
>
>  --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's RemoteDBA services!
>

Progress!

explain analyze select datestamp from vals group by datestamp;
                                                             QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1719740.40..1719783.03 rows=4263 width=4)
(actual time=120192.018..120193.930 rows=4252 loops=1)
   ->  Seq Scan on vals  (cost=0.00..1531261.72 rows=75391472 width=4)
(actual time=17.441..66807.429 rows=75391476 loops=1)
 Total runtime: 120195.144 ms


Compared with:

explain analyze select distinct datestamp from vals;
                                                              QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=15003047.47..15380004.83 rows=4263 width=4) (actual
time=649599.159..721671.595 rows=4252 loops=1)
  ->  Sort  (cost=15003047.47..15191526.15 rows=75391472 width=4)
(actual time=649599.157..694392.602 rows=75391476 loops=1)
        Sort Key: datestamp
        Sort Method:  external merge  Disk: 1178592kB
        ->  Seq Scan on vals  (cost=0.00..1531261.72 rows=75391472
width=4) (actual time=9.104..93130.468 rows=75391476 loops=1)
 Total runtime: 722379.434 ms

Still doing the sequential scan on the table, but at least it's
avoiding the expensive disk merge sort. It still seems as if I ought
to be able to coax it into using an index for this type of query,
though- especially since it's using one on the other table. Is there
perhaps some way to reformulate the index in such a way as to make it
more useful to the planner?

--
- David T. Wilson
Princeton Satellite Systems
david.t.wilson@gmail.com