From: | <philb(at)vodafone(dot)ie> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Cc: | tgl(at)sss(dot)pgh(dot)pa(dot)us |
Subject: | Re: Query plan for very large number of joins |
Date: | 2005-06-03 14:24:02 |
Message-ID: | 16345223.1117808642877.JavaMail.tomcat@iecsai19 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)vodafone(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2005-06-03 14:49:51 | Re: BUG #1697: Select getting slower on continously updating data |
Previous Message | Martin Fandel | 2005-06-03 13:52:00 | Re: Filesystem |