Subject: Re: pg_stat_statements oddity with track = all
To: Julien Rouhaud <rjuju123@gmail.com>,
 Nikolay Samokhvalov <samokhvalov@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <20201202040516.GA43757@nol>
 <CANNMO+KrVxMTrzSJLkrkENQX7utVYo1SEyaW54N+Y-Zjew=wbQ@mail.gmail.com>
 <20201202063241.GA51769@nol>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <dc48d183-65a2-8f12-3581-ab8f3faeab8b@oss.nttdata.com>
Date: Wed, 2 Dec 2020 15:52:37 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:78.0)
 Gecko/20100101 Thunderbird/78.5.1
MIME-Version: 1.0
In-Reply-To: <20201202063241.GA51769@nol>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


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@gmail.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