Re: Joining 16 tables seems slow

hi

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

> Hi all
>
> 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.
>
> Exactly the same exercise on Sql Server results in 0.06 seconds for both
> versions.
>
> I realise that, if I was selecting a large number of rows, 0.23 seconds is
> trivial and the overall result could be different. But still, it seems odd.
>
> Is this normal, or should I investigate further?
>

please send result of explain analyze

you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
number 14 maybe 16

regards

>
> Frank Millman
>
>