From: philb@vodafone.ie
To: tgl@sss.pgh.pa.us
Cc: pgsql-performance@postgresql.org
Subject: Re: Query plan for very large number of joins
Date: Fri, 3 Jun 2005 13:22:41 +0100 (IST)
Message-ID: <189960.1117801361979.JavaMail.tomcat@iecsai19> (raw)
>>> 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.
[snip]
>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.
Thanks for the suggestion. I've timed both the EXPLAIN and the EXPLAIN ANALYZE operations.
Both operations took 1m 37s. The analyze output indicates that the query
execution time was 950ms. This doesn't square with the JDBC prepareStatement
executing in 36ms. My guess is that the prepare was actually a no-op but
I haven't found anything about this yet.
So, is it correct to interpret this as the query planner taking an awful long
time? Is it possible to force the query planner to adopt a specific strategy
and not search for alternatives (I'm aware of the noXX options, it's the
reverse logic that I'm thinking of here). Alternatively, is there some way
to check if the query planner is bottlenecking on a specific resource?
Finally, PFC was asking about the nature of the application, it's not a
specific application just a generic bit of infrastructure consisting of
a transformation of the UBL schema. Despite being fairly restricted in scope,
the schema is highly denormalized hence the large number of tables.
Thanks for all your help.
-phil
I'm using Vodafone Mail - to get your free mobile email account go to http://www.vodafone.ie
Use of Vodafone Mail is subject to Terms and Conditions http://www.vodafone.ie/terms/website
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: philb@vodafone.ie, tgl@sss.pgh.pa.us
Subject: Re: Query plan for very large number of joins
In-Reply-To: <189960.1117801361979.JavaMail.tomcat@iecsai19>
* 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