Partial indexes on system catalogs

Hi,

On 2026-10-01 18:49:21 -0300, Manu wrote:

In the ATTACH PARTITION thread [1] Álvaro suggested exploring partial
indexes on system catalogs. The open question there was how to evaluate
the predicate during catalog maintenance without running the full
executor, while still representing the restriction in the catalogs.

Is the gain from that really substantial enough to warrant introducing this?

Catcache keeps negative lookup matches, so I suspect that in a bunch of the
cases we'd end up with similar number of cache entries (with a bit less memory
per entry, granted).

I'd guess that in most cases you'd be better off covering more columns to an
existing index. Most of the time there will not be a syscache for those,
because otherwise the key wouldn't have been unique... Or all that was used is
SearchSysCacheList etc.

Representation. The predicate is stored as the ordinary
pg_index.indpred, so the planner uses the partial index unchanged. The
restriction is not a new catalog field; it is an invariant checked in
two places that share the same whitelist: at CREATE/bootstrap time a
catalog index whose predicate is outside the whitelist is rejected, and
the maintenance evaluator re-checks the shape and errors out if it ever
sees anything else.

Why check any of this during normal index creation? If you create an index on
a catalog table (never a sane idea), somehow create a syscache for that (also
not a sane idea) and then do lookups that aren't supported, well, then you
deserve an error.

With that, initdb is clean, the index carries the expected predicate,
the planner uses it, and amcheck (bt_index_check with heapallindexed)
passes. As a concrete case I split ConstraintRelidTypidNameIndexId into
two partial indexes, "WHERE conrelid <> 0" and "WHERE contypid <> 0":
each row falls into exactly one of them, no InvalidOid is stored, each
is unique on its own, and both pass amcheck.

I think you'd really have to add an actual benefiting index + cache to make
this anywhere close to viable. Having syscaches that never get used doesn't
prove very much about any of this working.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:

On 2026-10-01 18:49:21 -0300, Manu wrote:

In the ATTACH PARTITION thread [1] Álvaro suggested exploring partial
indexes on system catalogs. The open question there was how to evaluate
the predicate during catalog maintenance without running the full
executor, while still representing the restriction in the catalogs.

Is the gain from that really substantial enough to warrant introducing this?

Yeah, I'm skeptical of that too, especially if the answer to "we can't
allow arbitrary code to execute during catalog updates" is to restrict
the set of allowed predicates to a tiny number. Then we don't have
partial indexes, just a hack with a small number of potential use-cases.

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 (for which we already accepted
that there'd be a bunch of useless zero entries at one end of the
index).

More generally, the real problem here is that pg_constraint is
misdesigned and in need of a refactoring. I could imagine doing
something like

(a) have a "core" catalog that stores the OID, name/namespace, and contype
of each constraint, and maybe a few other fields if we don't feel like
implementing this breakout idea fully. OID is constrained unique, the
name/namespace have a nonunique index.

(b) for each contype, have a breakout catalog that has the OID and the
columns needed for that contype. This would be pretty analogous
to the way that pg_aggregate extends pg_proc for aggregate functions.
We could put unique constraints on the breakout catalogs for each
uniqueness property we want, at the cost that we'd likely have to
duplicate conname into each such catalog (but I suspect we'd choose
to do that anyway). For example, "domain constraint names are
unique per-domain" could be enforced by a unique index on (contypid,
conname) in a breakout index for type-related constraints.

(c) for backwards compatibility, make a view pg_constraint on these
catalogs to avoid breaking what clients see.

This is pretty handwavy; in particular maybe the breakouts should
be designed along some other principle than "what's the contype".
But I would rather go in some such direction than implement
something as messy as partial indexes just to keep propping up a
poor catalog design.

regards, tom lane

Thanks, both, for the quick read -- and to Álvaro, whose suggestion in
the ATTACH-partition thread to look at these catalogs is what started
this.

Andres Freund <andres@anarazel.de> writes:

Is the gain from that really substantial enough to warrant introducing
this? Catcache keeps negative lookup matches [...]

Fair enough; I'll drop the partial-index approach. As you both say,
with pg_constraint as it stands the catcache already covers most of it,
and a whitelist of allowed predicates is a hack, not partial indexes.

Tom Lane <tgl@sss.pgh.pa.us> writes:

More generally, the real problem here is that pg_constraint is
misdesigned and in need of a refactoring. [...] in particular maybe
the breakouts should be designed along some other principle than
"what's the contype".

I went down that road to see where it leads, and a couple of things came
out that I'd value your read on.

On the split principle: contype isn't a clean key. conexclop is shared
by EXCLUSION and by PRIMARY KEY/UNIQUE with WITHOUT OVERLAPS; conindid
means different things for a foreign key (an index on the referenced
relation) versus a unique constraint (an index on conrelid); and CHECK
already spans relation and domain constraints. The cohesive groups look
like capabilities, not types.

More important, the compatibility view in your (c) runs into object
identity. A constraint's classid in pg_depend is ConstraintRelationId,
and the deletion path does table_open(classid), so classid has to stay a
real, scannable catalog -- it can't be a view; and information_schema
itself compares classid against 'pg_constraint'::regclass. The
pg_aggregate precedent you cited actually points the other way: pg_proc
keeps the identity and pg_aggregate only extends it, with no pg_proc
view.

So I prototyped that shape: pg_constraint stays the catalog, and a
pg_constraint_fkey breakout holds the foreign-key-only columns, keyed by
the constraint OID, the way pg_aggregate extends pg_proc. confrelid
lives in the breakout, where every row is a foreign key, so an ordinary
index on it is dense -- no partial predicate and none of the sparse zero
entries you were wary of.

On that prototype the scans you both flagged become index scans. In
wall time, ATTACH PARTITION on the referenced side -- the
CloneFkReferenced scan from that thread -- grows with the catalog, from
about 1 ms at ten thousand constraints to ~43 ms at a million; against
the breakout it stays flat near 2 ms, and DETACH goes from ~78 ms at a
million down to ~3 ms. The plan is the clearest evidence; at 200k
constraints the referencing-key lookup on master is

Seq Scan on pg_constraint (actual time=0.113..31.204 rows=3.00)
Rows Removed by Filter: 200203
Buffers: shared hit=12828 read=2562

and against the breakout

Seq Scan on pg_constraint_fkey (actual time=0.120..0.121 rows=3.00)
Rows Removed by Filter: 3
Buffers: shared read=1

which is O(catalog) versus O(1). The breakout holds only foreign keys,
so the lookup touches one page here; when the foreign keys themselves
are numerous the confrelid index keeps it flat as well. So the
direction does buy the thing the thread started from.

The open question I can't settle alone is the one your (c) was meant to
solve. The dense confrelid index needs confrelid to move into the
breakout, but confrelid on pg_constraint is exactly what clients select;
performance and "what clients see" pull against each other on that one
column, and a compat view to hide the move reintroduces the classid
problem above. Is a loud, pg_aggregate-style compatibility break
acceptable here, or does that tension argue for leaving confrelid on
pg_constraint and just adding the plain index?

Either way the immediate confrelid lookup is covered by the plain
non-unique index proposed separately, independent of this.

This is exploratory -- a prototype to test whether the refactoring
direction holds up, not a patch for commit. I'm happy to share the
branch if it helps. Does this match how you were thinking about it?

Thanks,
Manu

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

Tom Lane <tgl@sss.pgh.pa.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]/messages/by-id/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm; 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]: /messages/by-id/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm

Thanks,
Manu