Re: 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(dot)saul(at)verizonconnect(dot)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(at)postgresql(dot)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)

On Tue, Jan 29, 2019 at 12:46 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15609
> Logged by: Jean Paolo Saul
> Email address: paolo(dot)saul(at)verizonconnect(dot)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)
>

Can you show the actual pgbench command line used, and the contents of the
file specified by -f ?

Cheers,

Jeff

Hi Jeff,

Insert SQL:
INSERT INTO test_indexes (bool_data , int_data , text_data ) VALUES (
(RANDOM() * 10)::INT % 2 = 0, RANDOM() * 10000, MD5((RANDOM() *
1000)::TEXT) );

pg_bench:
/usr/pgsql-11/bin/pgbench -Upostgres -f ${SQL} -n -c 60 -j 60 -T120 -p
${PORT}

Before each test run, I drop and recreate the table and indexes.

Cheers,

Paolo

On Wed, 30 Jan 2019 at 07:37, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Tue, Jan 29, 2019 at 12:46 AM PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 15609
>> Logged by: Jean Paolo Saul
>> Email address: paolo(dot)saul(at)verizonconnect(dot)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)
>>
>
> Can you show the actual pgbench command line used, and the contents of the
> file specified by -f ?
>
> Cheers,
>
> Jeff
>

--------------------------------------------------------------------------------------------------------------------------------------------
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 Andrew,

1) changing wal_levels did not make any difference w/
synchronous_commit=off.
This pattern also applies to fsync=off

2) We suspect the same with regards to the flushing bottleneck being
removed.
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 common in practice.
I was wondering if anyone can point me in the right direction on how to
further investigate this?

Cheers,

Paolo

Test results below.

---------------------------
WAL_LEVEL = {DEFAULTS}
--(MINIMAL ON 9.5,9.6 , REPLICA ON 10,11)
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 104503 0.0%
PG9.6 98842 -5.4%
PG10 103924 -0.6%
PG11 117635 12.6%

PG9.5 67285 0.0%
PG9.6 70153 4.3%
PG10 53657 -20.3%
PG11 49952 -25.8%

PG9.5 67695 0.0%
PG9.6 68592 1.3%
PG10 51039 -24.6%
PG11 48630 -28.2%

PG9.5 66102 0.0%
PG9.6 67883 2.7%
PG10 48964 -25.9%
PG11 46215 -30.1%

WAL_LEVEL = MINIMAL
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 103785 0.0%
PG9.6 98303 -5.3%
PG10 103369 -0.4%
PG11 116446 12.2%

PG9.5 67877 0.0%
PG9.6 70841 4.4%
PG10 52885 -22.1%
PG11 50111 -26.2%

PG9.5 67754 0.0%
PG9.6 69373 2.4%
PG10 52646 -22.3%
PG11 48824 -27.9%

PG9.5 66197 0.0%
PG9.6 69217 4.6%
PG10 50518 -23.7%
PG11 47389 -28.4%

WAL_LEVEL = HOT_STANDBY; (9.5, 9.6)
WAL_LEVEL = REPLICA; (10, 11)
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 104718 0.0%
PG9.6 97279 -7.1%
PG10 104249 -0.4%
PG11 116119 10.9%

PG9.5 68819 0.0%
PG9.6 71223 3.5%
PG10 52592 -23.6%
PG11 50047 -27.3%

PG9.5 67057 0.0%
PG9.6 69256 3.3%
PG10 51317 -23.5%
PG11 48401 -27.8%

PG9.5 66727 0.0%
PG9.6 67591 1.3%
PG10 49819 -25.3%
PG11 47453 -28.9%

WAL_LEVEL = LOGICAL
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 104208 0.0%
PG9.6 97920 -6.0%
PG10 104084 -0.1%
PG11 115364 10.7%

PG9.5 66910 0.0%
PG9.6 70968 6.1%
PG10 52719 -21.2%
PG11 48882 -26.9%

PG9.5 67704 0.0%
PG9.6 69768 3.0%
PG10 50080 -26.0%
PG11 49294 -27.2%

PG9.5 67490 0.0%
PG9.6 68872 2.0%
PG10 45837 -32.1%
PG11 46505 -31.1%
---------------------------

On Tue, 29 Jan 2019 at 20:30, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)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.

On Tue, Jan 29, 2019 at 2:05 PM Saul, Jean Paolo
<paolo(dot)saul(at)verizonconnect(dot)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

Hi Peter,

Thanks for the tip! I did not think of that.

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.

Cheers,

Paolo

p.s. Yes I am comparing PG installations on the same hardware and OS.

