Is there something wrong with my test case?

From: Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Is there something wrong with my test case?
Date: 2018-12-25 11:54:11
Message-ID: 20181225115411.Horde.5fRj7BmRS_7UIgfoBa9Vz2w@webmail.gelassene-pferde.biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-12-25 18:09:49 Re: Enerprisedb compatibility
Previous Message az.crb@outlook.es 2018-12-25 11:53:10 Enerprisedb compatibility