Re: performance regression when filling in a table

Hello Andres,

>> ## pg 11.2 done in 31.51 s
>> ## pg 12devel (cd3e2746) real 0m38.695s
>>
>> What change could explain such a significant performance regression?
>
> I think the pre-release packages have had assertions enabled at some
> point. I suggest checking that. If it's not that, profiles would be
> helpful.

Thanks for the pointer.

After some more tests based on versions compiled from sources, the
situation is different, and I was (maybe) mostly identifying another
effect not related to postgres version.

The effect is that the first generation seems to take more time, but
dropping the table and regenerating again much less, with a typical 40%
performance improvement between first and second run, independently of the
version. The reported figures above where comparisons between first for
pg12 and second or later for pg11.

So I was wrong, there is no significant performance regression per se,
the two versions behave mostly the same.

I'm interested if someone has an explanation about why the first run is so
bad or others are so good. My wide guess is that there is some space reuse
under the hood, although I do not know enough about the details to
confirm.

A few relatively bad news nevertheless:

Performances are quite unstable, with index generation on the same scale
100 data taking anything from 6 to 15 seconds over runs.

Doing a VACUUM and checksums interact badly: vacuum time jumps from 3
seconds to 30 seconds:-(

--
Fabien.