Re: Joining 16 tables seems slow

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)

Regards

Pavel

>
> Frank
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>