Re: select distinct and index usage

On Mon, Apr 7, 2008 at 7:57 PM, Gregory Stark <stark(at)enterprisedb(dot)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(dot)t(dot)wilson(at)gmail(dot)com