From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: 8.1 count(*) distinct: IndexScan/SeqScan
Date: Fri, 25 Nov 2005 12:40:17 +0100
Message-ID: <20051125114017.GA10575@uio.no> (raw)
In-Reply-To: <438681E0.6070006@kylecordes.com>
References: <334D9941-B5B3-4D18-8312-F85D0FB054ED@rilk.com>
<A6F55E12-4479-49F6-B3EE-8CFCC8541179@rilk.com>
<17380.1132886273@sss.pgh.pa.us>
<438681E0.6070006@kylecordes.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgunderson@bigfoot.com
Subject: Re: 8.1 count(*) distinct: IndexScan/SeqScan
In-Reply-To: <20051125114017.GA10575@uio.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox