CREATE INDEX testindx ON testtab (col1, col2, col3, col4, col5, col6, col7, col8, col9);
Performance measurement tests: Ran12 times to eliminate run to run latencies.
==========================
VACUUM FULL;
BEGIN;
UPDATE testtab SET col2 = md5(random()::text);
ROLLBACK;
Response time recorded shows there is a much higher increase in response time from 10% to 25% after the patch.
After doing some tests on my side, I now think that there is something else going on, unrelated to the patch. I ran the same benchmark on AWS i2.xlarge machine with 32GB RAM. shared_buffers set to 16GB, max_wal_size to 256GB, checkpoint_timeout to 60min and autovacuum off.
I compared master and v19, every time running 6 runs of the test. The database was restarted whenever changing binaries, tables dropped/recreated and checkpoint taken after each restart (but not between 2 runs, which I believe what you did too.. but correct me if that's a wrong assumption).
Instead of col2, I am updating col9, but that's probably not too much relevant.
VACUUM FULL;
BEGIN;
UPDATE testtab SET col9 = md5(random()::text);
ROLLBACK;
First set of 6 runs with master:
163629.8
181183.8
194788.1
194606.1
194589.9
196002.6
(database restart, table drop/create, checkpoint)
First set of 6 runs with v19:
190566.55
228274.489
238110.202
239304.681
258748.189
284882.4
(database restart, table drop/create, checkpoint)
Second set of 6 runs with master:
232267.5
298259.6
312315.1
341817.3
360729.2
385210.7
This looks quite weird to me. Obviously these numbers are completely non-comparable. Even the time for VACUUM FULL goes up with every run.
May be we can blame it on AWS instance completely, but the pattern in your tests looks very similar where the number slowly and steadily keeps going up. If you do complete retest but run v18/v19 first and then run master, may be we'll see a complete opposite picture?
