Thread: v17 vs v16 performance comparison

Hello hackers,

I've repeated the performance measurement for REL_17_STABLE (1e020258e)
and REL_16_STABLE (6f6b0f193) and found several benchmarks where v16 is
significantly better than v17. Please find attached an html table with
all the benchmarking results.

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.

So it looks like q15 from TPC-DS is not the only query suffering from that
change.

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.

Does this deserve more analysis and maybe fixing?

[1] https://www.postgresql.org/message-id/63a63690-dd92-c809-0b47-af05459e95d1%40gmail.com

Best regards,
Alexander

Alexander Lakhin <exclusion@gmail.com> writes:
> I've repeated the performance measurement for REL_17_STABLE (1e020258e)
> and REL_16_STABLE (6f6b0f193) and found several benchmarks where v16 is
> significantly better than v17. Please find attached an html table with
> all the benchmarking results.

Thanks for doing that!

I have no opinion about b7b0f3f27, but as far as this goes:

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

I'm not terribly concerned about that.  The nature of planner changes
like that is that some queries will get worse and some better, because
the statistics and cost estimates we're dealing with are not perfect.
It is probably worth drilling down into that test case to understand
where the planner is going wrong, with an eye to future improvements;
but I doubt it's something we need to address for v17.

            regards, tom lane

On Thu, Aug 1, 2024 at 3:00 PM Alexander Lakhin <exclusion@gmail.com> wrote:
> So it looks like q15 from TPC-DS is not the only query suffering from that
> change.

I'm going to try to set up a local repro to study these new cases.  If
you have a write-up somewhere of how exactly you run that, that'd be
useful.

Hello Thomas.

01.08.2024 08:57, Thomas Munro wrote:
> On Thu, Aug 1, 2024 at 3:00 PM Alexander Lakhin <exclusion@gmail.com> wrote:
>> So it looks like q15 from TPC-DS is not the only query suffering from that
>> change.
> I'm going to try to set up a local repro to study these new cases.  If
> you have a write-up somewhere of how exactly you run that, that'd be
> useful.

I'm using this instrumentation (on my Ubuntu 22.04 workstation):
https://github.com/alexanderlaw/pg-mark.git
README.md can probably serve as a such write-up.

If you install all the prerequisites (some tests, including pg_tpcds,
require downloading additional resources; run-benchmarks.py will ask to
do that), there should be no problems with running benchmarks.

I just added two instances to config.xml:
         <instance id="pg-src-16" type="src" pg_version="16devel" git_branch="REL_16_STABLE" />
         <instance id="pg-src-17" type="src" pg_version="17devel" git_branch="REL_17_STABLE" />
and ran
1)
./prepare-instances.py -i pg-src-16 pg-src-17

2)
time ./run-benchmarks.py -i pg-src-16 pg-src-17 pg-src-16 pg-src-17 pg-src-17 pg-src-16
(it took 1045m55,215s on my machine so you may prefer to choose the single
benchmark (-b pg_tpcds or maybe s64da_tpcds))

3)
./analyze-benchmarks.py -i 'pg-src-17--.*' 'pg-src-16--.*'

All the upper-level commands to run benchmarks are contained in config.xml,
so you can just execute them separately, but my instrumentation eases
processing of the results by creating one unified benchmark-results.xml.

Please feel free to ask any questions or give your feedback.

Thank you for paying attention to this!

Best regards,
Alexander

01.08.2024 06:41, Tom Lane wrote:
>
>> 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.
> I'm not terribly concerned about that.  The nature of planner changes
> like that is that some queries will get worse and some better, because
> the statistics and cost estimates we're dealing with are not perfect.
> It is probably worth drilling down into that test case to understand
> where the planner is going wrong, with an eye to future improvements;
> but I doubt it's something we need to address for v17.

Please find attached two plans for that query [1].
(I repeated the benchmark for f7816aec2 and f7816aec2~1 five times and
made sure that both plans are stable.)

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.

[1] https://github.com/swarm64/s64da-benchmark-toolkit/blob/master/benchmarks/tpcds/queries/queries_10/95.sql

Best regards,
Alexander