Re: performance degredation after upgrade from 9.6 to 12

Hi,

On 2019-11-24 15:50:20 -0500, Jeff Janes wrote:
> 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:

Huh, that's somewhat surprising. <5% I can see - there were some
tradeoffs to be made, and some performance issues to be worked around,
but 15% seems large. Is this with assertions enabled? Optimized?

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

Yea, there's zero chance of a revert.

> 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 particular, artificial queries will often show bottlenecks that are
not releveant in practice...

> 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 wonder if the improvements in
https://www.postgresql.org/message-id/20191023163849.sosqbfs5yenocez3%40alap3.anarazel.de
would at least partially address this.

Greetings,

Andres Freund