[Imseih (AWS), Sami <simseih@amazon.com>]

Replacing constants in pg_stat_statements is on a best effort basis.
It is not unlikely that on a busy workload with heavy entry deallocation,
the user may observe the query with the constants in pg_stat_statements.

From what I can see, this is because the only time an entry is normalized is
during post_parse_analyze, and the entry may be deallocated by the time query
execution ends. At that point, the original form ( with constants ) of the query
is used.

It is not clear how prevalent this is in real-world workloads, but it's easily reproducible
on a workload with high entry deallocation. Attached are the repro steps on the latest
branch.

I think the only thing to do here is to call this out in docs with a suggestion to increase
pg_stat_statements.max to reduce the likelihood. I also attached the suggested
doc enhancement as well.

Any thoughts?

Regards,

--
Sami Imseih
Amazon Web Services




Attachments:

report.txt (text/plain, 1.9K) download
### pg_stat_statements.max is min allowed
postgres=# show pg_stat_statements.max ;
 pg_stat_statements.max
------------------------
 100
(1 row)

### create 200 tables
do $$
begin
    for i in 1 .. 200
    loop
        execute 'drop table if exists foo'||i;
        execute 'create table foo'||i||'(id int, c2 text)';
    end loop;
end; $$ ;

### create a pgbench script to insert into the tables.
for (( i=1; i<=200; i++ ))
do 
   echo "INSERT INTO foo"$i" (id, c2) values (1, 'somedata');" >> /tmp/pgbench.sql
done

### run pgbench
pgbench -c 50 -f /tmp/pgbench.sql -T 1200


### observe pg_stat_statements
postgres=# select query from pg_stat_statements where query like '%foo%' and query not like '%$1%';
                        query
-----------------------------------------------------
 INSERT INTO foo31 (id, c2) values (1, 'somedata')
 INSERT INTO foo20 (id, c2) values (1, 'somedata')
 INSERT INTO foo191 (id, c2) values (1, 'somedata')
 INSERT INTO foo170 (id, c2) values (1, 'somedata')
 INSERT INTO foo167 (id, c2) values (1, 'somedata')
 INSERT INTO foo32 (id, c2) values (1, 'somedata')
 INSERT INTO foo36 (id, c2) values (1, 'somedata')
 INSERT INTO foo43 (id, c2) values (1, 'somedata')
 INSERT INTO foo181 (id, c2) values (1, 'somedata')
 INSERT INTO foo88 (id, c2) values (1, 'somedata')
(10 rows)

postgres=# select query from pg_stat_statements where query like '%foo%' and query  like '%$1%' limit 5;
                   query
--------------------------------------------
 INSERT INTO foo33 (id, c2) values ($1, $2)
 INSERT INTO foo59 (id, c2) values ($1, $2)
 INSERT INTO foo50 (id, c2) values ($1, $2)
 INSERT INTO foo42 (id, c2) values ($1, $2)
 INSERT INTO foo91 (id, c2) values ($1, $2)

### observe the # of deallocations
postgres=# select * from pg_stat_statements_info ;
 dealloc |          stats_reset
---------+-------------------------------
  113371 | 2023-02-24 06:24:21.912275-06
(1 row)

0001-doc-update-regarding-pg_stat_statements-normalizatio.patch (application/octet-stream, 1.4K) download
From 1ecedf4e61171d2f02d513f7f83eed46ddbee5e3 Mon Sep 17 00:00:00 2001
From: "Imseih (AWS)" <simseih@88665a22795f.ant.amazon.com>
Date: Fri, 24 Feb 2023 14:22:39 -0600
Subject: [PATCH 1/1] doc update regarding pg_stat_statements normalization.

It is quite possible that possible willl not normalize
(replace constants with $1, $2..) when there is a high
rate of deallocation. This commit calls this out in
docs.
---
 doc/src/sgml/pgstatstatements.sgml | 9 +++++++++
 1 file changed, 9 insertions(+)

diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index efc36da602..4857f1b89e 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -516,6 +516,15 @@
    <structname>pg_stat_statements</structname> entry.
   </para>
 
+  <para>
+   A query text may be observed with constants in
+   <structname>pg_stat_statements</structname>, especially when there is a high
+   rate of entry deallocations. To reduce the likelihood of this occuring, consider
+   increasing <varname>pg_stat_statements.max</varname>.
+   The <structname>pg_stat_statements_info</structname> view provides entry
+   deallocation statistics.
+  </para>
+
   <para>
    In some cases, queries with visibly different texts might get merged into a
    single <structname>pg_stat_statements</structname> entry.  Normally this will happen
-- 
2.37.1 (Apple Git-137.1)