Pavel Stehule wrote:
2017-09-12 9:36 GMT+02:00 Frank Millman <fr...@chagford.com>:
Pavel Stehule wrote:
>
> 2017-09-12 8:45 GMT+02:00 Frank Millman <fr...@chagford.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
Frank
