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

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.