From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: Julien Rouhaud <rjuju123@gmail.com>
To: legrand legrand <legrand_legrand@hotmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Planning counters in pg_stat_statements (using pgss_store)
Date: Wed, 8 Apr 2020 17:37:27 +0900
Message-ID: <5c9e0ca4-c378-57aa-1b08-49c83a41d5ec@oss.nttdata.com> (raw)
In-Reply-To: <20200403072628.GB95652@nol>
References: <CAOBaU_YMDofrfRrA4c_kNfFd4ZtwtoTy1XmnBom7rwyVGeFTDw@mail.gmail.com>
<8c6449d4-5fd7-f544-182b-a4bb7da1d18c@oss.nttdata.com>
<20200331060307.e7kypfynz7jous62@nol>
<ac44fd08-5923-c0f7-742d-952a6f5a14a3@oss.nttdata.com>
<20200331073321.bhnx2p63gp5px37f@nol>
<f2b4e098-1b96-e1a8-f601-a0908c9ff8c4@oss.nttdata.com>
<20200331184217.tf3x534uz4vusgmp@nol>
<bd27f039-5285-1fb7-5ed4-57f870c8a5a4@oss.nttdata.com>
<13923268-bad9-80f9-2542-5fd5e8b8c500@oss.nttdata.com>
<1585857868967-0.post@n3.nabble.com>
<20200403072628.GB95652@nol>
On 2020/04/03 16:26, Julien Rouhaud wrote:
> On Thu, Apr 02, 2020 at 01:04:28PM -0700, legrand legrand wrote:
>> Fujii Masao-4 wrote
>>> On 2020/04/01 18:19, Fujii Masao wrote:
>>>
>>> Finally I pushed the patch!
>>> Many thanks for all involved in this patch!
>>>
>>> As a remaining TODO item, I'm thinking that the document would need to
>>> be improved. For example, previously the query was not stored in pgss
>>> when it failed. But, in v13, if pgss_planning is enabled, such a query is
>>> stored because the planning succeeds. Without the explanation about
>>> that behavior in the document, I'm afraid that users will get confused.
>>> Thought?
>>
>> Thank you all for this work and especially to Julian for its major
>> contribution !
>
>
> Thanks a lot to everyone! This was quite a long journey.
>
>
>> Regarding the TODO point: Yes I agree that it can be improved.
>> My proposal:
>>
>> "Note that planning and execution statistics are updated only at their
>> respective end phase, and only for successfull operations.
>> For exemple executions counters of a long running SELECT query,
>> will be updated at the execution end, without showing any progress
>> report in the interval.
>> Other exemple, if the statement is successfully planned but fails in
>> the execution phase, only its planning statistics are stored.
>> This may give uncorrelated plans vs calls informations."
Thanks for the proposal!
> There are numerous reasons for lack of correlation between number of planning
> and number of execution, so I'm afraid that this will give users the false
> impression that only failed execution can lead to that.
>
> Here's some enhancement on your proposal:
>
> "Note that planning and execution statistics are updated only at their
> respective end phase, and only for successful operations.
> For example the execution counters of a long running query
> will only be updated at the execution end, without showing any progress
> report before that.
Probably since this is not the example for explaining the relationship of
planning and execution stats, it's better to explain this separately or just
drop it?
What about the attached patch based on your proposals?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:improve_pgss_docs_v1.patch (text/plain, 1.1K) downloaddiff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgmlindex 3d26108649..5a962feb39 100644--- a/doc/src/sgml/pgstatstatements.sgml+++ b/doc/src/sgml/pgstatstatements.sgml@@ -129,7 +129,7 @@
<entry><structfield>calls</structfield></entry>
<entry><type>bigint</type></entry>
<entry></entry>
- <entry>Number of times executed</entry>+ <entry>Number of times the statement was executed</entry>
</row>
<row>
@@ -398,6 +398,16 @@
reducing <varname>pg_stat_statements.max</varname> to prevent
recurrences.
</para>
++ <para>+ <structfield>plans</structfield> and <structfield>calls</structfield> aren't+ always expected to match because planning and execution statistics are+ updated at their respective end phase, and only for successful operations.+ For example, if a statement is successfully planned but fails during+ the execution phase, only its planning statistics will be updated.+ If planning is skipped because a cached plan is used, only its execution+ statistics will be updated.+ </para>
</sect2>
<sect2>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: masao.fujii@oss.nttdata.com, rjuju123@gmail.com, legrand_legrand@hotmail.com
Subject: Re: Planning counters in pg_stat_statements (using pgss_store)
In-Reply-To: <5c9e0ca4-c378-57aa-1b08-49c83a41d5ec@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox