From: Tom Lane <tgl@sss.pgh.pa.us>
To: dougt@mugc.cc.monash.edu.au
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Slow join query optimisation?
Date: Sat, 27 Nov 1999 00:36:41 -0500
Message-ID: <17643.943681001@sss.pgh.pa.us> (raw)
In-Reply-To: <199911270212.NAA10098@mugca.cc.monash.edu.au>
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
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-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, dougt@mugc.cc.monash.edu.au
Subject: Re: [INTERFACES] Slow join query optimisation?
In-Reply-To: <17643.943681001@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