Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id AAA55693
	for <pgsql-interfaces@postgreSQL.org>;
	Sat, 27 Nov 1999 00:37:47 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id AAA17645;
	Sat, 27 Nov 1999 00:36:41 -0500 (EST)
To: dougt@mugc.cc.monash.edu.au
cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Slow join query optimisation? 
In-reply-to: Your message of Sat, 27 Nov 1999 13:12:54 +1100 (EST)
	<199911270212.NAA10098@mugca.cc.monash.edu.au>
Date: Sat, 27 Nov 1999 00:36:41 -0500
Message-ID: <17643.943681001@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Douglas Thomson <dougt@mugc.cc.monash.edu.au> writes:
> My question for the list: Is there something in the query optimiser
> that will take a time that is something like exponential on the number
> of tables being joined?

Yes.  See the manual's discussion of genetic query optimization.  You
might try setting the GEQO threshold to something less than 10.

FWIW, the time you are looking at here is strictly planning time and
will not increase when you actually put data in the tables.  Unless
you foresee fairly small final tables, the planning time is not likely
to be a significant factor in this range of query sizes.

Still, if the purpose of the join is just to substitute descriptions
for IDs, you'd be well advised to consider doing it via table lookup
on the frontend side, assuming your frontend is coded in a language
that makes that reasonable to do.  The Postgres optimizer cannot
help but waste many cycles per query reverse-engineering your intent,
even assuming that it ultimately comes up with the best possible
query plan for the join...

			regards, tom lane