Re: Is there something wrong with my test case?

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

> where U.KEY_U in ({correlated subquery 3})

This is not correlated in [1].

[1] https://pastebin.com/W2HsTBwi

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services