|
Ron Johnson
wrote:
> On 09/12/2017 01:45 AM, Frank Millman
wrote:
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. > Just out of curiosity, what if you PREPARE the statement, and take multiple timings? My setup is a bit complicated, as I am executing the commands from a python program on Windows against a PostgreSQL database on Fedora, so I hope I did it correctly! With that caveat, the results are that the time was reduced from 0.23 seconds to 0.22 seconds. The difference is consistent, so I think it is real. Frank |
