Re: subtransaction performance regression [kind of] due to snapshot caching

On 2021-04-06 01:34:02 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2021-04-06 00:47:13 -0400, Tom Lane wrote:
> >> Not wanting to distract from your point about xactCompletionCount,
> >> but ... I wonder if we could get away with defining "isObjectPinned"
> >> as "is the OID <= 9999" (and, in consequence, dropping explicit pin
> >> entries from pg_depend). I had not previously seen a case where the
> >> cost of looking into pg_depend for this info was this much of the
> >> total query runtime.
>
> > Couldn't we also treat FirstGenbkiObjectId to FirstBootstrapObjectId as
> > pinned? That'd be another 400kB of database size...
>
> Yeah, it'd require some close study of exactly what we want to pin
> or not pin.

One interesting bit is:

postgres[947554][1]=# SELECT classid::regclass, objid, refclassid::regclass, refobjid, deptype, refobjversion FROM pg_depend WHERE refobjid < 13000 AND deptype <> 'p';
┌───────────────┬───────┬──────────────┬──────────┬─────────┬───────────────┐
│ classid │ objid │ refclassid │ refobjid │ deptype │ refobjversion │
├───────────────┼───────┼──────────────┼──────────┼─────────┼───────────────┤
│ pg_constraint │ 15062 │ pg_collation │ 100 │ n │ 2.31 │
└───────────────┴───────┴──────────────┴──────────┴─────────┴───────────────┘
(1 row)

> Certainly everything with hand-assigned OIDs should
> be pinned, but I think there's a lot of critical stuff like index
> opclasses that don't get hand-assigned OIDs. On the other hand,
> it's intentional that nothing in information_schema is pinned.

Isn't that pretty much the difference between FirstGenbkiObjectId and
FirstBootstrapObjectId? Genbki will have assigned things like opclasses,
but not things like information_schema?

> We might have to rejigger initdb so that there's a clearer
> distinction between the OID ranges we want to pin or not.
> Maybe we'd even get initdb to record the cutoff OID in
> pg_control or someplace.

The only non-pinned pg_depend entry below FirstBootstrapObjectId is the
collation versioning one above. The only pinned entries above
FirstBootstrapObjectId are the ones created via
system_constraints.sql. So it seems we "just" would need to resolve the
constraint versioning stuff? And that could probably just be handled as
a hardcoded special case for now...

Greetings,

Andres Freund