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