Re: select distinct and index usage

"David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com> writes:

> I appreciate the responses so far! I'm used to several minutes for
> some of the complex queries on this DB, but 12.5 minutes for a select
> distinct just seems wrong. :)

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!