CF 7365 v10: measurements ========================= All on master a054b77e40d, production builds (-O2 -g, no cassert): master, and master + v10-0001. Counts are user-space instructions under perf stat, 3 runs per build in rotated order, median. The scripts are attached (david-repro.sh.txt, ops-counts.sh.txt, fk-lookup-counts.sh.txt). 1. David's schema at full scale ------------------------------- 957,311 constraints (859,329 NOT NULL, 32,686 PK, 32,625 CHECK, 32,623 FK, 48 UNIQUE) over 32,300 partitions; every FK references the same table, dim_ref, so master's scans read every row and find nothing. pg_upgrade from master, on a 24-thread laptop in light desktop use: master, 0001 and the index from David's last message, 3 rounds in rotated order, then 3 more master runs. A run counts only if the 1-min load average stayed below 5 (sampled every 30 s; for the last three runs, read at the end of each run). Three master runs overlapped load spikes of 12 to 56 and are listed but not counted: build started restore total load master 11-03 18:43 1443.5s 1481.3s 56.54 disturbed, not counted 0001 11-03 19:08 94.2s 116.9s 3.36 David's patch 11-03 19:11 85.5s 118.3s 2.51 0001 11-03 19:13 93.6s 115.7s 2.45 David's patch 11-03 19:15 86.5s 122.1s 3.12 master 11-03 19:17 1085.6s 1120.6s 3.65 David's patch 11-03 19:36 85.9s 119.6s 3.04 master 11-03 19:39 1411.5s 1447.0s 55.23 disturbed, not counted 0001 11-03 20:04 79.0s 99.7s 1.90 master 11-03 21:21 983.0s 1015.0s 1.27 master 11-03 21:38 1462.5s 1501.7s 12.12 disturbed, not counted master 11-03 22:05 1093.3s 1128.8s 3.18 median of the quiet runs: master n=3 restore 1085.6s total 1120.6s (total range 1015.0-1128.8) 0001 n=3 restore 93.6s total 115.7s (total range 99.7-116.9) David's patch n=3 restore 85.9s total 119.6s (total range 118.3-122.1) master / 0001: 11.6x the restore, 9.7x the whole pg_upgrade The same schema with a plain pg_restore --schema-only into a fresh cluster, instructions of the whole server: master rc=0 pg_constraint rows 957,311 server instructions 9.937T 0001 rc=0 pg_constraint rows 957,311 server instructions 0.667T 2. The three lookups, and the writes, by size of pg_constraint -------------------------------------------------------------- 0001 against master, 50 times each operation; nn = David's mix (90% NOT NULL, 3% FK), fk = almost only FKs. attach = CREATE + ATTACH PARTITION + DETACH + DROP of a partition of a referenced table; partof = CREATE ... PARTITION OF + DETACH + DROP; detachc = DETACH ... CONCURRENTLY; unlogged = SET UNLOGGED and LOGGED of a referenced table; fkadd = ADD + DROP of a FOREIGN KEY; dropfk = CREATE + DROP of a table with 10 FKs. mix rows attach partof detachc unlogged fkadd dropfk nn 205 -2.0% -2.2% -2.4% -1.6% +0.4% +0.4% fk 10,405 -50.1% -51.8% -49.9% -39.1% +0.4% +0.5% nn 10,211 -48.6% -50.1% -48.2% -37.4% +0.4% +0.4% fk 102,205 -90.5% -91.0% -90.4% -85.8% +0.4% +0.4% nn 100,271 -90.0% -90.6% -89.9% -85.1% +0.4% +0.1% nn 1,000,871 -99.0% -99.1% -99.0% -98.5% +0.5% +0.5% 100,000 NOT NULL (1,000 tables x 100 columns), 3 runs each, min-max; churn = 5 x (drop all 1,000 tables, create them again): instructions master 19.91G-19.91G 0001 20.80G-20.80G +4.5% WAL bytes master 121.68M-121.68M 0001 128.17M-128.17M +5.3% sizes pg_constraint 17.47M, confrelid index 0.66M churn heap 17.47M -> 104.62M, index 0.66M -> 3.88M On David's 957,311 rows (924,688 with confrelid = 0) a btree on confrelid is 6.65 MB, 21.55 MB without deduplication; pg_constraint itself is 184 MB. 3. heap_truncate_find_FKs() is not changed ------------------------------------------ TRUNCATE and ON COMMIT DELETE ROWS keep their seqscan; on a fresh database (194 rows) 0001 reads the same rows as master and the same instructions, within noise: master 0001 rows m/0001, idx ON COMMIT, N=1, per commit 0.14M 0.14M +0.1% 19400/19400, 0 ON COMMIT, N=100, per commit 3.48M 3.48M -0.0% 19400/19400, 0 ON COMMIT, N=1000, per commit 33.91M 33.91M +0.0% 19400/19400, 0 TRUNCATE N=1, per stmt 0.96M 0.96M +0.2% 19400/19400, 0 TRUNCATE N=10, per stmt 3.92M 3.92M +0.1% 19400/19400, 0 TRUNCATE N=100, per stmt 25.54M 25.51M -0.1% 19400/19400, 0 TRUNCATE N=1000, per stmt 262.16M 263.06M +0.3% 1940/1940, 0 CASCADE chain 10 20.53M 20.63M +0.5% 6690/6690, 0 CASCADE chain 100 83.91M 83.95M +0.0% 147900/147900, 0 CASCADE chain 1000 4.07G 4.07G +0.0% 9579000/9579000, 0 4. Correctness, in short ------------------------ - make check 239/239, cassert, 0 warnings; meson + clang with -Dwerror and gcc with -Werror clean; headerscheck and cpluspluscheck add no lines; pgindent clean. - The stats.sql check reads this backend's counter inside a transaction. On a build with CloneFkReferenced() back to a heap scan, and another session attaching and detaching an indexed partition in a loop, the old check (global idx_scan) said "index used" in 34 of 50 runs, the new one in 0 of 50. - Mutants: each lookup back to a heap scan, the key on the wrong column, the wrong index, the index on another column: all fail make check. - The whole regression suite with ignore_system_indexes = on, one test at a time: master and 0001 fail the same tests with the same diffs, except stats.sql (its index check) and toast OIDs in two warnings. - isolation: ATTACH, DETACH CONCURRENTLY and TRUNCATE CASCADE against a concurrent ADD FOREIGN KEY, 6 permutations: same output as master. - pg_upgrade from 14, 17, 18.6, 19beta3 and master (David's schema at 1/10, plus FKs to partitioned tables and a cycle): pg_dump --schema-only identical, amcheck clean on all 6 pg_constraint indexes; from 18 on, the user constraints are identical too (from 14 and 17 the not-null rows are new, and the clones of FKs onto referenced partitions get master's names).