From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adam Witney <awitney@sghms.ac.uk>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Complex SQL query and performance strategy
Date: Wed, 09 Oct 2002 09:47:22 -0400
Message-ID: <19820.1034171242@sss.pgh.pa.us> (raw)
In-Reply-To: <B9C9C442.9324%a.witney@sghms.ac.uk>
References: <B9C9C442.9324%a.witney@sghms.ac.uk>
Adam Witney <awitney@sghms.ac.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, awitney@sghms.ac.uk
Subject: Re: Complex SQL query and performance strategy
In-Reply-To: <19820.1034171242@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox