Re: join over 12 tables takes 3 secs to plan

Hilmar Lapp said:
> I have a query generated by an application (not mine, but there's
> nothing I can find that looks bad about the query itself) that takes an
> excessive amount of time to return even though there are almost no rows
> in the schema yet.

Yes -- an exhaustive search to determine the correct join order for a
multiple relation query is similar to solving the traveling salesman
problem (only more difficult, due to the availability of different join
algorithms, etc.). GEQO should be faster than the default optimizer for
large queries involving large numbers of joins, but it's still going to
take a fair bit of time.

In other words, it's not a surprise that a 12-relation join takes a little
while to plan.

> I'm running Postgres 7.3.1 on Mac OSX.

Tom recently checked in some optimizations for GEQO in CVS HEAD, so you
could try using that (or at least testing it, so you have an idea of what
7.4 will perform like).

You could also try using prepared queries.

Finally, there are a bunch of GEQO tuning parameters that you might want
to play with. They should allow you to reduce the planning time a bit, in
exchange for possibly generating an inferior plan.

Cheers,

Neil