Test results below.
------------------------------
DEFAULT CONF VERSION TPS DIFF FROM PG95
PKEY ONLY
PG9.5 44633 0.0%
PG9.6 44947 0.7%
PG10 45069 1.0%
PG11 44868 0.5%
+ BOOL INDEX ONLY
PG9.5 43086 0.0%
PG9.6 43275 0.4%
PG10 43741 1.5%
PG11 43638 1.3%
+ INT INDEX ONLY
PG9.5 43169 0.0%
PG9.6 42306 -2.0%
PG10 43525 0.8%
PG11 44078 2.1%
+ TEXT INDEX ONLY
PG9.5 41918 0.0%
PG9.6 42117 0.5%
PG10 42339 1.0%
PG11 42680 1.8%

SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95
PKEY ONLY
PG9.5 104048 0.0%
PG9.6 99267 -4.6%
PG10 104050 0.0%
PG11 116392 11.9%
+ BOOL INDEX ONLY
PG9.5 68366 0.0%
PG9.6 71196 4.1%
PG10 53265 -22.1%
PG11 52031 -23.9%
+ INT INDEX ONLY
PG9.5 102265 0.0%
PG9.6 96054 -6.1%
PG10 101051 -1.2%
PG11 113278 10.8%
+ TEXT INDEX ONLY
PG9.5 103689 0.0%
PG9.6 95384 -8.0%
PG10 101014 -2.6%
PG11 112658 8.7%

FSYNC=OFF VERSION TPS DIFF FROM PG95
PKEY ONLY
PG9.5 91968 0.0%
PG9.6 88558 -3.7%
PG10 94235 2.5%
PG11 100683 9.5%
+ BOOL INDEX ONLY
PG9.5 64236 0.0%
PG9.6 67519 5.1%
PG10 47473 -26.1%
PG11 46812 -27.1%
+ INT INDEX ONLY
PG9.5 89027 0.0%
PG9.6 85790 -3.6%
PG10 91315 2.6%
PG11 96348 8.2%
+ TEXT INDEX ONLY
PG9.5 87348 0.0%
PG9.6 84636 -3.1%
PG10 89983 3.0%
PG11 95333 9.1%
------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
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(dot)saul(at)verizonconnect(dot)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(dot)saul(at)verizonconnect(dot)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 common in 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

Hi Peter,

After each run, I did a:
SELECT pg_total_relation_size( {index name} ) / COUNT(1)::FLOAT FROM
test_indexes
and the standard deviation between PG versions is < 1%.

Boolean and Int have about ~28-29 bytes per row.
Text has about ~77-78 bytes per row.
So not much change between PG versions.

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.

Are there any workarounds to this, as far as you can see?

Cheers,

Paolo

-----------------------------------------------------
INT_DATA = (RANDOM()*10)::INT % 2
synchronous_commit=off
-----------------------------------------------------
version TPS diff from pg95
PKEY
PG9.5 102899 0.0%
PG9.6 97983 -4.8%
PG10 104842 1.9%
PG11 115594 12.3%
BOOL INDEX
PG9.5 67284 0.0%
PG9.6 69950 4.0%
PG10 52404 -22.1%
PG11 49837 -25.9%
INT INDEX *
PG9.5 69014 0.0%
PG9.6 71588 3.7%
PG10 50918 -26.2%
PG11 49780 -27.9%
TEXT INDEX
PG9.5 102695 0.0%
PG9.6 95124 -7.4%
PG10 101953 -0.7%
PG11 113096 10.1%
-----------------------------------------------------
TEXT_DATA = MD5(((RANDOM()*10)::INT % 2)::TEXT)
synchronous_commit=off
-----------------------------------------------------
version TPS diff from pg95
PKEY
PG9.5 104257 0.0%
PG9.6 98600 -5.4%
PG10 104352 0.1%
PG11 116419 11.7%
BOOL INDEX
PG9.5 67919 0.0%
PG9.6 71416 5.1%
PG10 51486 -24.2%
PG11 50160 -26.1%
INT INDEX
PG9.5 102088 0.0%
PG9.6 94483 -7.4%
PG10 100541 -1.5%
PG11 112723 10.4%
TEXT INDEX *
PG9.5 63001 0.0%
PG9.6 63970 1.5%
PG10 45311 -28.1%
PG11 45556 -27.7%
-----------------------------------------------------

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

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.

>

--------------------------------------------------------------------------------------------------------------------------------------------
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(at)momjian(dot)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 +

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.

How would I know if the PostgreSQL team will be investigating this possible
regression issue?
OR can someone help me how to further identify the root cause in the code?

It would be nice if we can patch this out, or at least find the reasoning
for the slowness.
Does btree somehow have a global lock on a leaf node when it's being
modified?

Sorry for all the questions.

Cheers,

Paolo

