Re: performance regression when filling in a table

Hello Andres,

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

I tried unlogged, and indeed the first run is no different from subsequent
ones.

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

See below for a taste.

> Are you restarting postgres inbetween?

Nope. Trying once did not change the measures.

> Perform checkpoints?

Nope, but with the default settings there is one avery five minutes. I'm
not sure a checkpoint should have a significant impact on a COPY
initialization.

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

I simply ran "pgbench -i -s 100" on master, with
https://commitfest.postgresql.org/23/2085/ thrown in for detailed stats.

Without checksums:

# init
37.68 s (drop tables 0.00 s, create tables 0.02 s, generate 27.12 s, vacuum 2.97 s, primary keys 7.56 s)
30.53 s (drop tables 0.25 s, create tables 0.01 s, generate 16.64 s, vacuum 3.47 s, primary keys 10.16 s)
36.31 s (drop tables 0.25 s, create tables 0.01 s, generate 18.94 s, vacuum 3.40 s, primary keys 13.71 s)
31.34 s (drop tables 0.23 s, create tables 0.01 s, generate 19.07 s, vacuum 3.00 s, primary keys 9.03 s)
# reinit
38.25 s (drop tables 0.00 s, create tables 0.03 s, generate 29.33 s, vacuum 3.10 s, primary keys 5.80 s)
35.16 s (drop tables 0.25 s, create tables 0.01 s, generate 17.62 s, vacuum 2.62 s, primary keys 14.67 s)
29.15 s (drop tables 0.25 s, create tables 0.01 s, generate 17.35 s, vacuum 2.98 s, primary keys 8.55 s)
32.70 s (drop tables 0.25 s, create tables 0.01 s, generate 21.49 s, vacuum 2.65 s, primary keys 8.29 s)
# reinit
42.39 s (drop tables 0.00 s, create tables 0.03 s, generate 33.98 s, vacuum 2.16 s, primary keys 6.23 s)
31.24 s (drop tables 0.24 s, create tables 0.01 s, generate 17.34 s, vacuum 4.74 s, primary keys 8.91 s)
26.91 s (drop tables 0.24 s, create tables 0.01 s, generate 16.83 s, vacuum 2.89 s, primary keys 6.94 s)
29.00 s (drop tables 0.25 s, create tables 0.01 s, generate 17.78 s, vacuum 2.97 s, primary keys 7.98 s)
# init
37.68 s (drop tables 0.00 s, create tables 0.02 s, generate 27.12 s, vacuum 2.97 s, primary keys 7.56 s)
30.53 s (drop tables 0.25 s, create tables 0.01 s, generate 16.64 s, vacuum 3.47 s, primary keys 10.16 s)
36.31 s (drop tables 0.25 s, create tables 0.01 s, generate 18.94 s, vacuum 3.40 s, primary keys 13.71 s)
31.34 s (drop tables 0.23 s, create tables 0.01 s, generate 19.07 s, vacuum 3.00 s, primary keys 9.03 s)
# reinit
38.25 s (drop tables 0.00 s, create tables 0.03 s, generate 29.33 s, vacuum 3.10 s, primary keys 5.80 s)
35.16 s (drop tables 0.25 s, create tables 0.01 s, generate 17.62 s, vacuum 2.62 s, primary keys 14.67 s)
29.15 s (drop tables 0.25 s, create tables 0.01 s, generate 17.35 s, vacuum 2.98 s, primary keys 8.55 s)
32.70 s (drop tables 0.25 s, create tables 0.01 s, generate 21.49 s, vacuum 2.65 s, primary keys 8.29 s)
# reinit
42.39 s (drop tables 0.00 s, create tables 0.03 s, generate 33.98 s, vacuum 2.16 s, primary keys 6.23 s)
31.24 s (drop tables 0.24 s, create tables 0.01 s, generate 17.34 s, vacuum 4.74 s, primary keys 8.91 s)
26.91 s (drop tables 0.24 s, create tables 0.01 s, generate 16.83 s, vacuum 2.89 s, primary keys 6.94 s)
29.00 s (drop tables 0.25 s, create tables 0.01 s, generate 17.78 s, vacuum 2.97 s, primary keys 7.98 s)

With checksum enabled:

# init
73.84 s (drop tables 0.00 s, create tables 0.03 s, generate 32.81 s, vacuum 34.95 s, primary keys 6.06 s)
61.49 s (drop tables 0.24 s, create tables 0.01 s, generate 18.55 s, vacuum 33.26 s, primary keys 9.42 s)
62.79 s (drop tables 0.24 s, create tables 0.01 s, generate 21.08 s, vacuum 33.50 s, primary keys 7.96 s)
58.77 s (drop tables 0.23 s, create tables 0.06 s, generate 21.98 s, vacuum 31.21 s, primary keys 5.30 s)
# restart
63.77 s (drop tables 0.04 s, create tables 0.02 s, generate 17.37 s, vacuum 40.84 s, primary keys 5.51 s)
64.48 s (drop tables 0.22 s, create tables 0.01 s, generate 19.84 s, vacuum 33.43 s, primary keys 10.98 s)
64.10 s (drop tables 0.23 s, create tables 0.01 s, generate 22.11 s, vacuum 33.17 s, primary keys 8.57 s)
# reinit
71.65 s (drop tables 0.00 s, create tables 0.03 s, generate 34.23 s, vacuum 31.67 s, primary keys 5.72 s)
64.33 s (drop tables 0.23 s, create tables 0.01 s, generate 21.31 s, vacuum 36.58 s, primary keys 6.20 s)
62.06 s (drop tables 0.23 s, create tables 0.02 s, generate 19.15 s, vacuum 37.34 s, primary keys 5.32 s)

Detailed figure are not visibly different (other reported figures about
checksum vs no checksum suggested a few percent impact), but for VACUUM
where it is closer to a thousand percent. Cassert is off, this is not the
issue. Hmmm.

--
Fabien.