Re: performance regression when filling in a table - Mailing list pgsql-hackers

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.