Is there something wrong with my test case?
От | Thiemo Kellner |
---|---|
Тема | Is there something wrong with my test case? |
Дата | |
Msg-id | 20181225115411.Horde.5fRj7BmRS_7UIgfoBa9Vz2w@webmail.gelassene-pferde.biz обсуждение исходный текст |
Ответы |
Re: Is there something wrong with my test case?
Re: Is there something wrong with my test case? |
Список | 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
В списке pgsql-general по дате отправления: