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
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