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

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.