BUG #15609: synchronous_commit=off insert performance regression with secondary indexes - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15609
Logged by:          Jean Paolo Saul
Email address:      paolo.saul@verizonconnect.com
PostgreSQL version: 11.1
Operating system:   CentOS Linux release 7.6.1810 (Core)
Description:

Summary:
  We are considering upgrading to PG11 and during performance testing we
have found that
  PostgreSQL 11 inserts are around 25% slower than inserts in PostgreSQL
9.5.

Tools Used:
  pgbench (11.1)

Test Overview:
  1) InitDB and start four instances using versions PG9.5.15, PG9.6.11
PG10.6, and PG11.1
  2) Create a test table
  3) pgbench using inserts to the test table
     3.1) test using default config settings , synchronous_commit=off ,
fsync=off
         3.1.1) test with primary key only , primary key with one secondary
index , primary key with two secondary indexes , primary key with three
secondary indexes

Test Setup:
  Amazon EC2 Instance:
  m4.16xlarge - 64 cores, 251GB RAM
  50GB EBS, volume type: io1

  Table:
    CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN,
int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id))

  Indexes:
    CREATE INDEX bool_idx ON test_indexes (bool_data)
    CREATE INDEX int_idx  ON test_indexes (int_data)
    CREATE INDEX text_idx ON test_indexes (text_data)


Test Results (TPS is average of three runs):
** DEFAULT CONF             VERSION  TPS     DIFF FROM PG95
pkey only
                            PG9.5    42414   0.0%
                            PG9.6    41967   -1.1%
                            PG10     43443   2.4%
                            PG11     43676   3.0%
bool index
                            PG9.5    42310   0.0%
                            PG9.6    42082   -0.5%
                            PG10     41902   -1.0%
                            PG11     42305   0.0%
bool+int index
                            PG9.5    41539   0.0%
                            PG9.6    41966   1.0%
                            PG10     41294   -0.6%
                            PG11     41819   0.7%
bool+int+text index
                            PG9.5    40000   0.0%
                            PG9.6    40526   1.3%
                            PG10     40582   1.5%
                            PG11     39882   -0.3%


** SYNCHRONOUS_COMMIT=OFF   VERSION  TPS     DIFF FROM PG95
pkey only
                            PG9.5    103904  0.0%
                            PG9.6    100017  -3.7%
                            PG10     103857  0.0%
                            PG11     117147  12.7%
bool index
                            PG9.5    67283   0.0%
                            PG9.6    70850   5.3%
                            PG10     51113   -24.0%
                            PG11     49659   -26.2%
bool+int index
                            PG9.5    66048   0.0%
                            PG9.6    68247   3.3%
                            PG10     50558   -23.5%
                            PG11     47734   -27.7%
bool+int+text index
                            PG9.5    66732   0.0%
                            PG9.6    67131   0.6%
                            PG10     47157   -29.3%
                            PG11     47692   -28.5%


** FSYNC=OFF (10 SECS)      VERSION  TPS     DIFF FROM PG95
no secondary index
                            PG9.5    90974   0.0%
                            PG9.6    90174   -0.9%
                            PG10     93661   3.0%
                            PG11     101758  11.9%
bool index
                            PG9.5    65328   0.0%
                            PG9.6    68447   4.8%
                            PG10     45757   -30.0%
                            PG11     46610   -28.7%
bool+int index
                            PG9.5    63247   0.0%
                            PG9.6    64010   1.2%
                            PG10     43378   -31.4%
                            PG11     45467   -28.1%
bool+int+text index
                            PG9.5    60768   0.0%
                            PG9.6    63230   4.1%
                            PG10     40968   -32.6%
                            PG11     44017   -27.6%

Questions:
  Is there an extra setting for Postgres 10+ required to "recover" the
performance loss from PG9.5?
    We are using PG9.5 with synchronous_commit=off in production and
majority of our tables have secondary indexes.
  Why is PG10+ slower by default when synchronous_commit is off?

Notes:
  Tested with all wal_sync_methods: fdatasync, open_datasync, fsync,
fsync_writethrough(fails), open_sync, with no statistical significance
found
  Did not test with updates or deletes