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
