Date: Fri, 3 Apr 2020 09:26:28 +0200
From: 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)
Message-ID: <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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1585857868967-0.post@n3.nabble.com>
Precedence: bulk

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."


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.
Similarly, if a statement is successfully planned but fails during
the execution phase, only its planning statistics will be displayed.
Please also note that the number of planning and number of execution aren't
expected to match, as the planification of a query won't always be followed by
its execution and reciprocally."