Re: pg_stat_statements oddity with track = all

On Tue, Dec 01, 2020 at 10:08:06PM -0800, Nikolay Samokhvalov wrote:
> On Tue, Dec 1, 2020 at 8:05 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> > Someone raised an interested point recently on pg_stat_kcache extension for
> > handling nested statements, which also applies to pg_stat_statements.
> >
> ...
>
> > The only idea I have for that is to add a new field to entry key, for
> > instance
> > is_toplevel.
>
>
> This particular problem often bothered me when dealing with
> pg_stat_statements contents operating under "track = all" (especially when
> performing the aggregated analysis, like you showed).
>
> I think the idea of having a flag to distinguish the top-level entries is
> great.
>

Ok!

> > The immediate cons is obviously that it could amplify quite a lot
> > the number of entries tracked, so people may need to increase
> > pg_stat_statements.max to avoid slowdown if that makes them reach frequent
> > entry eviction.
> >
>
> If all top-level records in pg_stat_statements have "true" in the new
> column (is_toplevel), how would this lead to the need to increase
> pg_stat_statements.max? The number of records would remain the same, as
> before extending pg_stat_statements.

If the same query is getting executed both at top level and as a nested
statement, two entries will then be created. That's probably unlikely for
things like RI trigger queries, but I don't know what to expect for client
application queries.