Re: performance regression when filling in a table

Hi,

On 2019-04-30 12:32:13 +0200, Fabien COELHO wrote:
> 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.

Yea, that's pretty normal. The likely difference is that in the repeated
case you'll have WAL files ready to be recycled. I'd assume that the
difference between the runs would be much smaller if used unlogged
tables (or WAL on a ramdisk or somesuch).

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

How comparable are the runs? Are you restarting postgres inbetween?
Perform checkpoints?

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

Hm, that's more than I normally see. What exactly did you do there?

Greetings,

Andres Freund