Is there something wrong with my test case? - Mailing list pgsql-general

Hi all and merry Christmas

I was under the impression that updating a table with values from (an)  
other table(s) would be implemented most efficiently with a correlated  
subquery a long the schema as follows, let's name it A.

update TO_BE_UPDATED U
    set ({column list}) = ({correlated subquery 1})
  where exists ({correlated subquery 1})
;

I set up a test case to figure out if this is the case. I compared the  
execution plan of a query with above pattern with the execution plans  
with the following patterns.

B
update TO_BE_UPDATED U
    set COL_1 = ({correlated subquery 1}),
        COL_2 = ({correlated subquery 2})
  where exists ({correlated subquery 3})
;

C
update TO_BE_UPDATED U
    set COL_1 = ({correlated subquery 1}),
        COL_2 = ({correlated subquery 2})
  where U.KEY_U in ({correlated subquery 3})
;

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.

Now I wonder whether I do not know, how to read the explain plan  
summary, I should not trust my subjective impression with respect to  
time measurement or if my test case is faulty.

I carried out the test on an idle Windows 10 laptop with portable  
PostgreSQL 10.4 provided by PortableApps.
You can find test case script and log at https://pastebin.com/W2HsTBwi

I would appreciate your two dimes.

Kind regards

Thiemo
-- 
Signal: +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B