Re: Joining 16 tables seems slow

2017-09-12 12:39 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 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;
>

from statistics - the ar_tran_inv table is scanned 6x in slow query and
2times in fast query. Maybe there should be some index

> ?
>
> Regards
>
> Pavel
>
>
>
>>
>> Frank
>>
>>
>
>