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