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