On Fri, 1 Feb 2019 at 03:36, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> 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(at)momjian(dot)us>
> https://urldefense.proofpoint.com/v2/url?u=http-3A__momjian.us&d=DwIBAg&c=MPZC0Rv-kl4oeK4li6Jc92hnsiCC4tOZO_GSKscRWJE&r=VrrIZRoLr_yZJ3k_V9FTyEmoRy2AmFzy-R0X02P-nWQ&m=Buc9EXWO9Nu-8r264HE6ITfcN7ZkB4kATgNdoUnhNJI&s=1EWjlPEfMXj_eBPXv8wYRZZ4KgnmyOAfA2l7ukFsBu8&e=
> EnterpriseDB
> https://urldefense.proofpoint.com/v2/url?u=http-3A__enterprisedb.com&d=DwIBAg&c=MPZC0Rv-kl4oeK4li6Jc92hnsiCC4tOZO_GSKscRWJE&r=VrrIZRoLr_yZJ3k_V9FTyEmoRy2AmFzy-R0X02P-nWQ&m=Buc9EXWO9Nu-8r264HE6ITfcN7ZkB4kATgNdoUnhNJI&s=xt6ZYwRpqRSjSWnuKCuOFCeJHP7X2KLV-BN3cB4JLzE&e=
>
> + 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(dot)saul(at)verizonconnect(dot)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

Hi Peter,

That is unfortunate (both your results and the hash performance info).
We are still testing hash indexing and select/update/delete performance and
will keep that warning in mind.

I have replicated my setup by running the script by hand (see below).
Can you see anything in the test setup that seems to be wrong?

Thanks for all your feedback.

Cheers,

Paolo

