Hi David
Thanks for your revision.
Quoting David Rowley <david.rowley@xxxxxxxxxxxxxxx>:
On Wed, 26 Dec 2018 at 00:54, Thiemo Kellner
<thiemo@xxxxxxxxxxxxxxxxxxxx> wrote:
Explain analyze verbose showed for:
A (cost=264.72..626.97 rows=31 width=90) (actual time=1.117..1.117
rows=0 loops=1)
B (cost=264.72..974.25 rows=31 width=58) (actual time=1.508..1.508
rows=0 loops=1)
C (cost=0.42..611.19 rows=31 width=52) (actual time=2.217..2.217
rows=0 loops=1)
I am very surprised that the cost of A is (much) higher than that of C
which I suspected to be the most inefficient. I was that much fixed on
the costs that I initially ignored the actual time where my
assumptions on efficiency are reflected. Funny though is that the
subjective impression when waiting for the update queries to complete
was that C was fastest by far, followed by B and only at the end was
update A.
While the times mentioned in "actual time" are for execution only and
don't account for the time taken to plan the query, the results you
put in [1] disagree entirely with your claim that 'C' was faster. 'A'
comes out fastest with the explain analyzes you've listed.
A:
Planning TIME: 0.423 ms
Execution TIME: 1.170 ms
C:
Planning TIME: 0.631 ms
Execution TIME: 2.281 ms
Have you confused each of the results, perhaps because they're in a
different order as to your cases above?
I am pretty sure I did not confuse. I am not worried about planning
times as I assume that PostgreSQL has a time limit restricting the
time used to find the best execution path in the order of seconds such
that for a heavy load query it would get neglectable.
I'd certainly expect 'A' to be the fastest of the bunch since it's
both less effort for the planner and also the executor. I didn't look
at why the cost is estimated to be slightly higher, but the planner
wouldn't consider rewriting the queries to one of the other cases
anyway, so it's likely not that critical that the costings are
slightly out from reality.
I am glad, that my feeling what should be the best query meets
reality. However, I am left a bit concerned about the usefulness of
the costs of the execution plan. I feel the costs rather contradict
the actual execution times in my test case. To me this would render
the cost useless for comparison of queries.
where U.KEY_U in ({correlated subquery 3})
This is not correlated in [1].
[1] https://pastebin.com/W2HsTBwi
Right you are, my fault. Thanks for your attention there as well. :-)
--
Signal: +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B
