Re: Is there something wrong with my test case?

Hi David

Thanks for your revision.

Quoting David Rowley <david.rowley@2ndquadrant.com>:

> On Wed, 26 Dec 2018 at 00:54, Thiemo Kellner
> <thiemo@gelassene-pferde.biz> 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