From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: philb@vodafone.ie
Cc: pgsql-performance@postgresql.org
Subject: Re: Query plan for very large number of joins
Date: Thu, 02 Jun 2005 12:26:26 -0400
Message-ID: <14834.1117729586@sss.pgh.pa.us> (raw)
In-Reply-To: <429F2D94.3030805@archonet.com>
References: <17165144.1117725925709.JavaMail.tomcat@iecsai19>
<429F2D94.3030805@archonet.com>
Richard Huxton <dev@archonet.com> writes:
> philb@vodafone.ie wrote:
>> I am using PostgreSQL (7.4) with a schema that was generated
>> automatically (using hibernate). The schema consists of about 650
>> relations. One particular query (also generated automatically)
>> consists of left joining approximately 350 tables.
> May I be the first to offer an "ouch"!
Seconded.
> However, I'm not sure how much leeway there is in
> planning a largely left-joined query.
Not much. The best hope for a better result is to order the LEFT JOIN
clauses in a way that will produce a good plan.
One thought is that I am not sure I believe the conclusion that planning
is taking only 36 ms; even realizing that the exclusive use of left
joins eliminates options for join order, there are still quite a lot of
plans to consider. You should try both EXPLAIN and EXPLAIN ANALYZE
from psql and see how long each takes. It'd also be interesting to keep
an eye on how large the backend process grows while doing this --- maybe
it's being driven into swap.
Also: I'm not sure there *is* such a thing as a good plan for a 350-way
join. It may be time to reconsider your data representation. If
Hibernate really forces this on you, it may be time to reconsider your
choice of tool.
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, dev@archonet.com, philb@vodafone.ie
Subject: Re: Query plan for very large number of joins
In-Reply-To: <14834.1117729586@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