| From: | Manu <manuelreyesbravo(at)gmail(dot)com> |
|---|---|
| To: | Álvaro Herrera <alvherre(at)kurilemu(dot)de> |
| Cc: | David Rowley <dgrowleyml(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Do we need an index on pg_constraint.confrelid? |
| Date: | 2026-11-16 18:47:09 |
| Message-ID: | 179485482980.2232586.7745827376472158691@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi Álvaro,
On Thu, Nov 12, 2026 at 3:29 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de>
wrote:
> I think the case in heap_truncate_check_FKs() could be made faster by
> using a ScalarArrayOpExpr (SK_SEARCHARRAY in nbtree), but I think we
> don't have any precedent where we manufacture that kind of thing for
> DDL use.
The attached patches do that, on top of master (77bba54, after your
b0496e30830). heap_truncate_check_FKs() does its lookups through
heap_truncate_find_FKs(), so that is the function they change.
0001 stands alone: TRUNCATE ... CASCADE passed heap_truncate_find_FKs()
the whole, growing list at every level, so a chain of N tables made N
lookups of up to N relations. It now passes only the relations added
at the previous level; each level adds the same relations in the same
order.
0002 lets systable_beginscan() take SK_SEARCHARRAY keys. nbtree
already handles them on the index path. On the heap path, used with
ignore_system_indexes or while reindexing, the heap scan would compare
the column with the array datum itself and return wrong rows, so 0002
keeps those keys out of it and checks them in systable_getnext(). An
index without amsearcharray raises an error. It is in genam.c rather
than a seqscan fallback in the caller because systable_beginscan()
makes that decision, and the caller would have to duplicate it.
0003 makes heap_truncate_find_FKs() do one confrelid = ANY scan instead
of the seqscan. Its test is in unsafe_tests, since it reconnects with
ignore_system_indexes; it fails if the genam.c part of 0002 is
reverted.
On precedent: no DDL code builds such keys today; outside nbtree only
nodeIndexscan.c does. The simpler alternative, one index probe per
relation, needs no genam.c change, so I measured it in the same cells
("probes" in measurements.txt). It gets the same gains in large
catalogs, but in a fresh database a commit with 1,000 ON COMMIT DELETE
ROWS tables that have triggers costs 21.8% more than master, against
2.7% with 0003: every probe sets up an index scan.
CPU instructions, master vs patched, median of 3 (measurements.txt
and the scripts that produced it are attached too):
- TRUNCATE of two PK/FK pairs in a catalog with 100,000 other FKs,
net of a TRUNCATE of four plain tables: 26.45M -> 1.02M
- whole commit with those 1,000 temp tables, in that catalog:
557.21M -> 34.91M
- TRUNCATE ... CASCADE down a chain of 1,000 tables: 3990.78M ->
1486.45M with 0001 alone, 637.02M with all three
The price, in a fresh database: that commit costs 2.7% more (33.90M ->
34.83M, under 1M instructions), 1.0% with 100 tables, and a TRUNCATE
of 1,000 tables in one statement 0.3% more. With 1,000 FKs in the
catalog the commit is already 11.0% cheaper. The other cells are
within run-to-run variation (measurements.txt, section 5).
The scripts count instructions, so they need builds without
assertions, as build.sh makes them.
Regards,
Manu
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Avoid-repeated-foreign-key-lookups-in-TRUNCATE-CA.patch | text/x-patch | 5.0 KB |
| v1-0002-Allow-SK_SEARCHARRAY-scan-keys-in-systable_begins.patch | text/x-patch | 10.5 KB |
| v1-0003-Use-the-confrelid-index-in-heap_truncate_find_FKs.patch | text/x-patch | 9.0 KB |
| measurements.txt | text/plain | 7.3 KB |
| build.sh.txt | text/plain | 934 bytes |
| counts.sh.txt | text/plain | 3.0 KB |
| fk-lookup-counts.sh.txt | text/plain | 13.5 KB |
| mk_measurements.py.txt | text/plain | 5.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tristan Partin | 2026-11-16 18:59:19 | Re: -Wformat-nonliteral |
| Previous Message | Tomas Vondra | 2026-11-16 18:01:26 | Re: contain_nonstrict_functions_walker() fails to consider JsonExpr and CoerceToDomain |