stored procedures vs pg_stat_statements

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: stored procedures vs pg_stat_statements
Date: 2024-12-23 21:31:32
Message-ID: CAHyXU0xm8vnFFUP2kpwZXuONDuX7Q44Szswspo-Q9g8zTsoGEQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I've noticed that in pg_stat_statements, stored procedures are almost
always given a unique query id unless the query is textually identical.
pg_stat_statements.c makes this pretty clear as procedures are considered
utility statements and no normalization analysis is done against them.
Client side invoked statements appear to be able to be parameterized so
that they might be normalized correctly, but AFAICT there is no way to do
this from the server or any non-parameterizing client (say, dblink).

Suffice it to say, pg_stat_statements is an administrator's dream and
heavily procedure wrapped databases might struggle to generate useful
statistics leading to lack of insight.

From pg_stat_statements.c in ProcessUtility():

/*
* Force utility statements to get queryId zero. We do this even
in cases
* where the statement contains an optimizable statement for which a
* queryId could be derived (such as EXPLAIN or DECLARE CURSOR).
For such
* cases, runtime control will first go through ProcessUtility and
then
* the executor, and we don't want the executor hooks to do
anything,
* since we are already measuring the statement's costs at the
utility
* level.
*
* Note that this is only done if pg_stat_statements is enabled and
* configured to track utility statements, in the unlikely
possibility
* that user configured another extension to handle utility
statements
* only.
*/
Can this simply be disabled for stored procedures as a special case? I'm
hoping this might do something useful that is also safe. Curious if anyone
has any thoughts on this.

merlin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2024-12-23 21:58:13 Re: stored procedures vs pg_stat_statements
Previous Message David G. Johnston 2024-12-23 21:17:44 Re: Document NULL