2017-09-12 12:39 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 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; >
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 >> >> > >
