PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15609
PG Version11.1
OSCentOS Linux release 7.6.1810 (Core)
Opened2019-01-29 02:27:33+00
Reported byJean Paolo Saul
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15609
Logged by:          Jean Paolo Saul
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-01-29 02:27:33+00PG Bug reporting formBUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-29 07:30:09+00Andrew GierthRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-29 18:36:51+00Jeff JanesRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-29 22:05:02+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-30 00:59:20+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-30 01:36:14+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-30 04:26:49+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-30 04:32:25+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-30 05:21:40+00Michael PaquierRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-30 21:30:07+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-31 02:06:01+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-31 03:08:39+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-31 14:35:58+00Bruce MomjianRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-01-31 21:49:13+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-01 00:08:32+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-01 04:35:07+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-03 22:45:12+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-03 23:09:49+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-04 02:26:59+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-04 23:08:04+00Bruce MomjianRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-05 00:51:03+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-05 04:39:18+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-10 23:05:09+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-11 00:44:39+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 00:27:49+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 00:36:03+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 00:47:17+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 00:49:23+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 01:19:30+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 01:22:52+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 01:36:33+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 01:42:39+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 02:00:56+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 02:06:20+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 02:59:35+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-12 04:32:05+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-13 01:48:21+00"Saul, Jean Paolo"Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
2019-02-13 06:51:05+00Peter GeogheganRe: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes