Re: Partial indexes on system catalogs - Mailing list pgsql-hackers

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