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