------------------------------------
Notes:
Aside from the port number, all other settings are using default values,
and is only overwritten when we pass in -o on pg_ctl at startup.
The pgbench outputs are grep'ed and awk'ed to only show tps including
connections establishing.
Changed timing run for pgbench to only 10 secs. This was enough to show the
pattern and I didn't want to run the pgbenches again @ 20 mins per data
point.
Note how I am changing the insert sql and watch the pgbench patterns change
when sync commit is off/on and when the indexes are applied to bool or text
columns.
The data is still consistent with my initial results :(. If anyone can spot
where my tests could have gone wrong that would be very much appreciated.

Raw commandline output below.

------------------------------------

demo_server $ cat /etc/*release
CentOS Linux release 7.6.1810 (Core)
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

CentOS Linux release 7.6.1810 (Core)
CentOS Linux release 7.6.1810 (Core)
demo_server $ free -m
total used free shared buff/cache
available
Mem: 257773 1941 248149 1352 7683
253379
Swap: 0 0 0
demo_server $ grep cores /proc/cpuinfo |wc -l
64
demo_server $ head /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 79
model name : Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz
stepping : 1
microcode : 0xb000031
cpu MHz : 1820.739
cache size : 46080 KB
physical id : 0
demo_server $ df -h /var/data/
Filesystem Size Used Avail Use% Mounted on
/dev/xvdn1 50G 14G 37G 28% /var/data
demo_server $
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D
/var/data/test95_data
server starting
demo_server $ < 2019-02-01 00:48:02.079 UTC >LOG: redirecting log output
to logging collector process
< 2019-02-01 00:48:02.079 UTC >HINT: Future log output will appear in
directory "pg_log".

demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D
/var/data/test11_data
waiting for server to start....2019-02-01 00:50:30.745 UTC [47358] LOG:
listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 00:50:30.745 UTC [47358] LOG: listening on IPv6 address "::",
port 11000
2019-02-01 00:50:30.747 UTC [47358] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 00:50:30.750 UTC [47358] LOG: listening on Unix socket
"/tmp/.s.PGSQL.11000"
2019-02-01 00:50:30.765 UTC [47358] LOG: redirecting log output to logging
collector process
2019-02-01 00:50:30.765 UTC [47358] HINT: Future log output will appear in
directory "log".
done
server started
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $
demo_server $
demo_server $ echo 'INSERT INTO test_indexes (bool_data , int_data ,
text_data ) VALUES ( (RANDOM()*10)::INT % 2 = 0, RANDOM()*10000,
MD5((RANDOM()*1000)::TEXT) );' > /home/postgres/insert_test.sql
demo_server $
demo_server $
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
43266.931198
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
45366.444002
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D
/var/data/test95_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D
/var/data/test11_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D
/var/data/test95_data -o "-c synchronous_commit=0"
server starting
demo_server $ < 2019-02-01 02:48:01.396 UTC >LOG: redirecting log output
to logging collector process
< 2019-02-01 02:48:01.396 UTC >HINT: Future log output will appear in
directory "pg_log".

demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D
/var/data/test11_data -o "-c synchronous_commit=0"
waiting for server to start....2019-02-01 02:48:33.186 UTC [64491] LOG:
listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 02:48:33.186 UTC [64491] LOG: listening on IPv6 address "::",
port 11000
2019-02-01 02:48:33.187 UTC [64491] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 02:48:33.190 UTC [64491] LOG: listening on Unix socket
"/tmp/.s.PGSQL.11000"
2019-02-01 02:48:33.204 UTC [64491] LOG: redirecting log output to logging
collector process
2019-02-01 02:48:33.204 UTC [64491] HINT: Future log output will appear in
directory "log".
done
server started
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
104986.476772
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
117705.555724
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
66106.908670
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
50950.234033
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
102645.086816
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
112472.667768
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
103298.461287
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
113698.041576
demo_server $
demo_server $
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D
/var/data/test95_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D
/var/data/test11_data stop
waiting for server to shut down.... done
server stopped
demo_server $
demo_server $
demo_server $ echo 'INSERT INTO test_indexes (bool_data , int_data ,
text_data ) VALUES ( (RANDOM()*10)::INT % 2 = 0, RANDOM()*10000,
MD5(((RANDOM()*1000)::BIGINT % 2)::TEXT) );' >
/home/postgres/insert_test.sql
demo_server $
demo_server $
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D
/var/data/test95_data
server starting
demo_server $ < 2019-02-01 03:26:10.233 UTC >LOG: redirecting log output
to logging collector process
< 2019-02-01 03:26:10.233 UTC >HINT: Future log output will appear in
directory "pg_log".

demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D
/var/data/test11_data
waiting for server to start....2019-02-01 03:26:31.248 UTC [71705] LOG:
listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 03:26:31.248 UTC [71705] LOG: listening on IPv6 address "::",
port 11000
2019-02-01 03:26:31.249 UTC [71705] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 03:26:31.252 UTC [71705] LOG: listening on Unix socket
"/tmp/.s.PGSQL.11000"
2019-02-01 03:26:31.267 UTC [71705] LOG: redirecting log output to logging
collector process
2019-02-01 03:26:31.267 UTC [71705] HINT: Future log output will appear in
directory "log".
done
server started
demo_server $
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
/usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS
test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
47055.402951
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
47844.925367
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
44519.824705
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
43483.982157
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
43995.744128
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
44881.281223
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
43505.532207
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
43480.194225
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D
/var/data/test95_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D
/var/data/test11_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D
/var/data/test95_data -o "-c synchronous_commit=0"
sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D /var/data/test11_data -o
"-c synchronous_commit=0"
server starting
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D
/var/data/test11_data -o "-c synchronous_commit=0"
waiting for server to start....2019-02-01 03:38:13.884 UTC [75407] LOG:
listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 03:38:13.884 UTC [75407] LOG: listening on IPv6 address "::",
port 11000
2019-02-01 03:38:13.885 UTC [75407] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 03:38:13.888 UTC [75407] LOG: listening on Unix socket
"/tmp/.s.PGSQL.11000"
2019-02-01 03:38:13.902 UTC [75407] LOG: redirecting log output to logging
collector process
2019-02-01 03:38:13.902 UTC [75407] HINT: Future log output will appear in
directory "log".
done
server started
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
105304.025276
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
117373.057477
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
67686.991879
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
52750.704496
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
103595.827322
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
112841.729898
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF
EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL,
bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY
(test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on
test_indexes using btree (text_data);"
/usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes
using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on
test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep
tps|head -n1|awk '{print " ",$3}'
62094.775577
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep
tps|head -n1|awk '{print " ",$3}'
46815.366392
demo_server $

--------------------------------------------------------------------------------------------------------------------------------------------
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 Peter,

The last post was too long. I think I have a much more simpler example that
is easier to replicate.

Cheers,

Paolo

------------------------------
postgres=# create table lowc_test (id bigserial, int_data int, primary
key(id));
CREATE TABLE
postgres=# \timing
Timing is on.
postgres=# truncate lowc_test;
postgres=# show synchronous_commit;
synchronous_commit
--------------------
off
(1 row)

Time: 0.445 ms
postgres=# show fsync;
fsync
-------
on
(1 row)

Time: 0.331 ms
postgres=# -- no secondary index
postgres=#
postgres=# insert into lowc_test (int_data) select s from
generate_series(1,5000000) s;
INSERT 0 5000000
Time: 12440.497 ms (00:12.440)
postgres=# truncate lowc_test;
TRUNCATE TABLE
Time: 68.427 ms
postgres=# create index on lowc_test using btree (int_data);
CREATE INDEX
Time: 2.449 ms
postgres=# -- 2ndary index w/ normal sequence of values inserted
postgres=#
postgres=# insert into lowc_test (int_data) select s from
generate_series(1,5000000) s;
INSERT 0 5000000
Time: 17221.095 ms (00:17.221)
postgres=# -- 2ndary index w/ single value inserted
postgres=#
postgres=# truncate lowc_test;
TRUNCATE TABLE
Time: 83.846 ms
postgres=# insert into lowc_test (int_data) select 42 from
generate_series(1,5000000) s;
INSERT 0 5000000
Time: 21440.356 ms (00:21.440)
------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
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(dot)saul(at)verizonconnect(dot)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

Hi Peter,

Thanks! I see.
It looks like I cannot replicate it in one transaction, but I have to use
pgbench instead.
Another simple test output is below.

What do you think?

Cheers,

Paolo

p.s.
synchronous_commit = off
pg9.5 on port 9500
pg11 on port 11000

-------
demo_server.pg $ echo 'INSERT INTO lowc_test (int_data) SELECT 42; --
arbitrary ' > /home/postgres/simple_insert_low.sql
demo_server.pg $

----- POSTGRESQL 9.5 -----
demo_server.pg $
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c 'CREATE TABLE
lowc_test (id bigserial, int_data int, PRIMARY KEY(id))'
CREATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 9500 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 1083976
latency average = 0.554 ms
tps = 108379.219155 (including connections establishing)
tps = 108472.988431 (excluding connections establishing)
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c 'CREATE INDEX
ON lowc_test USING BTREE (int_data);'
CREATE INDEX
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c 'TRUNCATE
lowc_test;'
TRUNCATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 9500 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 478797
latency average = 1.254 ms
*tps = 47865.701374 (including connections establishing)*
*tps = 47909.167492 (excluding connections establishing)*

----- POSTGRESQL 11 -----
demo_server.pg $
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c 'CREATE TABLE
lowc_test (id bigserial, int_data int, PRIMARY KEY(id))'
CREATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 11000 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 1215185
latency average = 0.494 ms
tps = 121488.366924 (including connections establishing)
tps = 121610.790950 (excluding connections establishing)
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c 'CREATE INDEX
ON lowc_test USING BTREE (int_data);'
CREATE INDEX
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c 'TRUNCATE
lowc_test;'
TRUNCATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 11000 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 341168
latency average = 1.759 ms
*tps = 34100.743631 (including connections establishing)*
*tps = 34137.949909 (excluding connections establishing)*

On Mon, 4 Feb 2019 at 12:10, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Sun, Feb 3, 2019 at 2:45 PM Saul, Jean Paolo
> <paolo(dot)saul(at)verizonconnect(dot)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(at)momjian(dot)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 +

Thank you, yes that makes sense.
We only use partial Boolean indexes on large tables
or normal Boolean indexes on small (0-10M rows) queue tables that get
cleared down.

On Tue, 5 Feb 2019 at 12:08, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> 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(at)momjian(dot)us>
> https://urldefense.proofpoint.com/v2/url?u=http-3A__momjian.us&d=DwIBAg&c=MPZC0Rv-kl4oeK4li6Jc92hnsiCC4tOZO_GSKscRWJE&r=VrrIZRoLr_yZJ3k_V9FTyEmoRy2AmFzy-R0X02P-nWQ&m=A8B3Bqt35dF1w-Myg_aaGo60wWI-OYNQRVwNYoe7REo&s=4Et9835fubwbTEUhqaEGIF57CiiUPdgbFEMZpPA_5Zw&e=
> EnterpriseDB
> https://urldefense.proofpoint.com/v2/url?u=http-3A__enterprisedb.com&d=DwIBAg&c=MPZC0Rv-kl4oeK4li6Jc92hnsiCC4tOZO_GSKscRWJE&r=VrrIZRoLr_yZJ3k_V9FTyEmoRy2AmFzy-R0X02P-nWQ&m=A8B3Bqt35dF1w-Myg_aaGo60wWI-OYNQRVwNYoe7REo&s=3JAKbpzBP8EeAuCgkGz6GWh3JrU85rCssCHpQV85FV8&e=
>
> + 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.

Hi Peter,

I did some additional testing using perf (I'm not a perf expert) and on AWS
there seemed to be a lot of calls to the hypervisor.
So I got hold of a real server from our inventory (
https://www.dell.com/en-nz/work/shop/povw/poweredge-r640)

Initial tests still show the same behavior for PG11 (see below).
I ran perf during pgbench testing for the two PG versions and pasted the
top calls below.
It looks like PG11 with low cardinality index is waiting for a futex on
_bt_search
and a semaphore lock on _bt_relandgetbuf.
Again I'm no perf expert and my analysis is possibly wrong.
Running pgbench using hashes instead shows no futex usage on pg11.

------------------------------------------
r640.tx $ head /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 85
model name : Intel(R) Xeon(R) Gold 5120 CPU @ 2.20GHz
stepping : 4
microcode : 0x2000050
cpu MHz : 2200.000
cache size : 19712 KB
physical id : 0
r640.tx $ grep cores /proc/cpuinfo |wc -l
56
r640.tx $ free -m
total used free shared buff/cache
available
Mem: 515376 4154 508564 313 2657
509310
Swap: 4095 0 4095
r640.tx $ psql -Upostgres -p9500 -c "create table lowc_test (id bigserial,
int_data int, primary key(id)); create index on lowc_test using btree
(int_data)";
CREATE INDEX
r640.tx $ psql -Upostgres -p11000 -c "create table lowc_test (id bigserial,
int_data int, primary key(id)); create index on lowc_test using btree
(int_data)";
CREATE INDEX
r640.tx $ echo 'insert into lowc_test (int_data) select random()*100000;' >
/home/postgres/insert_lowc.sql
r640.tx $ psql -Upostgres -p9500 -c "truncate lowc_test";
/usr/pgsql-11/bin/pgbench -n -Upostgres -f /home/postgres/insert_lowc.sql
-c50 -j60 -T10 -p 9500|grep tps
TRUNCATE TABLE
tps = 131279.163871 (including connections establishing)
tps = 131730.404978 (excluding connections establishing)
r640.tx $ psql -Upostgres -p11000 -c "truncate lowc_test";
/usr/pgsql-11/bin/pgbench -n -Upostgres -f /home/postgres/insert_lowc.sql
-c50 -j60 -T10 -p 11000|grep tps
TRUNCATE TABLE
tps = 144822.892940 (including connections establishing)
tps = 145296.472528 (excluding connections establishing)
r640.tx $
r640.tx $ echo 'insert into lowc_test (int_data) select 42;' >
/home/postgres/insert_lowc.sql
r640.tx $
r640.tx $ psql -Upostgres -p9500 -c "truncate lowc_test";
/usr/pgsql-11/bin/pgbench -n -Upostgres -f /home/postgres/insert_lowc.sql
-c50 -j60 -T10 -p 9500|grep tps
TRUNCATE TABLE
tps = 20137.416962 (including connections establishing)
tps = 20204.697353 (excluding connections establishing)
r640.tx $ psql -Upostgres -p11000 -c "truncate lowc_test";
/usr/pgsql-11/bin/pgbench -n -Upostgres -f /home/postgres/insert_lowc.sql
-c50 -j60 -T10 -p 11000|grep tps
TRUNCATE TABLE
tps = 17361.186258 (including connections establishing)
tps = 17420.454363 (excluding connections establishing)

---------------------------

PERF PG95

+ 26.19% 0.18% postgres /proc/kcore k [k]
system_call_fastpath
+ 26.13% 0.23% postgres /usr/lib64/libc-2.17. B [.]
__GI___semop
+ 17.42% 0.18% postgres /proc/kcore k [k]
sys_semop
+ 17.24% 0.06% postgres /proc/kcore k [k]
sys_semtimedop
+ 15.38% 2.06% postgres /proc/kcore k [k]
SYSC_semtimedop
+ 11.62% 0.00% postgres [unknown] ! [k]
0000000000000000
+ 8.02% 0.00% postgres [unknown] ! [k]
0x00007ffef3e98d30
+ 6.13% 0.34% postgres /proc/kcore k [k]
schedule
+ 5.95% 1.84% postgres /proc/kcore k [k]
try_to_wake_up
+ 5.45% 0.87% postgres /proc/kcore k [k]
__schedule
+ 5.41% 0.06% postgres /proc/kcore k [k]
wake_up_sem_queue_do
+ 5.34% 0.02% postgres /proc/kcore k [k]
wake_up_process

---------------------------

PERF PG11

+ 16.90% 0.08% postgres [kernel.kallsyms] [k]
system_call_fastpath
+ 12.18% 0.15% postgres [kernel.kallsyms] [k] sys_futex
+ 12.03% 0.13% postgres [kernel.kallsyms] [k] do_futex
+ 8.95% 0.00% postgres [unknown] [k]
0x0000000000000080
+ 8.80% 0.13% postgres libpthread-2.17.so [.]
do_futex_wait.constprop.1
+ 7.76% 0.29% postgres libpthread-2.17.so [.] sem_post@
@GLIBC_2.2.5
+ 6.44% 0.29% postgres [kernel.kallsyms] [k] futex_wait
+ 5.27% 0.33% postgres [kernel.kallsyms] [k] futex_wake

--------------------------------------------------------------------------------------------------------------------------------------------
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 All,

I have found a short term workaround for this. You recreate the index with
an added second column with high-cardinality.
ex. CREATE INDEX ON test_indexes USING BTREE (low_cardinality_column,
primary_key_id);

We have around 1,500 tables with low-cardinality indexes, so its not the
best workaround but one that restores the performance bump for PG11.

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.

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

Cheers,

Paolo

--------------------------------------------------------------------------------------------------------------------------------------------
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(dot)saul(at)verizonconnect(dot)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:

/message-id/flat/CAH2-Wzmf0fvVhU+SSZpGW4Qe9t--j_DmXdX3it5JcdB8FF2EsA(at)mail(dot)gmail(dot)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

Hi Peter,

Thanks for the reply, though I probably am not understanding your reply
correctly.

On Mon, 11 Feb 2019 at 13:44, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

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

Even for the numbers quoted above, that is a ~14% decrease in performance.
My tests (below) show around ~17% decrease in performance.
49347.886303 tps in PG9.5
41119.829583 tps in PG11

Are you saying that 14% decreased performance is not substantial enough for
this to be a bug, or are you saying you are not able to replicate the
results?
If it is the later, please note I am only able to replicate this using
pgbench(v11), and all single transaction tests I've done do not show this
loss.

> In general, I'm confused about why you're concerned about v11 in
> particular here.
>

Since our production servers have around 4-1500 table indexes with low
cardinality, once we upgrade from PG9.5 to PG11, I am assuming that there
will be at least 15-20% performance decrease with inserts. That is my main
concern.
We are quite happy will all the other performance improvements on PG11 and
are quite excited to roll it out.

Does that make sense?

And thanks for taking the time to look into this.

Cheers,

Paolo

-----
Another simple test case:

demo_server.postgres $ cat create_table.sql
drop table if exists test_indexes;
CREATE TABLE test_indexes (id BIGSERIAL, bool_data BOOLEAN, int_data INT,
text_data TEXT, PRIMARY KEY(id));
CREATE INDEX ON test_indexes USING BTREE (int_data);
demo_server.postgres $ cat insert.sql
INSERT INTO test_indexes (bool_data , int_data , text_data )
VALUES ( (RANDOM()*10)::INT % 2 = 0, 42, MD5((RANDOM()*1000)::TEXT) );
demo_server.postgres $ for p in 9500 11000; do
> echo "* DB ON PORT $p *";
> /usr/pgsql-11/bin/psql -Upostgres -qp $p -f create_table.sql;
> /usr/pgsql-11/bin/pgbench -Upostgres -f insert.sql -n -c 60 -j 60 -T1800
postgres -p $p;
> done
* DB ON PORT 9500 *
transaction type: insert.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 1800 s
number of transactions actually processed: 88826370
latency average = 1.216 ms
tps = 49347.886303 (including connections establishing)
tps = 49348.107350 (excluding connections establishing)
* DB ON PORT 11000 *
transaction type: insert.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 1800 s
number of transactions actually processed: 74015762
latency average = 1.459 ms
tps = 41119.829583 (including connections establishing)
tps = 41120.057764 (excluding connections establishing)

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

What settings have you used in both cases?

Peter Geoghegan
(Sent from my phone)

Aside from the ports:

listen_addresses = '*'
max_connections = 300
max_wal_senders = 0 -- (on PG11)
synchronous_commit = off

--------------------------------------------------------------------------------------------------------------------------------------------
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(dot)saul(at)verizonconnect(dot)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

Yes its 128MB, the default.

You raise an interesting point, Peter.
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.

I have updated the testing rigs to use 40GB of shared buffers with the same
results. :(

--------------

demo_server.postgres $ for p in 9500 11000; do echo "* DB ON PORT $p *";
/usr/pgsql-11/bin/psql -Upostgres -qp $p -f create_table.sql;
/usr/pgsql-11/bin/psql -Upostgres -qtp $p -c"show shared_buffers";
/usr/pgsql-11/bin/pgbench -Upostgres -f insert.sql -n -c 60 -j 60 -T120
postgres -p $p;/usr/pgsql-11/bin/psql -Upostgres -qtp $p -c"select 'table
size = ' || pg_size_pretty(pg_total_relation_size('test_indexes'))"; done
* DB ON PORT 9500 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 4493742
latency average = 1.602 ms
tps = 37447.000308 (including connections establishing)
tps = 37449.413044 (excluding connections establishing)
table size = 581 MB

* DB ON PORT 11000 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 3606004
latency average = 1.997 ms
tps = 30049.353360 (including connections establishing)
tps = 30052.041139 (excluding connections establishing)
table size = 467 MB

On Tue, 12 Feb 2019 at 13:49, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Mon, Feb 11, 2019 at 4:47 PM Saul, Jean Paolo
> <paolo(dot)saul(at)verizonconnect(dot)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(dot)saul(at)verizonconnect(dot)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

Using prepared and extended. No luck.

-----------------------
demo_server.postgres $ for p in 9500 11000; do echo "* DB ON PORT $p *";
/usr/pgsql-11/bin/psql -Upostgres -qp $p -f create_table.sql;
/usr/pgsql-11/bin/psql -Upostgres -qtp $p -c"show shared_buffers";
/usr/pgsql-11/bin/pgbench -Upostgres -M *prepared *-f insert.sql -n -c 60
-j 60 -T120 postgres -p $p;/usr/pgsql-11/bin/psql -Upostgres -qtp $p
-c"select 'table size = ' ||
pg_size_pretty(pg_total_relation_size('test_indexes'))"; done
* DB ON PORT 9500 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: prepared
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 4555946
latency average = 1.580 ms
tps = 37965.164927 (including connections establishing)
tps = 37967.496937 (excluding connections establishing)
table size = 589 MB

* DB ON PORT 11000 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: prepared
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 3550999
latency average = 2.028 ms
tps = 29590.591808 (including connections establishing)
tps = 29593.242681 (excluding connections establishing)
table size = 461 MB

demo_server.postgres $ for p in 9500 11000; do echo "* DB ON PORT $p *";
/usr/pgsql-11/bin/psql -Upostgres -qp $p -f create_table.sql;
/usr/pgsql-11/bin/psql -Upostgres -qtp $p -c"show shared_buffers";
/usr/pgsql-11/bin/pgbench -Upostgres -M *extended *-f insert.sql -n -c 60
-j 60 -T120 postgres -p $p;/usr/pgsql-11/bin/psql -Upostgres -qtp $p
-c"select 'table size = ' ||
pg_size_pretty(pg_total_relation_size('test_indexes'))"; done
* DB ON PORT 9500 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: extended
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 4511910
latency average = 1.596 ms
tps = 37598.593591 (including connections establishing)
tps = 37601.100548 (excluding connections establishing)
table size = 584 MB

* DB ON PORT 11000 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: extended
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 3553357
latency average = 2.026 ms
tps = 29610.805470 (including connections establishing)
tps = 29613.253046 (excluding connections establishing)
table size = 459 MB

--------------------------------------------------------------------------------------------------------------------------------------------
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(dot)saul(at)verizonconnect(dot)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

Thanks for the tip Peter. Unfortunately it did not move the needle. Just
curious what numbers are you getting when you are testing?

-------------------
PG9.5
tps = 37595.067794 (including connections establishing)

PG11
tps = 28709.328124 (including connections establishing)

On Tue, 12 Feb 2019 at 14:42, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Mon, Feb 11, 2019 at 5:36 PM Saul, Jean Paolo
> <paolo(dot)saul(at)verizonconnect(dot)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(dot)saul(at)verizonconnect(dot)com> wrote:
> Thanks for the tip Peter. Unfortunately it did not move the needle. Just curious what numbers are you getting when you are 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

Thanks.

I am only able to get over 100K if I change the insert query from:
INSERT INTO test_indexes (int_data )
VALUES ( (RANDOM()*10000)::BIGINT % 2 ); -- note the modulus
38057.023869 tps for PG9.5
28257.312372 tps for PG11

to:
INSERT INTO test_indexes (int_data )
VALUES ( (RANDOM()*10000)::BIGINT );
83065.111054 tps for PG9.5
130080.356992 tps for PG11

So I am assuming at this point you are not able to replicate this behavior?
It might be a setting we have on our end, I just don't know what it its yet.

On Tue, 12 Feb 2019 at 15:06, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Mon, Feb 11, 2019 at 6:01 PM Saul, Jean Paolo
> <paolo(dot)saul(at)verizonconnect(dot)com> wrote:
> > Thanks for the tip Peter. Unfortunately it did not move the needle. Just
> curious what numbers are you getting when you are 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.

I think I might have found the tipping point for the performance dip, at
least on our servers.
It looks like an integer btree index with distinct values less than four (<
4) is affected.
After that PG11 starts to gain significant advantage over PG9.5.

I am still concerned that no one is able to replicate this. :(

--------
dr640.texas.postgres $ cat insert_template.sql
INSERT INTO test_indexes (int_data )
VALUES ( (RANDOM()* __RANGE__ )::BIGINT );

dr640.texas.postgres $ for r in 0 1 2 3 4 10 100 1000 10000; do echo "RANGE
0 - $r"; for p in 9500 11000; do echo " PG $p"; /usr/pgsql-11/bin/psql
-Upostgres -qp $p -f create_table.sql; cat insert_template.sql >
insert.sql; sed -i "s/__RANGE__/$r/g" insert.sql; /usr/pgsql-11/bin/pgbench
-Upostgres -M prepared -f insert.sql -n -c 60 -j 60 -T120 postgres -p
$p|grep including|awk '{print $3}'|awk -F. '{print " TPS ", $1}';
done; done
RANGE 0 - 0
PG 9500
TPS 18030
PG 11000
TPS 14760
RANGE 0 - 1
PG 9500
TPS 39566
PG 11000
TPS 32790
RANGE 0 - 2
PG 9500
TPS 46839
PG 11000
TPS 39912
(PG11 is now faster with four distinct values in the indexed column)
RANGE 0 - 3
PG 9500
TPS 63432
PG 11000
TPS 69568
RANGE 0 - 4
PG 9500
TPS 63783
PG 11000
TPS 93460
RANGE 0 - 10
PG 9500
TPS 64047
PG 11000
TPS 97580
RANGE 0 - 100
PG 9500
TPS 64200
PG 11000
TPS 98370
RANGE 0 - 1000
PG 9500
TPS 66030
PG 11000
TPS 105207
RANGE 0 - 10000
PG 9500
TPS 63495
PG 11000
TPS 98403

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

Does having < four distinct values in the btree index have any known
significance wrt to btree performance?

--------------------------------------------------------------------------------------------------------------------------------------------
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(dot)saul(at)verizonconnect(dot)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