Re: Complex SQL query and performance strategy

Adam Witney <awitney(at)sghms(dot)ac(dot)uk> writes:
> I have a complex SQL query which requires the joining of 18 tables. There
> are only primary key indices on the table and at the moment it runs a little
> slow (30s or so) and so I am trying to optimise it.

> The output of EXPLAIN is a little confusing and seems to vary from run to
> run. Does the query optimiser have trouble with larger number of table
> joins?

The output probably would vary, because at that number of tables it'll
be using the GEQO optimizer, which is probabilistic. If you don't like
that, you can raise the GEQO threshold above 18 tables, but I suspect
you'll not like the amount of time the exhaustive optimizer will take.

A reasonable solution is to jack up the threshold, experiment until you
find a good query plan, and then restructure the query with explicit
JOIN operators to limit the optimizer's search space. That will bring
the planning time down out of the stratosphere.

See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
for details.

regards, tom lane