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

Hi,

On 2021-04-06 00:47:13 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > The time in 14 is spent mostly below:
> > - 94.58% 0.01% postgres postgres [.] CreateFunction
> > - 94.57% CreateFunction
> > - 94.49% ProcedureCreate
> > - 90.95% record_object_address_dependencies
> > - 90.93% recordMultipleDependencies
> > - 89.65% isObjectPinned
> > - 89.12% systable_getnext
> > - 89.06% index_getnext_slot
> > - 56.13% index_fetch_heap
> > - 54.82% table_index_fetch_tuple
> > + 53.79% heapam_index_fetch_tuple
> > 0.07% heap_hot_search_buffer
> > 0.01% ReleaseAndReadBuffer
> > 0.01% LockBuffer
> > 0.08% heapam_index_fetch_tuple
>
> 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.

I had the same thought, and yes, I do think we should do that. I've seen
it show up in non-buggy workloads too (not to that degree though).

The <= 9999 pg_depend entries area also just a substantial proportion of
the size of an empty database, making it worth to remove <= 9999 entries:

freshly initdb'd empty cluster:

VACUUM FULL pg_depend;
dropme[926131][1]=# SELECT oid::regclass, pg_relation_size(oid) FROM pg_class WHERE relfilenode <> 0 ORDER BY 2 DESC LIMIT 10;
┌─────────────────────────────────┬──────────────────┐
│ oid │ pg_relation_size │
├─────────────────────────────────┼──────────────────┤
│ pg_depend │ 532480 │
│ pg_toast.pg_toast_2618 │ 516096 │
│ pg_collation │ 360448 │
│ pg_description │ 352256 │
│ pg_depend_depender_index │ 294912 │
│ pg_depend_reference_index │ 294912 │
│ pg_description_o_c_o_index │ 221184 │
│ pg_statistic │ 155648 │
│ pg_operator │ 114688 │
│ pg_collation_name_enc_nsp_index │ 106496 │
└─────────────────────────────────┴──────────────────┘
(10 rows)

DELETE FROM pg_depend WHERE deptype = 'p' AND refobjid <> 0 AND refobjid < 10000;
VACUUM FULL pg_depend;

dropme[926131][1]=# SELECT oid::regclass, pg_relation_size(oid) FROM pg_class WHERE relfilenode <> 0 ORDER BY 2 DESC LIMIT 10;
┌─────────────────────────────────┬──────────────────┐
│ oid │ pg_relation_size │
├─────────────────────────────────┼──────────────────┤
│ pg_toast.pg_toast_2618 │ 516096 │
│ pg_collation │ 360448 │
│ pg_description │ 352256 │
│ pg_depend │ 253952 │
│ pg_description_o_c_o_index │ 221184 │
│ pg_statistic │ 155648 │
│ pg_depend_depender_index │ 147456 │
│ pg_depend_reference_index │ 147456 │
│ pg_operator │ 114688 │
│ pg_collation_name_enc_nsp_index │ 106496 │
└─────────────────────────────────┴──────────────────┘
(10 rows)

A reduction from 8407kB to 7863kB of the size of the "dropme" database
just by deleting the "implicitly pinned" entries seems like a good deal.

To save people the time to look it up: pg_toast.pg_toast_2618 is
pg_description...

Couldn't we also treat FirstGenbkiObjectId to FirstBootstrapObjectId as
pinned? That'd be another 400kB of database size...

Greetings,

Andres Freund