Message-ID: <16345223.1117808642877.JavaMail.tomcat@iecsai19>
Date: Fri, 3 Jun 2005 15:24:02 +0100 (IST)
From: <philb@vodafone.ie>
Reply-To: <philb@vodafone.ie>
To: pgsql-performance@postgresql.org
Subject: Re: Query plan for very large number of joins
Cc: tgl@sss.pgh.pa.us
Mime-Version: 1.0
Content-Type: text/plain;charset="UTF-8"
Content-Transfer-Encoding: 7bit


Anyone following this thread might be interested to know that disabling 
the merge and hash joins (as suggested below) resulted in the execution 
time dropping from ~90 seconds to ~35 seconds. Disabling GEQO has brought 
about a marginal reduction (~1 second, pretty much within the the margin 
of error)

Tom, a quick grep indicates that all of the joins are left joins so there's no 
scope for tweaking there. I'll send you the schema + query offlist, anyone 
else curious about it, let me know. 

Thanks again, 
 -phil


><philb@vodafone.ie> writes:
>> 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.
>
>Only in very recent JDBCs does prepareStatement do much of anything.
>
>> So, is it correct to interpret this as the query planner taking an
>> awful long time?
>
>Looks that way.
>
>> 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).
>
>There's no positive forcing method.  But you could probably save some
>time by disabling both mergejoin and hashjoin, now that you know it's
>going to end up picking nestloop for each join anyway.  Even more
>important: are you sure that *every* one of the joins is a LEFT JOIN?
>Even a couple of regular joins will let it fool around choosing
>different join orders.
>
>> Alternatively, is there some way to check if the query planner is
>> bottlenecking on a specific resource?
>
>I think it would be interesting to try profiling it.  I'm not really
>expecting to find anything easily-fixable, but you never know.  From
>what you said before, the database is not all that large --- would
>you be willing to send me a database dump and the text of the query
>off-list?


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