Re: subtransaction performance regression [kind of] due to snapshot caching - Mailing list pgsql-hackers

Hi,

On 2021-04-06 00:47:13 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.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
LIMIT10;
 
┌─────────────────────────────────┬──────────────────┐
│               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
LIMIT10;
 
┌─────────────────────────────────┬──────────────────┐
│               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