Re: Query plan for very large number of joins

Поиск
Список
Период
Сортировка
От
Тема Re: Query plan for very large number of joins
Дата
Msg-id 16345223.1117808642877.JavaMail.tomcat@iecsai19
обсуждение исходный текст
Ответ на Query plan for very large number of joins  (<philb@vodafone.ie>)
Список 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@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



В списке pgsql-performance по дате отправления:

Предыдущее
От: "Martin Fandel"
Дата:
Сообщение: Re: Filesystem
Следующее
От: Bruno Wolff III
Дата:
Сообщение: Re: BUG #1697: Select getting slower on continously updating data