Re: v17 vs v16 performance comparison

Hello Thomas,

02.08.2024 12:00, Alexander Lakhin wrote:

>
>>
>>>
>>> I had payed attention to:
>>> Best pg-src-17--.* worse than pg-src-16--.* by 57.9 percents (225.11 > 142.52): pg_tpcds.query15
>>> Average pg-src-17--.* worse than pg-src-16--.* by 55.5 percents (230.57 > 148.29): pg_tpcds.query15
>>> in May, performed `git bisect` for this degradation, that led me to commit
>>> b7b0f3f27 [1].
>>>
>>> This time I bisected the following anomaly:
>>> Best pg-src-17--.* worse than pg-src-16--.* by 23.6 percents (192.25 > 155.58): pg_tpcds.query21
>>> Average pg-src-17--.* worse than pg-src-16--.* by 25.1 percents (196.19 > 156.85): pg_tpcds.query21
>>> and to my surprise I got "b7b0f3f27 is the first bad commit".
>>>
>>> Moreover, bisecting of another anomaly:
>>> Best pg-src-17--.* worse than pg-src-16--.* by 24.2 percents (24269.21 > 19539.89): pg_tpcds.query72
>>> Average pg-src-17--.* worse than pg-src-16--.* by 24.2 percents (24517.66 > 19740.12): pg_tpcds.query72
>>> pointed at the same commit again.
>>>
>>> ...
>>>
>>> But beside that, I've found a separate regression. Bisecting for this degradation:
>>> Best pg-src-17--.* worse than pg-src-16--.* by 105.0 percents (356.63 > 173.96): s64da_tpcds.query95
>>> Average pg-src-17--.* worse than pg-src-16--.* by 105.2 percents (357.79 > 174.38): s64da_tpcds.query95
>>> pointed at f7816aec2.
>>
>
> Meanwhile I've bisected another degradation:
> Best pg-src-17--.* worse than pg-src-16--.* by 11.3 percents (7.17 > 6.44): job.query6f
> and came to the commit b7b0f3f27 again.

Now that the unfairness in all-cached parallel seq scan eliminated (with
3ed3683618),  I've re-run the same performance tests and got new results
(please see attached). As we can see, the aforementioned pg_tpcds.query72
got better:
              2024-05-15  2024-07-30 2024-09-03
pg-src-16--1   20492.58    19669.34    19913.32
pg-src-17--1   25286.10    24269.21    20654.95
pg-src-16--2   20769.88    19539.89    20429.72
pg-src-17--2   25771.90    24530.51    21244.92
pg-src-17--3   25978.55    24753.25    20904.09
pg-src-16--3   20943.10    20011.13    20086.61

We can also see the improvement of pg_tpcds.query16, but not on all runs:
              2024-05-15  2024-07-30 2024-09-03
pg-src-16--1     105.36       94.31       97.74
pg-src-17--1     145.74      145.53      145.51
pg-src-16--2     101.82       98.36       96.63
pg-src-17--2     140.07      146.90       96.93
pg-src-17--3     154.89      148.11      106.18
pg-src-16--3     101.03      100.94       93.44

So it looks like now we see the same instability, that we observed before
([1]).

Unfortunately, the troublesome tpcds.query15 hasn't produced good numbers
this time too:
              2024-05-15  2024-07-30 2024-09-03
pg-src-16--1     153.41      142.52      142.54
pg-src-17--1     229.84      225.11      212.51
pg-src-16--2     153.47      150.13      149.37
pg-src-17--2     236.34      227.15      232.73
pg-src-17--3     236.43      239.46      233.77
pg-src-16--3     151.03      152.23      144.90

From a bird's eye view, new v17-vs-v16 comparison has only 87 "worse",
while the previous one had 115 (it requires deeper analysis, of course, but
still...).

[1] https://www.postgresql.org/message-id/d1fb5c09-dd03-2540-9ec2-86dbfdfa2c65%40gmail.com

Best regards,
Alexander