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, 

><[EMAIL PROTECTED]> 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 
>> 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 
>> 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

I'm using Vodafone Mail - to get your free mobile email account go to
Use of Vodafone Mail is subject to Terms and Conditions

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to