Re: Joining 16 tables seems slow

2017-09-12 12:25 GMT+02:00 Frank Millman <frank(at)chagford(dot)com>:

> Pavel Stehule wrote:
>
> 2017-09-12 9:36 GMT+02:00 Frank Millman <frank(at)chagford(dot)com>:
>
>> Pavel Stehule wrote:
>> >
>> > 2017-09-12 8:45 GMT+02:00 Frank Millman <frank(at)chagford(dot)com>:
>>
>>> I am using 9.4.4 on Fedora 22.
>>>
>>> I am experimenting with optimising a SQL statement. One version uses 4
>>> LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the
>>> filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no
>>> CASE statements.
>>>
>>> My test involves selecting a single row. Both versions work. The first
>>> version takes 0.06 seconds. The second takes 0.23 seconds. On further
>>> experimentation, the time for the second one seems to taken in setting up
>>> the joins, because if I omit selecting anything from the joined tables, it
>>> still takes 0.23 seconds.
>>>
>> >
>> > please send result of explain analyze
>> >
>> > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
>> number 14 maybe 16
>> >
>> I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.
>>
>> I have attached files containing my SQL command, and the results of
>> EXPLAIN ANALYSE
>>
>
> > please use https://explain.depesz.com/ for both plans (slow, fast)
>
>
> Here are the results -
>
> sql_slow - https://explain.depesz.com/s/9vn3
>
> sql_fast - https://explain.depesz.com/s/oW0F
>

I don't see any issue there - it looks like some multi dimensional query
and it should not be well optimized due not precious estimations. The slow
query has much more complex - some bigger logic is under nested loop -
where estimation is not fully correct, probably due dependencies between
columns.

what does SET enable_nestloop to off;

?

Regards

Pavel

>
> Frank
>
>