Re: 8.1 count(*) distinct: IndexScan/SeqScan

Steinar H. Gunderson wrote:
> On Thu, Nov 24, 2005 at 09:15:44PM -0600, Kyle Cordes wrote:
> > I have hit cases where I have a query for which there is a somewhat
> > "obvious" (to a human...) query plan that should make it possible to get
> > a query answer pretty quickly. Yet the query "never" finishes (or
> > rather, after hours of waiting I finally kill it). I assume this is
> > because of a sub-optimal query plan. But, it appears that an EXPLAIN
> > ANALYZE runs the actual query, so it takes as long as the actual query.
>
> In this case, you probably can't do better than EXPLAIN. Look at the
> estimates, find out if the cost is way high somewhere.

Also you want to make absolutely sure all the involved tables have been
ANALYZEd recently.

If you have weird cases where there is an obvious query plan and the
optimizer is not using it, by all means submit it so that developers can
take a look at how to improve the optimizer.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.