Foreign keys to a partitioned table: constraint naming, measurements ===================================================================== Base: master 7fc2f0423320e02beb8c44b863f1827604c47c75, with and without v1-0001. Builds: -O2 -g, no assertions, same configure for both. Host: Ryzen AI 9 HX 370 (Zen 5 cores 0-3,12-15 pinned), Linux 7.3. Method: both builds run on byte-identical clones of the same cluster, alternating per round (m p, p m, ...). Instructions are instructions:u of the backend (perf stat -p) for the measured statements only; ms is the client's wall clock. "change" is the median of patched against master with a bootstrap 95% CI. Script: fk-names-measure.py.txt. p = table with N range partitions and a primary key, f = table with one int column. addfk ALTER TABLE f ADD FOREIGN KEY (a) REFERENCES p, N partitions. partof with that FK in place, CREATE TABLE ... PARTITION OF p for 50 more partitions, per statement (this path is not changed by the patch: it searches from suffix 1, linear per command). restore pg_dump -Fc of p (N partitions x ROWS rows) and f with the FK, restored into an empty cluster section by section: pre-data, data, then post-data (the PKs of every partition and the FK). postdata N = 10000: pre-data and data restored once into a template; each round clones it and runs only post-data. op size metric master patch change [95% CI] n addfk 100 instr (M) 152 89.8 -40.8% [-40.8..-40.8] 6/6 addfk 100 ms 93.8 95.1 +1.4% [-25.8..+22.9] 6/6 addfk 1000 instr (M) 7,382 917 -87.6% [-87.6..-87.6] 6/6 addfk 1000 ms 688 280 -59.3% [-82.5..-16.0] 6/6 addfk 5000 instr (M) 172,581 4,717 -97.3% [-97.3..-97.3] 6/6 addfk 5000 ms 11,184 1,097 -90.2% [-91.0..-85.9] 6/6 addfk 8192 instr (M) 463,267 7,886 -98.3% [-98.3..-98.3] 6/6 addfk 8192 ms 28,720 1,478 -94.9% [-94.9..-94.5] 6/6 addfk 10000 instr (M) 691,002 9,699 -98.6% [-98.6..-98.6] 6/6 addfk 10000 ms 42,800 2,053 -95.2% [-95.2..-95.1] 6/6 partof 1000 instr/op (M) 32.8 32.8 +0.0% [+0.0..+0.0] 6/6 partof 1000 ms/op 8.495 8.345 -1.8% [-11.9..+6.5] 6/6 partof 10000 instr/op (M) 303 303 +0.0% [+0.0..+0.0] 6/6 partof 10000 ms/op 26.7 26.1 -2.5% [-15.9..+2.3] 6/6 postdata 10000x10 post instr (M) 764,938 64,721 -91.5% [-91.6..-91.5] 5/5 postdata 10000x10 post ms 172,995 124,714 -27.9% [-74.5..+66.3] 5/5 restore 1000x10 data ms 1,555 1,449 -6.8% [-69.6..+20.5] 6/6 restore 1000x10 post instr (M) 9,439 2,846 -69.8% [-69.9..-69.8] 6/6 restore 1000x10 post ms 3,048 2,645 -13.2% [-70.8..+4.1] 6/6 restore 1000x10 pre ms 5,095 4,417 -13.3% [-66.6..+2.9] 6/6 restore 1000x10 total ms 10,606 8,588 -19.0% [-66.7..+1.2] 6/6 restore 1000x1000 data ms 8,396 8,156 -2.9% [-22.8..+4.0] 9/9 restore 1000x1000 post instr (M) 12,927 6,332 -51.0% [-51.0..-51.0] 9/9 restore 1000x1000 post ms 14,180 13,484 -4.9% [-18.0..+3.7] 9/9 restore 1000x1000 pre ms 20,182 20,382 +1.0% [-13.7..+18.0] 9/9 restore 1000x1000 total ms 43,236 41,750 -3.4% [-13.5..+7.2] 9/9 Reading: - addfk: the cost moves from quadratic to linear; N = 1000 is about the x8 the naming share predicted. At N = 100 the wall clock does not change within noise. - restore, N = 1000: the patch saves the same ~6,590M instructions in post-data with 10 and with 1000 rows per partition; that is the naming. What grows with the data is index builds and validation. The wall clock of pg_restore on this machine is dominated by the commit of every object (fsync), so the totals are within noise. - postdata, N = 10000: 765 billion instructions on master, 65 billion patched. The wall clock is bimodal on both builds (master 173, 173, 75, 174, 81 s; patched 134, 133, 44, 114, 125 s) because post-data also builds 10000 primary key indexes and the disk dominates; I do not draw a time from it. The instruction difference (~700 billion) is the same as in addfk N = 10000, which costs ~41 s here. - restore 1000x1000 has 9 rounds: a run cut by a time limit kept going and a second run repeated rounds 4-6; all 9 are kept. Single full restore, N = 10000, 10 rows per partition (1 round each, for scale only): master 604 s (pre-data 342, data 80, post-data 182); patched 513 s (296, 85, 132). Not measured cleanly: pg_upgrade with N = 10000 (one patched run took 268 s while the machine was loaded by other jobs; the noise sentinel was 27-29% over its baseline, so it is not a number). Other platforms.