Re: pg_stat_statements oddity with track = all

On 2020/12/02 15:32, Julien Rouhaud wrote:
> 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.

Just idea; instead of boolean is_toplevel flag, what about
counting the number of times when the statement is executed
in toplevel, and also in nested level?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION