Thread: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes

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

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> Questions:

 PG>   Is there an extra setting for Postgres 10+ required to "recover"
 PG> the performance loss from PG9.5?

The default wal_level changed between pg 9.6 and pg10, does reverting
that change make any difference? (this is just a guess)

 PG>   Why is PG10+ slower by default when synchronous_commit is off?

synchronous_commit is probably only relevant to the extent that turning
it off causes the test not to be bottlenecked on WAL flush calls.

-- 
Andrew (irc:RhodiumToad)

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Tue, Jan 29, 2019 at 2:05 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> Before each test run, I drop and recreate the table and indexes.

What happens if you don't create bool_idx, or replace it with another
index on some other column? I notice that you didn't show any case
that doesn't have this index, except for the PK-only case, which is
actually faster. I surmise that that's the common factor in all of the
test cases where you have observed a regression. It would be nice to
confirm or disprove this theory.

The nbtree code is known to deal poorly with low cardinality indexes
[1], something I'm currently working to address. Are you comparing
installations that are on the same hardware and operating system?

[1] https://postgr.es/m/CAH2-Wzmf0fvVhU+SSZpGW4Qe9t--j_DmXdX3it5JcdB8FF2EsA@mail.gmail.com
--
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

Hi,

On Tue, Jan 29, 2019 at 8:27 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> That seems to have narrowed down the regression to Boolean data types only.
> I will run the test now against the most common base types that we use and report back.
>
> Is this a known regression/bug on PG10+? Sorry I could not find any reference to this bug.

I can't think of a reason why the problem would be any worse on recent
releases right now. However, the behavior I'm describing is
complicated. I could have missed something.

-- 
Peter Geoghegan

On Tue, Jan 29, 2019 at 07:30:09AM +0000, Andrew Gierth wrote:
> The default wal_level changed between pg 9.6 and pg10, does reverting
> that change make any difference? (this is just a guess)

It seems to me that Andrew has the good conclusion here.  The OP is
mentioning that a couple of data folders are just initdb'd, which
could point out to the fact that the default configuration set is used
for all of them.
--
Michael

On Tue, Jan 29, 2019 at 11:32 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> But that seems to imply that there was a change deeper in the code that causes this regression on PG10+.
> IMHO I cannot be the only one that noticed this, since PG10 has been out for a while and secondary indexes are quite
commonin practice.
 
> I was wondering if anyone can point me in the right direction on how to further investigate this?

What size is bool_idx, and the other indexes once your benchmarks
finish? How do they compare across versions?

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Thu, Jan 31, 2019 at 04:08:39PM +1300, Saul, Jean Paolo wrote:
> Additional tests replacing BTREE indexes with HASH indexes indicate that hash
> indexes do not suffer from low-cardinality performance regression.
> However I cannot use hash indexes in our systems as they are discouraged.

Hash indexes are crash safe since PG 10 so their use is no longer
discouraged, at least from a project perspective.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Wed, Jan 30, 2019 at 6:06 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> I am testing your comment about low cardinality indexes, and changed my inserted values.
>
> for integers: (RANDOM()*10)::INT % 2
> for text:  MD5(((RANDOM()*10)::INT % 2)::TEXT)
>
> The latest results show that text and integer indexes now behave poorly like the boolean index.
> The performance hit is visibly disappointing compared to versions prior to PG10.

FWIW, I cannot recreate this. I still have no reason to believe that
this problem with low cardinality indexes would be any worse on more
recent versions.

BTW, I think that you'll find that hash indexes don't do as well as
B-Tree indexes with lots of duplicates when reading.

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Sun, Feb 3, 2019 at 2:45 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> The last post was too long. I think I have a much more simpler example that is easier to replicate.

This new example is very similar to examples that I have personally
come up with. I have no difficulty explaining why the case with lots
of duplicates is slower, so it doesn't really help.

I cannot account for why you can observe a difference across Postgres
versions, though -- that's what I'm having difficulty with. Are you
sure about that effect? There haven't been any directly relevant
changes in this area in many years.

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Fri, Feb  1, 2019 at 10:49:13AM +1300, Saul, Jean Paolo wrote:
> Hi Bruce,
> 
> Thank you for that info. We will likely migrate our boolean indexes (and
> possibly all our low cardinality indexes) to hash after we move to PG11.

Uh, there is rarely value in creating boolean indexes because, for an
index to be useful, it should have high selectivity.  What people often
do is to create _partial_ indexes on true, false, or NULL values that
are of high selectivity.  Since there is only a single value in the
index, I guess a hash index would be better than btree, but I am not
sure.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Sun, Feb 10, 2019 at 3:05 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> Can anyone please shed some light as to why this works?
> The only thing I could think of is a locking issue with the leaf nodes.

I describe why in the original thread about the problem and my
approach, though it's very low level stuff. See:

https://www.postgresql.org/message-id/flat/CAH2-Wzmf0fvVhU+SSZpGW4Qe9t--j_DmXdX3it5JcdB8FF2EsA@mail.gmail.com

> How much testing is required for this to be considered a proper bug?

You haven't really demonstrated a substantial regression across
versions (17361.186258 tps on v11, down from 20137.416962 tps on 9.5),
which is the only way that this could get classified as a bug. It's a
*far* smaller difference than the difference that you show between
otherwise-similar high cardinality and low cardinality indexes. In
general, I'm confused about why you're concerned about v11 in
particular here.

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Mon, Feb 11, 2019 at 4:47 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> Aside from the ports:
>
> listen_addresses = '*'
> max_connections = 300
> max_wal_senders = 0  --  (on PG11)
> synchronous_commit = off

You mean shared_buffers is set to the default?

I don't think that that's an interesting test case. If shared_buffers
is ridiculously small, then there is little that we can take away from
your benchmark, or any benchmark like it.

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Mon, Feb 11, 2019 at 5:19 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> Just to clarify we were using shared_buffers=30GB for our initial pgbench runs that would take a few hours to
complete.In an attempt to make the tests repeatable before submitting I have simplified the steps.
 

Can you retry, but pass "-M prepared" to pgbench? That can easily make
something like this 40%+ faster. I'm not supposing that that'll change
the difference very significantly, but you'll at least have more
signal and less noise that way.

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Mon, Feb 11, 2019 at 5:36 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> Using prepared and extended. No luck.

And your checkpoint settings?

The performance you're seeing is pretty bad for a server that has more
than 40GB of memory. I can get far superior performance on the same
benchmark with a relatively inexpensive workstation, at least on the
master branch.

Try these for a start if you're using default checkpoint settings, as
I suspect you are:

checkpoint_timeout=5min
min_wal_size=10GB
max_wal_size=40GB
wal_compression=on
backend_flush_after=0
checkpoint_flush_after=256kB
checkpoint_completion_target=0.9

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Mon, Feb 11, 2019 at 6:01 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> Thanks for the tip Peter. Unfortunately it did not move the needle. Just curious what numbers are you getting when
youare testing?
 

Well over 100k tps, usually ~150k tps, quite consistently (I need to
use -M prepared to get over 100k tps, though). I can see the progress
by using pgbench's -P option (e.g. -P 5 to see progress reports every
5 seconds), which is how I've determined that it's consistent. I am
using the master branch, but I can't think of any reason why it would
be different to v11.

I do have a fairly high end though still workstation grade SSD -- a
Samsung 970 PRO 512GB. I imagine that your server is at least as
powerful as my workstation by every measure, so this shouldn't matter.

-- 
Peter Geoghegan

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

---

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

On Tue, Feb 12, 2019 at 5:48 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.com> wrote:
> Does having < four distinct values in the btree index have any known significance wrt to btree performance?

Not that I'm aware of, or have been able to ascertain through testing.

-- 
Peter Geoghegan