Re: performance degredation after upgrade from 9.6 to 12

On Sun, Nov 24, 2019 at 1:05 PM Mariel Cherkassky <
mariel(dot)cherkassky(at)gmail(dot)com> wrote:

> Hey Jeff,
> This example was only used to show that pg96 had better perfomance than
> pg12 in a very simple case.
>

OK, but do you agree that a 15% slow down is more realistic than 3 fold
one? Or are you still getting 3 fold slow down with more careful testing
and over a wide variety of queries?

I find that the main regression (about 15%) in your example occurs in major
version 10, at the following commit:

commit b8d7f053c5c2bf2a7e8734fe3327f6a8bc711755
Author: Andres Freund <andres(at)anarazel(dot)de>
Date: Tue Mar 14 15:45:36 2017 -0700

Faster expression evaluation and targetlist projection.

It is disappointing that this made this case slower rather than faster, and
that the "future work" alluded to either hasn't happened, or wasn't
effective for this example. I also tested the same example, only 100 times
more rows, and still see the regression at about 16%. This is a major
infrastructure change patch which has been extensively built on since then,
the chances of reverting it are very small. It is making an omelette, and
your example is one of the eggs that got broken.

Performance changes in a large body of queries are usually not all due to
the same thing. Are you a position to custom compile your own PostgreSQL?
It would be nice to test this commit against the one before it, and see how
much of the change in your real queries is explained by this one thing (or
whether any of it is)

> In all the tests that I run most of the queries took less time on 9.6`s
> version. I dont know why, but as you can see after disabling the parameter
> the simple test that I did showed different results.
>

I can't see--You didn't post results for that. And running your test on my
own system doesn't show that at all. In your test case,
max_parallel_workers_per_gather makes no difference. With 100 times more
rows, setting it to 0 actually slows things down, as at that size
parallelization is useful and disabling it hurts.

Of course parallel query might be hurting some of the other queries, but
for the one example you show you will have to show something more
convincing for me to believe that that is what caused it.

It is easy to benchmark with something like:

PGOPTIONS="-c max_parallel_workers_per_gather=0" pgbench -T30 -f <(echo
"select count(*) from test1") -p 9912 -n -M prepared

If it is other queries where mpwpg is making a difference, than one issue
could be that your settings of parallel_setup_cost and/or
parllel_tuple_cost are too low (although I usually find the default
settings too high, not too low); or you are running your test concurrently
already and so don't need parallel query to fully load the CPUs and trying
to use parallel query just increases the overhead; or your machine doesn't
have the number of truly effective CPUs you think it does.

Cheers,

Jeff

>