Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 944EC47633C for ; Wed, 9 Oct 2002 09:47:24 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 62EA547623F for ; Wed, 9 Oct 2002 09:47:19 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g99DlMhR019821; Wed, 9 Oct 2002 09:47:23 -0400 (EDT) To: Adam Witney Cc: pgsql-sql Subject: Re: Complex SQL query and performance strategy In-reply-to: References: Comments: In-reply-to Adam Witney message dated "Wed, 09 Oct 2002 11:32:18 +0100" Date: Wed, 09 Oct 2002 09:47:22 -0400 Message-ID: <19820.1034171242@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/123 X-Sequence-Number: 9840 Adam Witney 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