Re: Partial indexes on system catalogs

Following up on my own question upthread, which I can now answer.

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> The fact of the matter is that if you just need to do pg_constraint
> lookups by confrelid, you could simply add a non-unique index on that
> column, paralleling the one on contypid

I measured that against the breakout prototype, both on current
master, and the plain index wins. With about a million constraints
(ms, median of 7):

ATTACH PARTITION: master 53, index 0.6, breakout 0.6
DETACH PARTITION: master 87, index 1.4, breakout 1.2

So the breakout buys nothing over the index, and it costs more where
it differs: every foreign key writes a second row (about 6% more WAL
per FK), readers need a second lookup to get conrelid, and in the
schemas I had at hand foreign keys are 7-16% of pg_constraint, which
makes the breakout larger than the index, not smaller. Moving
confrelid out instead would break most client tools: 15 of the 21 I
checked query the foreign-key columns of pg_constraint directly.

I also misread you earlier: the zero entries were not a concern, as
you had already accepted them for contypid.

Doing the plain index properly turned up a fourth lookup by confrelid
that the patch in CF 7365 had missed: heap_truncate_find_FKs(), whose
comment says it seqscans because no index exists. It runs on TRUNCATE
and, when an ON COMMIT DELETE ROWS table has a foreign key or trigger,
at every commit of a transaction that used temporary tables. With a
million constraints:

TRUNCATE of a table with a trigger: master 58 ms, index 0.9 ms
each such commit: master 34 ms, index 0.07 ms

That function takes a list of relations, so it reads the index with
one range scan from the smallest to the largest OID in the list; one
probe per relation lost to the old seqscan on a fresh catalog with a
thousand ON COMMIT DELETE ROWS tables, whereas the range scan costs at
most one visit per foreign key in each pass, however long the list.

The attached v4 covers all four lookups. It is the patch of CF 7365,
whose thread is [1]; I've added this thread to that entry. check-world
passes, pg_upgrade from master leaves the index consistent, and
differential fuzzing of TRUNCATE and SET UNLOGGED against master shows
no difference in behaviour.

[1] https://postgr.es/m/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm

Thanks,
Manu