Re: Query performanc issue - too many table?

> 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.

It's not the merge join's fault; it's a question of a poorly chosen join
order. I'm kinda surprised that GEQO didn't manage to find a better
one, but your example shows that its odds of doing so aren't very good
in this example. (I wonder whether GEQO shouldn't be augmented with
some heuristics, so that it pays some attention to which relations have
WHERE-clause links to which other ones, or restriction clauses that
would make them useful to visit first. Right now I think it's a pretty
blind search...)

> 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?

I wouldn't shut it off. I would suggest raising the geqo_threshold a
little bit, if you do a lot of 11-table joins, and you don't find the
planning time unreasonable at 11 tables.

Another possibility is to use explicit JOIN syntax to constrain the join
order into a good one; that would save you planning time as well. See
the manual.

regards, tom lane