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.