Please excuse the attachment but these EXPLAIN ANALYSE were getting so
wide, email was making it hard to fight word-wrap.
The attached output is from 3 consecutive EXPLAIN ANALYSEs of the exact
same query. Each has a merge join floating somewhere within the query.
This has to be the culprit in terms of performance as this should be a
straightforward (albeit lengthy) step ladder keyed join query. The
question then becomes why chose to do merge.
Based on Tom's comment about GEQC, we then did a "set geqc to false" and
ran the query again and got great results. They too are included in the
log.
So, is GEQC broken or just misconfigured on our box? If the latter, what
is the proper config? We've made no changes from the default settings? If
the former, can I simply shut it off? Is the only time this comes into
play equate to the number of times I see the debug message appear in the
postmaster log?
Marc
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Marc Mitchell" <marcm@eisolution.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, November 21, 2002 1:45 PM
Subject: Re: [ADMIN] Query performanc issue - too many table?
> "Marc Mitchell" <marcm@eisolution.com> writes:
> > I am having a problem with the below SQL taking an extreme amount of
time
> > to run. The problem is that the explain looks great with all index
scans.
> > But the query itself takes minutes to run. The query contains 11
tables.
> > We've found that by dropping any one table, performance reverts to
being
> > nearly instantaneous.
>
> 11 tables is the default GEQO threshold, so I'm wondering if the GEQO
> planner is missing the best plan. It's hard to tell much though without
> seeing plans for *both* queries you are comparing. EXPLAIN ANALYZE
> output would be much more useful than just EXPLAIN, too.
>
> regards, tom lane