On Thu, 17 Jun 2021 at 17:14, Matthias van de Meent <boekewurm+postg...@gmail.com> wrote: > > I'll try to > benchmark the patches in this patchset (both 0001, and 0001+0002) in > the upcoming weekend.
Somewhat delayed, benchmark results are attached. These are based on 7
iterations of the attached benchmark script ('scratch.sql'), with the
latest 'UK Price Paid' dataset. Again, the index_test table is an
unlogged copy of the land_registry_price_paid_uk table, with one
additional trailing always_null column.
Results for 0001 are quite good in the target area of multi-column
indexes in which attcacheoff cannot be used (2-4% for insertion
workloads, 4-12% for reindex workloads), but regresses slightly for
the single unique column insertion test, and are quite a bit worse for
both insert and reindex cases for the attcacheoff-enabled multi-column
index (4% and 18% respectively (!)).
With 0001+0002, further improvements are made in the target area (now
4-7% for the various insertion workloads, 5-14% for reindex). The
regression in the insert- and reindex-workload in attcacheoff-enabled
multi-column indexes is still substantial, but slightly less bad (down
to a 2% and 15% degradation respectively).
Evidently, this needs improvements in the (likely common)
attcacheoff-enabled multi-column case; as I don't think we can
reasonably commit a 10+% regression. I'll work on that this weekend.
Kind regards,
Matthias van de Meent
Benchmarks were all performed on WSL2 running Debian 10, on an AMD
5950X, with shared_buffers = 15GB (which should fit the dataset three
times), enable_indexscan = off, autovacuum disabled, and parallel
workers disabled on the tables, so that the results should be about as
stable as it gets.
./tmp_install/usr/local/pgsql/bin/pgbench --transactions=7 --report-latencies
--no-vacuum --client=1
--log
--file=/mnt/c/Users/Matthias/AppData/Roaming/JetBrains/CLion2021.1/scratches/scratch.sql
testing
pgbench (14beta1)
transaction type:
/mnt/c/Users/Matthias/AppData/Roaming/JetBrains/CLion2021.1/scratches/scratch.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 7
number of transactions actually processed: 7/7
latency average = 507826.653 ms
initial connection time = 0.762 ms
tps = 0.001969 (without initial connection time)
statement latencies in milliseconds:
0.093 BEGIN;
22.484 CREATE UNIQUE INDEX uidx ON index_test USING btree
(transaction);
61833.583 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
9374.550 REINDEX INDEX uidx;
0.155 DROP INDEX uidx;
4.500 TRUNCATE index_test;
1.861 CREATE INDEX ccl ON index_test USING btree (county, city,
locality);
72790.944 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
42364.419 REINDEX INDEX ccl;
0.155 DROP INDEX ccl;
306.673 TRUNCATE index_test;
5.833 CREATE INDEX ccl_collated ON index_test USING btree (county
COLLATE "en_US", city, locality);
84156.380 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
42272.279 REINDEX INDEX ccl_collated;
0.149 DROP INDEX ccl_collated;
332.287 TRUNCATE index_test;
10.861 CREATE INDEX accl ON index_test USING btree (always_null,
county, city, locality);
78607.320 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
50884.837 REINDEX INDEX accl;
0.154 DROP INDEX accl;
337.609 TRUNCATE index_test;
15.329 CREATE INDEX tnt ON index_test USING btree (transfer_date,
newly_built, transaction);
48327.733 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
15111.673 REINDEX INDEX tnt;
0.150 DROP INDEX tnt;
351.298 TRUNCATE index_test;
713.319 COMMIT;
pgbench_log.master.26482
Description: Binary data
./tmp_install/usr/local/pgsql/bin/pgbench --transactions=7 --report-latencies
--no-vacuum --client=1
--log
--file=/mnt/c/Users/Matthias/AppData/Roaming/JetBrains/CLion2021.1/scratches/scratch.sql
testing
pgbench (14beta1)
transaction type:
/mnt/c/Users/Matthias/AppData/Roaming/JetBrains/CLion2021.1/scratches/scratch.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 7
number of transactions actually processed: 7/7
latency average = 499049.630 ms
initial connection time = 0.732 ms
tps = 0.002004 (without initial connection time)
statement latencies in milliseconds:
0.091 BEGIN;
22.849 CREATE UNIQUE INDEX uidx ON index_test USING btree
(transaction);
63756.013 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
9357.520 REINDEX INDEX uidx;
0.173 DROP INDEX uidx;
4.506 TRUNCATE index_test;
1.699 CREATE INDEX ccl ON index_test USING btree (county, city,
locality);
71118.264 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
39849.243 REINDEX INDEX ccl;
0.145 DROP INDEX ccl;
305.330 TRUNCATE index_test;
5.833 CREATE INDEX ccl_collated ON index_test USING btree (county
COLLATE "en_US", city, locality);
83673.213 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
40427.121 REINDEX INDEX ccl_collated;
0.169 DROP INDEX ccl_collated;
339.383 TRUNCATE index_test;
10.314 CREATE INDEX accl ON index_test USING btree (always_null,
county, city, locality);
75521.468 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
44901.042 REINDEX INDEX accl;
0.159 DROP INDEX accl;
338.667 TRUNCATE index_test;
13.447 CREATE INDEX tnt ON index_test USING btree (transfer_date,
newly_built, transaction);
50472.199 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
17867.951 REINDEX INDEX tnt;
0.145 DROP INDEX tnt;
351.942 TRUNCATE index_test;
710.720 COMMIT;
pgbench_log.0001.20660
Description: Binary data
./tmp_install/usr/local/pgsql/bin/pgbench --transactions=7 --report-latencies
--no-vacuum --client=1
--log
--file=/mnt/c/Users/Matthias/AppData/Roaming/JetBrains/CLion2021.1/scratches/scratch.sql
testing
pgbench (14beta1)
transaction type:
/mnt/c/Users/Matthias/AppData/Roaming/JetBrains/CLion2021.1/scratches/scratch.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 7
number of transactions actually processed: 7/7
latency average = 487476.847 ms
initial connection time = 0.718 ms
tps = 0.002051 (without initial connection time)
statement latencies in milliseconds:
0.085 BEGIN;
259.079 CREATE UNIQUE INDEX uidx ON index_test USING btree
(transaction);
63115.157 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
9505.625 REINDEX INDEX uidx;
0.177 DROP INDEX uidx;
4.592 TRUNCATE index_test;
1.686 CREATE INDEX ccl ON index_test USING btree (county, city,
locality);
69923.493 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
40202.434 REINDEX INDEX ccl;
0.151 DROP INDEX ccl;
308.214 TRUNCATE index_test;
5.394 CREATE INDEX ccl_collated ON index_test USING btree (county
COLLATE "en_US", city, locality);
80143.752 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
39677.198 REINDEX INDEX ccl_collated;
0.152 DROP INDEX ccl_collated;
335.813 TRUNCATE index_test;
15.291 CREATE INDEX accl ON index_test USING btree (always_null,
county, city, locality);
72638.613 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
43370.492 REINDEX INDEX accl;
0.154 DROP INDEX accl;
339.273 TRUNCATE index_test;
13.803 CREATE INDEX tnt ON index_test USING btree (transfer_date,
newly_built, transaction);
49171.589 INSERT INTO index_test SELECT * FROM
land_registry_price_paid_uk;
17378.554 REINDEX INDEX tnt;
0.153 DROP INDEX tnt;
353.809 TRUNCATE index_test;
712.091 COMMIT;
pgbench_log.0001+0002.22858
Description: Binary data
scratch.sql
Description: application/sql
