2017-09-12 12:25 GMT+02:00 Frank Millman <fr...@chagford.com>: > 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 >
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 > >
