Skip site navigation (1) Skip section navigation (2)

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

From: "Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: 8.1 count(*) distinct: IndexScan/SeqScan
Date: 2005-11-25 11:40:17
Message-ID: 20051125114017.GA10575@uio.no (view raw or flat)
Thread:
Lists: pgsql-performance
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. If a simple query
estimates a billion disk page fetches, something is probably wrong, ie. the
planner did for some reason overlook the query plan you were thinking of. (A
common problem here used to include data type mismatches leading to less
efficient joins, lack of index scans and less efficient IN/NOT IN; most of
that is fixed, but a few cases still remain.)

If the query is estimated at a reasonable amount of disk page fetches but
still takes forever, look at the number of estimated rows returned. Do they
make sense? If you run subsets of your query, are they about right? If not,
you probably want to fiddle with the statistics targets.

/* Steinar */
-- 
Homepage: http://www.sesse.net/

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2005-11-25 12:32:00
Subject: Re: 8.1 count(*) distinct: IndexScan/SeqScan
Previous:From: Tom LaneDate: 2005-11-25 04:59:26
Subject: Re: xlog flush request error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group