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/