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.