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
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 |