heap_truncate_find_FKs() with confrelid = ANY: measurements =========================================================== CPU instructions in user space (perf stat) of a single-user backend, fsync off; 3 runs per build in rotated order; median, and the min-max of the runs (in M) at the right. Builds: gcc -O2 -g, no cassert. m: 77bba54 doc: Fix link to TCL project p: 871e054 Use the confrelid index in heap_truncate_find_FKs() a: cd80160 Avoid repeated foreign key lookups in TRUNCATE CASCADE n: 741ff54 MEASUREMENT ONLY: one confrelid index probe per relation in he m = master; p = m + v1-0001..0003 ("patched"); a = m + v1-0001 only ("0001 only", the CASCADE change); n = m + v1-0001 + one confrelid index probe per relation in heap_truncate_find_FKs() instead of 0002+0003 ("probes"; the alternative that needs no genam.c change, not proposed, measured for comparison). Every figure is net of a control: sections 1-2 subtract a run with fewer repetitions or with FK-less tables (below); sections 3-4 subtract a session that runs SELECT 1 the same number of times (backend start-up, stats flush and counter queries). Scripts: build.sh.txt, counts.sh.txt (driver), fk-lookup-counts.sh.txt (setups and measuring stages), mk_measurements.py.txt (this file); drop the .txt suffix to run them. 1. Commit with ON COMMIT DELETE ROWS tables ------------------------------------------- N temporary tables, each with a trigger (without one, the FK lookup does not run at all); every commit truncates them. Per commit: (100 commits - 10 commits) / 90. K = 0 is a fresh database (194 pg_constraint rows); with K, the catalog of section 2 (K + 2 FKs). cell master patched change runs: master / patched N=1 K=0 0.14M 0.10M -25.9% 0.13-0.14 / 0.10-0.10 N=100 K=0 3.48M 3.51M +1.0% 3.47-3.49 / 3.51-3.51 N=1000 K=0 33.90M 34.83M +2.7% 33.88-33.99 / 34.78-34.88 N=100 K=1000 4.21M 3.52M -16.5% 4.19-4.22 / 3.50-3.52 N=1000 K=1000 39.12M 34.83M -11.0% 39.02-39.23 / 34.73-34.85 N=100 K=10000 10.81M 3.53M -67.4% 10.81-10.82 / 3.52-3.54 N=1000 K=10000 86.25M 34.93M -59.5% 86.20-86.29 / 34.93-34.97 N=100 K=100000 76.69M 3.52M -95.4% 76.69-76.70 / 3.51-3.54 N=1000 K=100000 557.21M 34.91M -93.7% 557.20-557.22 / 34.91-34.97 cell master probes change runs: master / probes N=1 K=0 0.14M 0.10M -26.7% 0.13-0.14 / 0.10-0.10 N=100 K=0 3.48M 4.12M +18.5% 3.47-3.49 / 4.11-4.12 N=1000 K=0 33.90M 41.31M +21.8% 33.88-33.99 / 41.09-41.99 N=100 K=1000 4.21M 4.08M -3.2% 4.19-4.22 / 4.06-4.08 N=1000 K=1000 39.12M 38.97M -0.4% 39.02-39.23 / 38.72-41.16 N=100 K=10000 10.81M 4.26M -60.6% 10.81-10.82 / 4.26-4.32 N=1000 K=10000 86.25M 41.35M -52.1% 86.20-86.29 / 40.40-42.45 N=100 K=100000 76.69M 4.31M -94.4% 76.69-76.70 / 4.28-4.33 N=1000 K=100000 557.21M 41.93M -92.5% 557.20-557.22 / 40.73-41.99 2. TRUNCATE in a catalog with many other FKs -------------------------------------------- old_pk/old_fk, then K FKs between other tables, then new_pk/new_fk; 100 x TRUNCATE old_pk, old_fk, new_pk, new_fk, minus 100 x TRUNCATE of four plain tables (no index, no trigger), per TRUNCATE. So it is the FK lookup plus truncating two PK indexes; the lookup is what changes. cell master patched change runs: master / patched K=1000 1.22M 0.94M -23.2% 1.21-1.22 / 0.93-0.94 K=10000 3.56M 0.99M -72.2% 3.56-3.56 / 0.99-0.99 K=100000 26.45M 1.02M -96.1% 26.44-26.45 / 1.02-1.03 cell master probes change runs: master / probes K=1000 1.22M 0.95M -21.9% 1.21-1.22 / 0.95-0.96 K=10000 3.56M 1.00M -71.8% 3.56-3.56 / 1.00-1.01 K=100000 26.45M 1.04M -96.1% 26.44-26.45 / 1.04-1.04 3. TRUNCATE ... CASCADE ----------------------- Fresh database, per TRUNCATE. chain: c1 <- c2 <- ... <- cN, TRUNCATE c1 CASCADE. wide: N tables reference w0, TRUNCATE w0 CASCADE. pp: partitioned table with 100 partitions, referenced by 10 tables; TRUNCATE pp CASCADE, and TRUNCATE pp_1 CASCADE (one partition). chain master patched change runs: master / patched N=10 7.96M 7.66M -3.7% 7.96-7.99 / 7.62-7.78 N=100 71.06M 57.15M -19.6% 70.99-71.11 / 57.09-57.31 N=1000 3990.78M 637.02M -84.0% 3990.7-4056.0 / 599.58-663.40 chain master 0001 only change runs: master / 0001 only N=10 7.96M 7.88M -1.0% 7.96-7.99 / 7.84-7.96 N=100 71.06M 68.46M -3.7% 70.99-71.11 / 68.41-73.53 N=1000 3990.78M 1486.45M -62.8% 3990.7-4056.0 / 1486.2-1523.0 wide master patched change runs: master / patched N=1000 591.72M 612.02M +3.4% 583.58-632.56 / 585.36-634.61 wide master 0001 only change runs: master / 0001 only N=1000 591.72M 589.12M -0.4% 583.58-632.56 / 588.99-626.43 pp master patched change runs: master / patched P=100 Q=10 69.52M 69.51M -0.0% 69.31-69.64 / 69.19-69.53 pp master 0001 only change runs: master / 0001 only P=100 Q=10 69.52M 68.50M -1.5% 69.31-69.64 / 68.40-72.78 pp_1 master patched change runs: master / patched P=100 Q=10 6.15M 4.50M -26.8% 6.14-6.16 / 4.46-4.61 pp_1 master 0001 only change runs: master / 0001 only P=100 Q=10 6.15M 5.63M -8.5% 6.14-6.16 / 5.58-5.67 4. Other shapes, fresh database, per statement ---------------------------------------------- list: TRUNCATE of N tables with a trigger in one statement. err: TRUNCATE of 100 tables that fails on the last one (error path). list master patched change runs: master / patched N=1 0.56M 0.52M -6.4% 0.56-0.56 / 0.52-0.53 N=10 3.52M 3.49M -0.7% 3.51-3.52 / 3.49-3.51 N=100 25.20M 25.10M -0.4% 25.19-25.22 / 25.09-25.14 N=1000 251.23M 251.91M +0.3% 251.04-251.33 / 251.57-256.64 list master probes change runs: master / probes N=1 0.56M 0.53M -5.5% 0.56-0.56 / 0.51-0.53 N=10 3.52M 3.55M +0.9% 3.51-3.52 / 3.55-3.55 N=100 25.20M 25.73M +2.1% 25.19-25.22 / 25.72-26.10 N=1000 251.23M 258.13M +2.7% 251.04-251.33 / 258.04-282.24 err master patched change runs: master / patched N=100 13.54M 4.69M -65.4% 13.53-13.54 / 4.68-4.70 err master probes change runs: master / probes N=100 13.54M 5.16M -61.8% 13.53-13.54 / 5.16-5.17 5. Run-to-run variation ----------------------- Cells whose min-max ranges overlap with master, read as no change: cell N=1000 K=1000 (probes), chain N=100 (0001 only), wide N=1000, wide N=1000 (0001 only), pp P=100 Q=10, pp P=100 Q=10 (0001 only). In every other cell the ranges do not overlap.