Subject: Re: pg_stat_statements and "IN" conditions

From: Dmitry Dolgov <[email-masked]>
Date: Sat, 11 Feb 2023 13:08:20 +0100

> On Sat, Feb 11, 2023 at 11:47:07AM +0100, Dmitry Dolgov wrote:
>
> The original version of the patch was doing all of this, i.e. handling
> numerics, Param nodes, RTE_VALUES. The commentary about
> find_const_walker in tests is referring to a part of that, that was
> dealing with evaluation of expression to see if it could be reduced to a
> constant.
>
> Unfortunately there was a significant push back from reviewers because
> of those features. That's why I've reduced the patch to it's minimally
> useful version, having in mind re-implementing them as follow-up patches
> in the future. This is the reason as well why I left tests covering all
> this missing functionality -- as breadcrumbs to already discovered
> cases, important for the future extensions.

I'd like to elaborate on this a bit and remind about the origins of the
patch, as it's lost somewhere in the beginning of the thread. The idea
is not pulled out of thin air, everything is coming from our attempts to
improve one particular monitoring infrastructure in a real commercial
setting. Every covered use case and test in the original proposal was a
result of field trials, when some application-side library or ORM was
responsible for gigabytes of data in pgss, chocking the monitoring agent.

---

From: David Geier <[email-masked]>
Date: Wed, 15 Feb 2023 08:51:56 +0100

Hi,

> responsible for gigabytes of data in pgss, chocking the monitoring agent.

Thanks for the clarifications. I didn't mean to contend the usefulness 
of the patch and I wasn't aware that you already jumped through the 
loops of handling Param, etc. Seems like supporting only constants is a 
good starting point. The only thing that is likely confusing for users 
is that NUMERICs (and potentially constants of other types) are 
unsupported. Wouldn't it be fairly simple to support them via something 
like the following?

     is_const(element) || (is_coercion(element) && is_const(element->child))

-- 
David Geier
(ServiceNow)

---

From: Dmitry Dolgov <[email-masked]>
Date: Fri, 17 Feb 2023 16:46:02 +0100

> On Wed, Feb 15, 2023 at 08:51:56AM +0100, David Geier wrote:
> Hi,
>
> Thanks for the clarifications. I didn't mean to contend the usefulness of
> the patch and I wasn't aware that you already jumped through the loops of
> handling Param, etc.

No worries, I just wanted to emphasize that we've already collected
quite some number of use cases.

> Seems like supporting only constants is a good starting
> point. The only thing that is likely confusing for users is that NUMERICs
> (and potentially constants of other types) are unsupported. Wouldn't it be
> fairly simple to support them via something like the following?
>

It definitely makes sense to implement that, although I don't think it's
going to be acceptable to do that via directly listing conditions an
element has to satisfy. It probably has to be more flexible, sice we
would like to extend it in the future. My plan is to address this in a
follow-up patch, when the main mechanism is approved. Would you agree
with this approach?

---

From: David Geier <[email-masked]>
Date: Thu, 23 Feb 2023 09:48:35 +0100

Hi,

> follow-up patch, when the main mechanism is approved. Would you agree

I still think it's counterintuitive and I'm pretty sure people would 
even report this as a bug because not knowing about the difference in 
internal representation you would expect NUMERICs to work the same way 
other constants work. If anything we would have to document it.

Can't we do something pragmatic and have something like 
IsMergableInElement() which for now only supports constants and later 
can be extended? Or what exactly do you mean by "more flexible"?

-- 
David Geier
(ServiceNow)

---

From: Dmitry Dolgov <[email-masked]>
Date: Sun, 26 Feb 2023 11:46:19 +0100

> On Thu, Feb 23, 2023 at 09:48:35AM +0100, David Geier wrote:
> Hi,
>
> I still think it's counterintuitive and I'm pretty sure people would even
> report this as a bug because not knowing about the difference in internal
> representation you would expect NUMERICs to work the same way other
> constants work. If anything we would have to document it.
>
> IsMergableInElement() which for now only supports constants and later can be
> extended? Or what exactly do you mean by "more flexible"?

Here is how I see it (pls correct me if I'm wrong at any point). To
support numerics as presented in the tests from this patch, we have to
deal with FuncExpr (the function converting a value into a numeric).
Having in mind only numerics, we would need to filter out any other
FuncExpr (which already sounds dubious). Then we need to validate for
example that the function is immutable and have constant arguments,
which is already implemented in evaluate_function and is a part of
eval_const_expression. There is nothing special about numerics at this
point, and this approach leads us back to eval_const_expression to a
certain degree. Do you see any other way?

I'm thinking about Michael idea in this context, and want to see if it
would be possible to make the mechanism more flexible using some node
attributes. But I see it only as a follow-up step, not a prerequisite.

---

From: "Gregory Stark (as CFM)" <[email-masked]>
Date: Tue, 14 Mar 2023 14:14:17 -0400

So I was seeing that this patch needs a rebase according to cfbot.

However it looks like the review feedback you're looking for is more
of design questions. What jumbling is best to include in the feature
set and which is best to add in later patches. It sounds like you've
gotten conflicting feedback from initial reviews.

It does sound like the patch is pretty mature and you're actively
responding to feedback so if you got more authoritative feedback it
might even be committable now. It's already been two years of being
rolled forward so it would be a shame to keep rolling it forward.

Or is there some fatal problem that you're trying to work around and
still haven't found the magic combination that convinces any
committers this is something we want? In which case perhaps we set
this patch returned? I don't get that impression myself though.

---

From: Dmitry Dolgov <[email-masked]>
Date: Tue, 14 Mar 2023 20:04:32 +0100

> On Tue, Mar 14, 2023 at 02:14:17PM -0400, Gregory Stark (as CFM) wrote:

Yeah, folks are getting up to speed in with pgss improvements recently.
Thanks for letting me know.

> rolled forward so it would be a shame to keep rolling it forward.

You got it about right. There is a balance to strike between
implementation, that would cover more useful cases, but has more
dependencies (something like possibility of having multiple query id),
and more minimalistic implementation that would actually be acceptable
in the way it is now. But I haven't heard back from David about it, so I
assume everybody is fine with the minimalistic approach.

> this patch returned? I don't get that impression myself though.

Nothing like this on my side, although I'm not good at conjuring
committing powers of the nature.

---

From: Dmitry Dolgov <[email-masked]>
Date: Sun, 19 Mar 2023 13:27:34 +0100

> On Tue, Mar 14, 2023 at 08:04:32PM +0100, Dmitry Dolgov wrote:

Following recent refactoring of pg_stat_statements tests, I've created a
new one for merging functionality in the patch. This should solve any
conflicts.
>From 4f8ad43c1f0547913e5da8fa97fe08bccf06c91d Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Fri, 17 Feb 2023 10:17:55 +0100
Subject: [PATCH v14 1/2] Reusable decimalLength functions

Move out decimalLength functions to reuse in the following patch.
---
 src/backend/utils/adt/numutils.c | 50 +-----------------------
 src/include/utils/numutils.h     | 67 ++++++++++++++++++++++++++++++++
 2 files changed, 68 insertions(+), 49 deletions(-)
 create mode 100644 src/include/utils/numutils.h

diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index 471fbb7ee6..df7418cce7 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -18,9 +18,8 @@
 #include <limits.h>
 #include <ctype.h>
 
-#include "common/int.h"
 #include "utils/builtins.h"
-#include "port/pg_bitutils.h"
+#include "utils/numutils.h"
 
 /*
  * A table of all two-digit numbers. This is used to speed up decimal digit
@@ -38,53 +37,6 @@ static const char DIGIT_TABLE[200] =
 "80" "81" "82" "83" "84" "85" "86" "87" "88" "89"
 "90" "91" "92" "93" "94" "95" "96" "97" "98" "99";
 
-/*
- * Adapted from http://graphics.stanford.edu/~seander/bithacks.html#IntegerLog10
- */
-static inline int
-decimalLength32(const uint32 v)
-{
-	int			t;
-	static const uint32 PowersOfTen[] = {
-		1, 10, 100,
-		1000, 10000, 100000,
-		1000000, 10000000, 100000000,
-		1000000000
-	};
-
-	/*
-	 * Compute base-10 logarithm by dividing the base-2 logarithm by a
-	 * good-enough approximation of the base-2 logarithm of 10
-	 */
-	t = (pg_leftmost_one_pos32(v) + 1) * 1233 / 4096;
-	return t + (v >= PowersOfTen[t]);
-}
-
-static inline int
-decimalLength64(const uint64 v)
-{
-	int			t;
-	static const uint64 PowersOfTen[] = {
-		UINT64CONST(1), UINT64CONST(10),
-		UINT64CONST(100), UINT64CONST(1000),
-		UINT64CONST(10000), UINT64CONST(100000),
-		UINT64CONST(1000000), UINT64CONST(10000000),
-		UINT64CONST(100000000), UINT64CONST(1000000000),
-		UINT64CONST(10000000000), UINT64CONST(100000000000),
-		UINT64CONST(1000000000000), UINT64CONST(10000000000000),
-		UINT64CONST(100000000000000), UINT64CONST(1000000000000000),
-		UINT64CONST(10000000000000000), UINT64CONST(100000000000000000),
-		UINT64CONST(1000000000000000000), UINT64CONST(10000000000000000000)
-	};
-
-	/*
-	 * Compute base-10 logarithm by dividing the base-2 logarithm by a
-	 * good-enough approximation of the base-2 logarithm of 10
-	 */
-	t = (pg_leftmost_one_pos64(v) + 1) * 1233 / 4096;
-	return t + (v >= PowersOfTen[t]);
-}
-
 static const int8 hexlookup[128] = {
 	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
 	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
diff --git a/src/include/utils/numutils.h b/src/include/utils/numutils.h
new file mode 100644
index 0000000000..876e64f2df
--- /dev/null
+++ b/src/include/utils/numutils.h
@@ -0,0 +1,67 @@
+/*-------------------------------------------------------------------------
+ *
+ * numutils.h
+ *	  Decimal length functions for numutils.c
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/utils/numutils.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef NUMUTILS_H
+#define NUMUTILS_H
+
+#include "common/int.h"
+#include "port/pg_bitutils.h"
+
+/*
+ * Adapted from http://graphics.stanford.edu/~seander/bithacks.html#IntegerLog10
+ */
+static inline int
+decimalLength32(const uint32 v)
+{
+	int			t;
+	static const uint32 PowersOfTen[] = {
+		1, 10, 100,
+		1000, 10000, 100000,
+		1000000, 10000000, 100000000,
+		1000000000
+	};
+
+	/*
+	 * Compute base-10 logarithm by dividing the base-2 logarithm by a
+	 * good-enough approximation of the base-2 logarithm of 10
+	 */
+	t = (pg_leftmost_one_pos32(v) + 1) * 1233 / 4096;
+	return t + (v >= PowersOfTen[t]);
+}
+
+static inline int
+decimalLength64(const uint64 v)
+{
+	int			t;
+	static const uint64 PowersOfTen[] = {
+		UINT64CONST(1), UINT64CONST(10),
+		UINT64CONST(100), UINT64CONST(1000),
+		UINT64CONST(10000), UINT64CONST(100000),
+		UINT64CONST(1000000), UINT64CONST(10000000),
+		UINT64CONST(100000000), UINT64CONST(1000000000),
+		UINT64CONST(10000000000), UINT64CONST(100000000000),
+		UINT64CONST(1000000000000), UINT64CONST(10000000000000),
+		UINT64CONST(100000000000000), UINT64CONST(1000000000000000),
+		UINT64CONST(10000000000000000), UINT64CONST(100000000000000000),
+		UINT64CONST(1000000000000000000), UINT64CONST(10000000000000000000)
+	};
+
+	/*
+	 * Compute base-10 logarithm by dividing the base-2 logarithm by a
+	 * good-enough approximation of the base-2 logarithm of 10
+	 */
+	t = (pg_leftmost_one_pos64(v) + 1) * 1233 / 4096;
+	return t + (v >= PowersOfTen[t]);
+}
+
+#endif							/* NUMUTILS_H */
-- 
2.32.0

>From 27e4f29ac1052163cf9bbcf0a0c96a1b227d7d6c Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sun, 19 Mar 2023 13:01:07 +0100
Subject: [PATCH v14 2/2] Prevent jumbling of every element in ArrayExpr

pg_stat_statements produces multiple entries for queries like

    SELECT something FROM table WHERE col IN (1, 2, 3, ...)

depending on the number of parameters, because every element of
ArrayExpr is jumbled. In certain situations it's undesirable, especially
if the list becomes too large.

Make an array of Const expressions contribute only the number of
elements (up to power of 10) to the jumble hash. Allow to enable this
behavior via the new GUC query_id_const_merge with the default value off.

Reviewed-by: Zhihong Yu, Sergey Dudoladov, Robert Haas, Tom Lane,
Michael Paquier, Sergei Kornilov, Alvaro Herrera, David Geier
Tested-by: Chengxi Sun
---
 contrib/pg_stat_statements/Makefile           |   2 +-
 .../pg_stat_statements/expected/merging.out   | 221 ++++++++++++++++++
 contrib/pg_stat_statements/meson.build        |   1 +
 .../pg_stat_statements/pg_stat_statements.c   |  46 +++-
 contrib/pg_stat_statements/sql/merging.sql    |  69 ++++++
 doc/src/sgml/config.sgml                      |  29 +++
 doc/src/sgml/pgstatstatements.sgml            |  27 ++-
 src/backend/nodes/gen_node_support.pl         |   2 +-
 src/backend/nodes/queryjumblefuncs.c          | 111 ++++++++-
 src/backend/utils/misc/guc_tables.c           |  10 +
 src/backend/utils/misc/postgresql.conf.sample |   2 +-
 src/include/nodes/primnodes.h                 |   2 +
 src/include/nodes/queryjumble.h               |  12 +-
 13 files changed, 514 insertions(+), 20 deletions(-)
 create mode 100644 contrib/pg_stat_statements/expected/merging.out
 create mode 100644 contrib/pg_stat_statements/sql/merging.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 5578a9dd4e..2e30324334 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -18,7 +18,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
 
 REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
 REGRESS = select dml cursors utility level_tracking planning \
-	user_activity wal cleanup oldextversions
+	user_activity wal cleanup oldextversions merging
 # Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
 # which typical installcheck users do not have (e.g. buildfarm clients).
 NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out
new file mode 100644
index 0000000000..069cd4709d
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/merging.out
@@ -0,0 +1,221 @@
+--
+-- Const merging functionality
+--
+CREATE EXTENSION pg_stat_statements;
+CREATE TABLE test_merge (id int, data int);
+-- IN queries
+-- No merging is performed, as a baseline result
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                        query                                        | calls 
+-------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9)           |     1
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)      |     1
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) |     1
+ SELECT pg_stat_statements_reset()                                                   |     1
+(4 rows)
+
+-- Normal scenario, too many simple constants for an IN query
+SET query_id_const_merge = on;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                       query                       | calls 
+---------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1)         |     1
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3) |     1
+ SELECT pg_stat_statements_reset()                 |     1
+(3 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                   query                                   | calls 
+---------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1)                                 |     1
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3)                         |     1
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])                |     2
+ SELECT pg_stat_statements_reset()                                         |     1
+ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"    |     1
+(6 rows)
+
+-- Second order of magnitude, brace yourself
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                            query                             | calls 
+--------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) |     1
+ SELECT pg_stat_statements_reset()                            |     1
+(2 rows)
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                         query                         | calls 
+-------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) |     1
+ SELECT pg_stat_statements_reset()                     |     1
+(2 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                 query                                  | calls 
+------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4)                  |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])             |     1
+ SELECT pg_stat_statements_reset()                                      |     1
+ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" |     1
+(4 rows)
+
+-- No unmerged constants
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                           query                            | calls 
+------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) |     1
+ SELECT pg_stat_statements_reset()                          |     1
+(2 rows)
+
+-- More conditions in the query
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) and data = 2;
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and data = 2;
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and data = 2;
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                          query                                           | calls 
+------------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) and data = $10 |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3                 |     2
+ SELECT pg_stat_statements_reset()                                                        |     1
+(3 rows)
+
+-- On table, numeric type causes every constant being wrapped into functions.
+CREATE TABLE test_merge_numeric (id int, data numeric(5, 2));
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                               query                                | calls 
+--------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) |     1
+ SELECT pg_stat_statements_reset()                                  |     1
+(2 rows)
+
+-- Test constants evaluation
+WITH cte AS (
+    SELECT 'const' as const FROM test_merge
+)
+SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
+FROM cte;
+ result 
+--------
+(0 rows)
+
+RESET query_id_const_merge;
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 3e3062ada9..3c43c9f283 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -50,6 +50,7 @@ tests += {
       'wal',
       'cleanup',
       'oldextversions',
+      'merging',
     ],
     'regress_args': ['--temp-config', files('pg_stat_statements.conf')],
     # Disabled because these tests require
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 5285c3f7fa..70920a7853 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2668,6 +2668,10 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 				n_quer_loc = 0, /* Normalized query byte location */
 				last_off = 0,	/* Offset from start for previous tok */
 				last_tok_len = 0;	/* Length (in bytes) of that tok */
+	bool		skip = false; 	/* Signals that certain constants are
+								   merged together and have to be skipped */
+	int 		magnitude; 		/* Order of magnitute for number merged constants */
+
 
 	/*
 	 * Get constants' lengths (core system only gives us locations).  Note
@@ -2691,7 +2695,6 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 	{
 		int			off,		/* Offset from start for cur tok */
 					tok_len;	/* Length (in bytes) of that tok */
-
 		off = jstate->clocations[i].location;
 		/* Adjust recorded location if we're dealing with partial string */
 		off -= query_loc;
@@ -2706,12 +2709,43 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 		len_to_wrt -= last_tok_len;
 
 		Assert(len_to_wrt >= 0);
-		memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
-		n_quer_loc += len_to_wrt;
 
-		/* And insert a param symbol in place of the constant token */
-		n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d",
-							  i + 1 + jstate->highest_extern_param_id);
+		/* Normal path, non merged constant */
+		magnitude = jstate->clocations[i].magnitude;
+		if (magnitude == 0)
+		{
+			memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
+			n_quer_loc += len_to_wrt;
+
+			/* And insert a param symbol in place of the constant token */
+			n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d",
+								  i + 1 + jstate->highest_extern_param_id);
+
+			/* In case previous constants were merged away, stop doing that */
+			if (skip)
+				skip = false;
+		}
+		/* The firsts merged constant */
+		else if (!skip)
+		{
+			static const uint32 powers_of_ten[] = {
+				1, 10, 100,
+				1000, 10000, 100000,
+				1000000, 10000000, 100000000,
+				1000000000
+			};
+			int lower_merged = powers_of_ten[magnitude - 1];
+			int upper_merged = powers_of_ten[magnitude];
+
+			memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
+			n_quer_loc += len_to_wrt;
+
+			/* Skip the following until a non merged constant appear */
+			skip = true;
+			n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]",
+								  lower_merged, upper_merged - 1);
+		}
+		/* Otherwise the constant is merged away */
 
 		quer_loc = off + tok_len;
 		last_off = off;
diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql
new file mode 100644
index 0000000000..0b431bf9a2
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/merging.sql
@@ -0,0 +1,69 @@
+--
+-- Const merging functionality
+--
+CREATE EXTENSION pg_stat_statements;
+
+CREATE TABLE test_merge (id int, data int);
+
+-- IN queries
+
+-- No merging is performed, as a baseline result
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Normal scenario, too many simple constants for an IN query
+SET query_id_const_merge = on;
+
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Second order of magnitude, brace yourself
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- No unmerged constants
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- More conditions in the query
+SELECT pg_stat_statements_reset();
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) and data = 2;
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and data = 2;
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and data = 2;
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- On table, numeric type causes every constant being wrapped into functions.
+CREATE TABLE test_merge_numeric (id int, data numeric(5, 2));
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Test constants evaluation
+WITH cte AS (
+    SELECT 'const' as const FROM test_merge
+)
+SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
+FROM cte;
+
+RESET query_id_const_merge;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e5c41cc6c6..25e4897648 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8278,6 +8278,35 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-query-id-const-merge" xreflabel="query_id_const_merge">
+      <term><varname>query_id_const_merge</varname> (<type>bool</type>)
+      <indexterm>
+       <primary><varname>query_id_const_merge</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies how arrays of constants (e.g. for an "IN" clause) contribute
+        to the query identifier computation. Normally every element of an array
+        contributes to a query identifier, which means effectively the same
+        query could get multiple different identifiers, depending of size of the
+        array.
+
+        If this parameter is on, two queries with an array will get the same
+        query identifier if the only difference between them is the number of
+        constants, both numbers is of the same order of magnitude and greater or
+        equal 10 (so the order of magnitude is greather than 1, it is not worth
+        the efforts otherwise).
+
+        The parameter could be used to reduce amount of repeating data stored
+        via <xref linkend="pgstatstatements"/> extension.  The default value is off.
+
+        The <xref linkend="pgstatstatements"/> extension will represent such
+        queries in form <literal>'(... [10-99 entries])'</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
 
     </sect2>
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index b1214ee645..4ec8ba2c4f 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -529,10 +529,29 @@
   <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
-   only for semantically equivalent queries, but there is a small chance of
-   hash collisions causing unrelated queries to be merged into one entry.
-   (This cannot happen for queries belonging to different users or databases,
-   however.)
+   only for semantically equivalent queries, for example when queries are
+   different only in values of constants they use. Another valid possibility for
+   merging queries into a single <structname>pg_stat_statements</structname>
+   entry is when <xref linkend="guc-query-id-const-merge"/> is enabled and the
+   queries contain an array of constants of similar size:
+
+<screen>
+=# SET query_id_const_merge = on;
+=# SELECT pg_stat_statements_reset();
+=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+=# SELECT query, calls FROM pg_stat_statements;
+-[ RECORD 1 ]------------------------------
+query | SELECT * FROM test WHERE a IN (... [10-99 entries])
+calls | 2
+-[ RECORD 2 ]------------------------------
+query | SELECT pg_stat_statements_reset()
+calls | 1
+</screen>
+
+   But there is a small chance of hash collisions causing unrelated queries to
+   be merged into one entry. (This cannot happen for queries belonging to
+   different users or databases, however.)
   </para>
 
   <para>
diff --git a/src/backend/nodes/gen_node_support.pl b/src/backend/nodes/gen_node_support.pl
index ecbcadb8bf..93aed18c2e 100644
--- a/src/backend/nodes/gen_node_support.pl
+++ b/src/backend/nodes/gen_node_support.pl
@@ -1308,7 +1308,7 @@ _jumble${n}(JumbleState *jstate, Node *node)
 			# Track the node's location only if directly requested.
 			if ($query_jumble_location)
 			{
-				print $jff "\tJUMBLE_LOCATION($f);\n"
+				print $jff "\tJUMBLE_LOCATION($f, false);\n"
 				  unless $query_jumble_ignore;
 			}
 		}
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index d7fd72d70f..b2508cb0bd 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -37,17 +37,26 @@
 #include "nodes/queryjumble.h"
 #include "parser/scansup.h"
 
+#include "utils/numutils.h"
+
 #define JUMBLE_SIZE				1024	/* query serialization buffer size */
 
+#define QUERY_ID_CONST_MERGE_THRESHOLD 	10 	/* when to start merging constants,
+											 * purely to avoid unnecessary work */
+
 /* GUC parameters */
 int			compute_query_id = COMPUTE_QUERY_ID_AUTO;
 
+/* Whether to merge constants in a list when computing query_id */
+bool		query_id_const_merge = false;
+
 /* True when compute_query_id is ON, or AUTO and a module requests them */
 bool		query_id_enabled = false;
 
 static void AppendJumble(JumbleState *jstate,
 						 const unsigned char *item, Size size);
-static void RecordConstLocation(JumbleState *jstate, int location);
+static void RecordConstLocation(JumbleState *jstate,
+								int location, int magnitude);
 static void _jumbleNode(JumbleState *jstate, Node *node);
 static void _jumbleA_Const(JumbleState *jstate, Node *node);
 static void _jumbleList(JumbleState *jstate, Node *node);
@@ -186,11 +195,18 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size)
 }
 
 /*
- * Record location of constant within query string of query tree
- * that is currently being walked.
+ * Record location of constant within query string of query tree that is
+ * currently being walked.
+ *
+ * Magnitude argument larger than zero signals that the constant represents the
+ * first or the last element in a series of merged constants, and everything
+ * but such first/last element will contribute nothing to the jumble hash. In
+ * this case magnitute value describes order of magnitute for the number of
+ * elements in the series (i.e. how many digits it has), to represent the fact
+ * of merging later on.
  */
 static void
-RecordConstLocation(JumbleState *jstate, int location)
+RecordConstLocation(JumbleState *jstate, int location, int magnitude)
 {
 	/* -1 indicates unknown or undefined location */
 	if (location >= 0)
@@ -206,15 +222,73 @@ RecordConstLocation(JumbleState *jstate, int location)
 		}
 		jstate->clocations[jstate->clocations_count].location = location;
 		/* initialize lengths to -1 to simplify third-party module usage */
+		jstate->clocations[jstate->clocations_count].magnitude = magnitude;
 		jstate->clocations[jstate->clocations_count].length = -1;
 		jstate->clocations_count++;
 	}
 }
 
+/*
+ * Verify if the provided list contains could be merged down, which means it
+ * contains only constant expressions and its length is greater than or equal
+ * to CONST_MERGE_THRESHOLD.
+ *
+ * Return value is an order of magnitude for size of the list (to use for
+ * representation purposes later on) if merging is possible, otherwise zero.
+ *
+ * Note that this function searches only for Const directly and do not tries to
+ * simplify expressions.
+ */
+static int
+IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst)
+{
+	ListCell   *temp;
+	Node	   *firstExpr = NULL;
+
+	if (elements == NULL)
+		return 0;
+
+	if (!query_id_const_merge)
+	{
+		/* Merging is disabled, process everything one by one */
+		return 0;
+	}
+
+	if (elements->length < QUERY_ID_CONST_MERGE_THRESHOLD)
+	{
+		/* It is not worth it to consider small lists for merging */
+		return 0;
+	}
+
+	firstExpr = linitial(elements);
+
+	/*
+	 * If the first expression is a constant, verify if the following elements
+	 * are constants as well. If yes, the list is eligible for merging, and the
+	 * order of magnitude need to be calculated.
+	 */
+	if (IsA(firstExpr, Const))
+	{
+		foreach(temp, elements)
+			if (!IsA(lfirst(temp), Const))
+				return 0;
+
+		*firstConst = (Const *) firstExpr;
+		*lastConst = llast_node(Const, elements);
+		return decimalLength32(elements->length);
+	}
+
+	/*
+	 * If we end up here, it means no constants merging is possible, process
+	 * the list as usual.
+	 */
+	return 0;
+}
+
 #define JUMBLE_NODE(item) \
 	_jumbleNode(jstate, (Node *) expr->item)
-#define JUMBLE_LOCATION(location) \
-	RecordConstLocation(jstate, expr->location)
+#define JUMBLE_LOCATION(location, magnitude) \
+	RecordConstLocation(jstate, expr->location, magnitude)
 #define JUMBLE_FIELD(item) \
 	AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item))
 #define JUMBLE_FIELD_SINGLE(item) \
@@ -227,6 +301,31 @@ do { \
 
 #include "queryjumblefuncs.funcs.c"
 
+static void
+_jumbleArrayExpr(JumbleState *jstate, Node *node)
+{
+	ArrayExpr *expr = (ArrayExpr *) node;
+	Const *first, *last;
+	int magnitude = IsMergeableConstList(expr->elements, &first, &last);
+
+	if (magnitude)
+	{
+		RecordConstLocation(jstate, first->location, magnitude);
+		RecordConstLocation(jstate, last->location, magnitude);
+
+		/*
+		 * After merging constants down we end up with only two constants, the
+		 * first and the last one. To distinguish the order of magnitute behind
+		 * merged constants, add its value into the jumble.
+		 */
+		JUMBLE_FIELD_SINGLE(magnitude);
+	}
+	else
+	{
+		JUMBLE_NODE(elements);
+	}
+}
+
 static void
 _jumbleNode(JumbleState *jstate, Node *node)
 {
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 1c0583fe26..64bbd737e7 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1973,6 +1973,16 @@ struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"query_id_const_merge", PGC_SUSET, STATS_MONITORING,
+			gettext_noop("Sets whether an array of constants will contribute to"
+						 " query identified computation."),
+		},
+		&query_id_const_merge,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index d06074b86f..57b83b296d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -627,7 +627,7 @@
 #log_parser_stats = off
 #log_planner_stats = off
 #log_executor_stats = off
-
+#query_id_const_merge = off
 
 #------------------------------------------------------------------------------
 # AUTOVACUUM
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 8fb5b4b919..f5ef01ce4d 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1294,6 +1294,8 @@ typedef struct CaseTestExpr
  */
 typedef struct ArrayExpr
 {
+	pg_node_attr(custom_query_jumble)
+
 	Expr		xpr;
 	/* type of expression result */
 	Oid			array_typeid pg_node_attr(query_jumble_ignore);
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 204b8f74fd..001694aee9 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -15,6 +15,7 @@
 #define QUERYJUBLE_H
 
 #include "nodes/parsenodes.h"
+#include "nodes/nodeFuncs.h"
 
 /*
  * Struct for tracking locations/lengths of constants during normalization
@@ -23,6 +24,15 @@ typedef struct LocationLen
 {
 	int			location;		/* start offset in query text */
 	int			length;			/* length in bytes, or -1 to ignore */
+
+	/*
+	 * The constant may represent the beginning or the end of a merged
+	 * constants interval. In this case the magnitude value contains how many
+	 * constants were merged away (to a power of 10), in other words order of
+	 * manitude for number of merged constants. Otherwise the value is 0,
+	 * indicating that no merging is involved.
+	 */
+	int			magnitude;
 } LocationLen;
 
 /*
@@ -61,7 +71,7 @@ enum ComputeQueryIdType
 
 /* GUC parameters */
 extern PGDLLIMPORT int compute_query_id;
-
+extern PGDLLIMPORT bool query_id_const_merge;
 
 extern const char *CleanQuerytext(const char *query, int *location, int *len);
 extern JumbleState *JumbleQuery(Query *query, const char *querytext);
-- 
2.32.0

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 3 Jul 2023 21:46:11 -0700

> +        the efforts otherwise).

IMHO this adds way too much complexity to something that most users would
expect to be an on/off switch.  If I understand Álvaro's suggestion [0]
correctly, he's saying that in addition to allowing "on" and "off", it
might be worth allowing something like "powers" to yield roughly the
behavior described above.  I don't think he's suggesting that this "powers"
behavior should be the only available option.  Also, it seems
counterintuitive that queries with fewer than 10 constants are not merged.

In the interest of moving this patch forward, I would suggest making it a
simple on/off switch in 0002 and moving the "powers" functionality to a new
0003 patch.  I think separating out the core part of this feature might
help reviewers.  As you can see, I got distracted by the complicated
threshold logic and ended up focusing my first round of review there.

[0] https://postgr.es/m/20230209172651.cfgrebpyyr72h7fv%40alvherre.pgsql

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

---

From: Dmitry Dolgov <[email-masked]>
Date: Tue, 4 Jul 2023 21:02:56 +0200

> On Mon, Jul 03, 2023 at 09:46:11PM -0700, Nathan Bossart wrote:

Thanks for reviewing.

> expect to be an on/off switch.

This documentation is exclusively to be precise about how does it work.
Users don't have to worry about all this, and pretty much turn it
on/off, as you've described. I agree though, I could probably write this
text a bit differently.

> If I understand Álvaro's suggestion [0] correctly, he's saying that in
> addition to allowing "on" and "off", it might be worth allowing
> something like "powers" to yield roughly the behavior described above.
> I don't think he's suggesting that this "powers" behavior should be
> the only available option.

Independently of what Álvaro was suggesting, I find the "powers"
approach more suitable, because it answers my own concerns about the
previous implementation. Having "on"/"off" values means we would have to
scratch heads coming up with a one-size-fit-all default value, or to
introduce another option for the actual cut-off threshold. I would like
to avoid both of those options, that's why I went with "powers" only.

> Also, it seems counterintuitive that queries with fewer than 10
> constants are not merged.

Why? What would be your intuition using this feature?

> threshold logic and ended up focusing my first round of review there.

I would disagree. As I've described above, to me "powers" seems to be a
better fit, and the complicated logic is in fact reusing one already
existing function. Do those arguments sound convincing to you?

---

From: Michael Paquier <[email-masked]>
Date: Fri, 13 Oct 2023 17:07:00 +0900

> on/off, as you've described. I agree though, I could probably write this

FWIW, I am going to side with Nathan on this one, but not completely
either.  I was looking at the patch and it brings too much complexity
for a monitoring feature in this code path.  In my experience, I've
seen people complain about IN/ANY never strimmed down to a single
parameter in pg_stat_statements but I still have to hear from somebody
outside this thread that they'd like to reduce an IN clause depending
on the number of items, or something else.

> to avoid both of those options, that's why I went with "powers" only.

Now, it doesn't mean that this approach with the "powers" will never
happen, but based on the set of opinions I am gathering on this thread
I would suggest to rework the patch as follows:
- First implement an on/off switch that reduces the lists in IN and/or
ANY to one parameter.  Simply.
- Second, refactor the powers routine.
- Third, extend the on/off switch, or just implement a threshold with
a second switch.

When it comes to my opinion, I am not seeing any objections to the
feature as a whole, and I'm OK with the first step.  I'm also OK to
keep the door open for more improvements in controlling how these
IN/ANY lists show up, but there could be more than just the number of
items as parameter (say the query size, different behaviors depending
on the number of clauses in queries, subquery context or CTEs/WITH,
etc. just to name a few things coming in mind).
--
Michael

---

From: Dmitry Dolgov <[email-masked]>
Date: Fri, 13 Oct 2023 15:35:19 +0200

> On Fri, Oct 13, 2023 at 05:07:00PM +0900, Michael Paquier wrote:

Well, if it will help move this patch forward, why not. To clarify, I'm
going to split the current implementation into three patches, one for
each point you've mentioned.

> etc. just to name a few things coming in mind).

Interesting point, but now it's my turn to have troubles imagining the
case, where list representation could be controlled depending on
something else than the number of elements in it. Do you have any
specific example in mind?

---

From: Nathan Bossart <[email-masked]>
Date: Fri, 13 Oct 2023 11:37:30 -0500

> Why? What would be your intuition using this feature?

For the "powers" setting, I would've expected queries with 0-9 constants to
be merged.  Then 10-99, 100-999, 1000-9999, etc.  I suppose there might be
an argument for separating 0 from 1-9, too.

---

From: Dmitry Dolgov <[email-masked]>
Date: Tue, 17 Oct 2023 10:15:41 +0200

> On Fri, Oct 13, 2023 at 03:35:19PM +0200, Dmitry Dolgov wrote:

Here is what I had mind. The first patch implements the basic notion of
merging, and I guess everyone agrees on its usefulness. The second and
third implement merging into groups power of 10, which I still find
useful as well. The last one adds a lower threshold for merging on top
of that. My intentions are to get the first one in, ideally I would love
to see the second and third applied as well.

> specific example in mind?

In the current patch version I didn't add anything yet to address the
question of having more parameters to tune constants merging. The main
obstacle as I see it is that the information for that has to be
collected when jumbling various query nodes. Anything except information
about the ArrayExpr itself would have to be acquired when jumbling some
other parts of the query, not directly related to the ArrayExpr. It
seems to me this interdependency between otherwise unrelated nodes
outweigh the value it brings, and would require some more elaborate (and
more invasive for the purpose of this patch) mechanism to implement.
>From 4367571d3d886a14690ba31ad0163d0d309a52a3 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 14 Oct 2023 15:00:48 +0200
Subject: [PATCH v15 1/4] Prevent jumbling of every element in ArrayExpr

Make an array of Const expressions contribute only the first/last
elements to the jumble hash. Allow to enable this behavior via the new
GUC query_id_const_merge with the default value off.

Reviewed-by: Zhihong Yu, Sergey Dudoladov, Robert Haas, Tom Lane,
Michael Paquier, Sergei Kornilov, Alvaro Herrera, David Geier
Tested-by: Chengxi Sun
---
 contrib/pg_stat_statements/Makefile           |   2 +-
 .../pg_stat_statements/expected/merging.out   | 167 ++++++++++++++++++
 contrib/pg_stat_statements/meson.build        |   1 +
 .../pg_stat_statements/pg_stat_statements.c   |  34 +++-
 contrib/pg_stat_statements/sql/merging.sql    |  58 ++++++
 doc/src/sgml/config.sgml                      |  28 +++
 doc/src/sgml/pgstatstatements.sgml            |  25 ++-
 src/backend/nodes/gen_node_support.pl         |   2 +-
 src/backend/nodes/queryjumblefuncs.c          |  86 ++++++++-
 src/backend/utils/misc/guc_tables.c           |  10 ++
 src/backend/utils/misc/postgresql.conf.sample |   2 +-
 src/include/nodes/primnodes.h                 |   2 +
 src/include/nodes/queryjumble.h               |   9 +-
 13 files changed, 406 insertions(+), 20 deletions(-)
 create mode 100644 contrib/pg_stat_statements/expected/merging.out
 create mode 100644 contrib/pg_stat_statements/sql/merging.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index eba4a95d91a..af731fc9a58 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -19,7 +19,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
 
 REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
 REGRESS = select dml cursors utility level_tracking planning \
-	user_activity wal cleanup oldextversions
+	user_activity wal cleanup oldextversions merging
 # Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
 # which typical installcheck users do not have (e.g. buildfarm clients).
 NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out
new file mode 100644
index 00000000000..7711572e0b7
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/merging.out
@@ -0,0 +1,167 @@
+--
+-- Const merging functionality
+--
+CREATE EXTENSION pg_stat_statements;
+CREATE TABLE test_merge (id int, data int);
+-- IN queries
+-- No merging is performed, as a baseline result
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                        query                                        | calls 
+-------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9)           |     1
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)      |     1
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) |     1
+ SELECT pg_stat_statements_reset()                                                   |     1
+(4 rows)
+
+-- Normal scenario, too many simple constants for an IN query
+SET query_id_const_merge = on;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                   query                    | calls 
+--------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1)  |     1
+ SELECT * FROM test_merge WHERE id IN (...) |     1
+ SELECT pg_stat_statements_reset()          |     1
+(3 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                 query                                  | calls 
+------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1)                              |     1
+ SELECT * FROM test_merge WHERE id IN (...)                             |     4
+ SELECT pg_stat_statements_reset()                                      |     1
+ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" |     1
+(4 rows)
+
+-- More conditions in the query
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) and data = 2;
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and data = 2;
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and data = 2;
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                          query                           | calls 
+----------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN (...) and data = $3 |     3
+ SELECT pg_stat_statements_reset()                        |     1
+(2 rows)
+
+-- No constants simplification
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                                              query                                                              | calls 
+---------------------------------------------------------------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1 + $2, $3 + $4, $5 + $6, $7 + $8, $9 + $10, $11 + $12, $13 + $14, $15 + $16, $17 + $18) |     1
+ SELECT pg_stat_statements_reset()                                                                                               |     1
+(2 rows)
+
+-- Numeric type
+CREATE TABLE test_merge_numeric (id int, data numeric(5, 2));
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                       query                        | calls 
+----------------------------------------------------+-------
+ SELECT * FROM test_merge_numeric WHERE id IN (...) |     1
+ SELECT pg_stat_statements_reset()                  |     1
+(2 rows)
+
+-- Test constants evaluation, verifies a tricky part to make sure there are no
+-- issues in the merging implementation
+WITH cte AS (
+    SELECT 'const' as const FROM test_merge
+)
+SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
+FROM cte;
+ result 
+--------
+(0 rows)
+
+RESET query_id_const_merge;
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 15b7c7f2b02..6371c81e138 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -51,6 +51,7 @@ tests += {
       'wal',
       'cleanup',
       'oldextversions',
+      'merging',
     ],
     'regress_args': ['--temp-config', files('pg_stat_statements.conf')],
     # Disabled because these tests require
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index a46f2db352b..0b03009a053 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2695,6 +2695,9 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 				n_quer_loc = 0, /* Normalized query byte location */
 				last_off = 0,	/* Offset from start for previous tok */
 				last_tok_len = 0;	/* Length (in bytes) of that tok */
+	bool		skip = false; 	/* Signals that certain constants are
+								   merged together and have to be skipped */
+
 
 	/*
 	 * Get constants' lengths (core system only gives us locations).  Note
@@ -2718,7 +2721,6 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 	{
 		int			off,		/* Offset from start for cur tok */
 					tok_len;	/* Length (in bytes) of that tok */
-
 		off = jstate->clocations[i].location;
 		/* Adjust recorded location if we're dealing with partial string */
 		off -= query_loc;
@@ -2733,12 +2735,32 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 		len_to_wrt -= last_tok_len;
 
 		Assert(len_to_wrt >= 0);
 
+		if (!jstate->clocations[i].merged)
+			n_quer_loc += sprintf(norm_query + n_quer_loc, "...");
 
index 00000000000..02266a92ce8
@@ -0,0 +1,58 @@
+-- No constants simplification
+SELECT * FROM test_merge WHERE id IN (1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9);
+-- Numeric type
+-- Test constants evaluation, verifies a tricky part to make sure there are no
+-- issues in the merging implementation
index 3839c72c868..55b0795bce1 100644
@@ -8190,6 +8190,34 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-query-id-const-merge" xreflabel="query_id_const_merge">
+      <term><varname>query_id_const_merge</varname> (<type>bool</type>)
+      <indexterm>
+       <primary><varname>query_id_const_merge</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies how an array of constants (e.g. for an "IN" clause)
+        contributes to the query identifier computation. Normally every element
+        of an array contributes to the query identifier, which means the same
+        query will get multiple different identifiers, one for each occurrence
+        with an array of different lenght.
+
+        If this parameter is on, an array of constants will contribute only the
+        first and the last elements to the query identifier. It means two
+        occurences of the same query, where the only difference is number of
+        constants in the array, are going to get the same query identifier.
+
+        The parameter could be used to reduce amount of repeating data stored
+        via <xref linkend="pgstatstatements"/> extension.  The default value is off.
+
+        The <xref linkend="pgstatstatements"/> extension will represent such
+        queries in form <literal>'(...)'</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
 
     </sect2>
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 7e7c5c9ff82..8df4064cbf4 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -548,10 +548,27 @@
   <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
-   only for semantically equivalent queries, but there is a small chance of
-   hash collisions causing unrelated queries to be merged into one entry.
-   (This cannot happen for queries belonging to different users or databases,
-   however.)
+   only for semantically equivalent queries, or if
+   <xref linkend="guc-query-id-const-merge"/> is enabled and the only
+   difference between queries is the length of an array with constants they contain:
+
+<screen>
+=# SET query_id_const_merge = on;
+=# SELECT pg_stat_statements_reset();
+=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+=# SELECT query, calls FROM pg_stat_statements;
+-[ RECORD 1 ]------------------------------
+query | SELECT * FROM test WHERE a IN (...)
+calls | 2
+-[ RECORD 2 ]------------------------------
+query | SELECT pg_stat_statements_reset()
+calls | 1
+</screen>
+
+   But there is a small chance of hash collisions causing unrelated queries to
+   be merged into one entry. (This cannot happen for queries belonging to
+   different users or databases, however.)
   </para>
 
   <para>
index 72c79635781..5b4a3f37a2b 100644
index 281907a4d83..52d45c2b093 100644
@@ -42,12 +42,16 @@

 static void AppendJumble(JumbleState *jstate,
 						 const unsigned char *item, Size size);
-static void RecordConstLocation(JumbleState *jstate, int location);
+static void RecordConstLocation(JumbleState *jstate,
+								int location, bool merged);
 static void _jumbleNode(JumbleState *jstate, Node *node);
 static void _jumbleA_Const(JumbleState *jstate, Node *node);
 static void _jumbleList(JumbleState *jstate, Node *node);
@@ -186,11 +190,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size)
 }
 
 /*
- * Record location of constant within query string of query tree
- * that is currently being walked.
+ * Record location of constant within query string of query tree that is
+ * currently being walked.
+ *
+ * Merged argument signals that the constant represents the first or the last
+ * element in a series of merged constants, and everything but the first/last
+ * element contributes nothing to the jumble hash.
  */
 static void
-RecordConstLocation(JumbleState *jstate, int location)
+RecordConstLocation(JumbleState *jstate, int location, bool merged)
 {
 	/* -1 indicates unknown or undefined location */
 	if (location >= 0)
@@ -206,15 +214,65 @@ RecordConstLocation(JumbleState *jstate, int location)
 		}
 		jstate->clocations[jstate->clocations_count].location = location;
 		/* initialize lengths to -1 to simplify third-party module usage */
+		jstate->clocations[jstate->clocations_count].merged = merged;
 		jstate->clocations[jstate->clocations_count].length = -1;
 		jstate->clocations_count++;
 	}
 }
 
+/*
+ * Verify if the provided list contains could be merged down, which means it
+ * contains only constant expressions.
+ *
+ * Return value indicates if merging is possible.
+ *
+ * Note that this function searches only for explicit Const nodes and does not
+ * try to simplify expressions.
+ */
+static bool
+IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst)
+{
+	ListCell   *temp;
+	Node	   *firstExpr = NULL;
+
+	if (elements == NULL)
+		return false;
+
+	if (!query_id_const_merge)
+	{
+		/* Merging is disabled, process everything one by one */
+		return false;
+	}
+
+	firstExpr = linitial(elements);
+
+	/*
+	 * If the first expression is a constant, verify if the following elements
+	 * are constants as well. If yes, the list is eligible for merging, and the
+	 * order of magnitude need to be calculated.
+	 */
+	if (IsA(firstExpr, Const))
+	{
+		foreach(temp, elements)
+			if (!IsA(lfirst(temp), Const))
+				return false;
+
+		*firstConst = (Const *) firstExpr;
+		*lastConst = llast_node(Const, elements);
+		return true;
+	}
+
+	/*
+	 * If we end up here, it means no constants merging is possible, process
+	 * the list as usual.
+	 */
+	return false;
+}
+
 #define JUMBLE_NODE(item) \
 	_jumbleNode(jstate, (Node *) expr->item)
-#define JUMBLE_LOCATION(location) \
-	RecordConstLocation(jstate, expr->location)
+#define JUMBLE_LOCATION(location, merged) \
+	RecordConstLocation(jstate, expr->location, merged)
 #define JUMBLE_FIELD(item) \
 	AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item))
 #define JUMBLE_FIELD_SINGLE(item) \
@@ -227,6 +285,22 @@ do { \

+static void
+_jumbleArrayExpr(JumbleState *jstate, Node *node)
+{
+	ArrayExpr *expr = (ArrayExpr *) node;
+	Const *first, *last;
+	if(IsMergeableConstList(expr->elements, &first, &last))
+	{
+		RecordConstLocation(jstate, first->location, true);
+		RecordConstLocation(jstate, last->location, true);
+	}
+	else
+	{
+		JUMBLE_NODE(elements);
+	}
+}
+
 static void
 _jumbleNode(JumbleState *jstate, Node *node)
 {
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 4c585741661..fbb50adb9f9 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2011,6 +2011,16 @@ struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
index d08d55c3fe4..3397780dc72 100644
@@ -628,7 +628,7 @@
 
 #------------------------------------------------------------------------------
 # AUTOVACUUM
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 60d72a876b4..fde0320b263 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1296,6 +1296,8 @@ typedef struct CaseTestExpr
  */
 typedef struct ArrayExpr
 {
+	pg_node_attr(custom_query_jumble)
+
 	Expr		xpr;
 	/* type of expression result */
 	Oid			array_typeid pg_node_attr(query_jumble_ignore);
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 7649e095aa5..225957d7b3c 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -15,6 +15,7 @@
 #define QUERYJUMBLE_H

 /*
  * Struct for tracking locations/lengths of constants during normalization
@@ -23,6 +24,12 @@ typedef struct LocationLen
 {
 	int			location;		/* start offset in query text */
 	int			length;			/* length in bytes, or -1 to ignore */
+
+	/*
+	 * Indicates the constant represents the beginning or the end of a merged
+	 * constants interval.
+	 */
+	bool		merged;
 } LocationLen;
 
 /*
@@ -61,7 +68,7 @@ enum ComputeQueryIdType
 
 /* GUC parameters */
 
 extern const char *CleanQuerytext(const char *query, int *location, int *len);
 extern JumbleState *JumbleQuery(Query *query);

base-commit: 22655aa23132a0645fdcdce4b233a1fff0c0cf8f
-- 
2.41.0

>From 0eadf14630fa0e888cdd27e41a091c6564a4637c Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Fri, 17 Feb 2023 10:17:55 +0100
Subject: [PATCH v15 2/4] Reusable decimalLength functions

diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index a597e5ed796..02fe132f285 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -18,9 +18,8 @@
 #include <limits.h>
 #include <ctype.h>
 
-#include "common/int.h"
 
 /*
 
-/*
index 00000000000..876e64f2df9
2.41.0

>From 4ad001ab9866467c90d5b1ddc664bfe1b6b87ee4 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sun, 15 Oct 2023 10:06:09 +0200
Subject: [PATCH v15 3/4] Merge constants in ArrayExpr into groups

Using query_id_const_merge only first/last element in an ArrayExpr will
be used to compute query id. Extend this to take into account number of
elements, and merge constants into groups based on it. Resulting groups
are powers of 10, i.e. 1 to 9, 10 to 99, etc.
---
 .../pg_stat_statements/expected/merging.out   | 84 +++++++++++++++----
 .../pg_stat_statements/pg_stat_statements.c   | 17 +++-
 contrib/pg_stat_statements/sql/merging.sql    | 13 +++
 doc/src/sgml/config.sgml                      |  9 +-
 doc/src/sgml/pgstatstatements.sgml            |  2 +-
 src/backend/nodes/queryjumblefuncs.c          | 52 ++++++++----
 src/include/nodes/queryjumble.h               |  7 +-
 7 files changed, 142 insertions(+), 42 deletions(-)

diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out
index 7711572e0b7..1bb75a93045 100644
--- a/contrib/pg_stat_statements/expected/merging.out
+++ b/contrib/pg_stat_statements/expected/merging.out
@@ -54,11 +54,11 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3);
 (0 rows)
 
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-                   query                    | calls 
---------------------------------------------+-------
- SELECT * FROM test_merge WHERE id IN ($1)  |     1
- SELECT * FROM test_merge WHERE id IN (...) |     1
- SELECT pg_stat_statements_reset()          |     1
+                          query                           | calls 
+----------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1)                |     1
+ SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) |     1
+ SELECT pg_stat_statements_reset()                        |     1
 (3 rows)
 
 SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
@@ -80,7 +80,60 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
                                  query                                  | calls 
 ------------------------------------------------------------------------+-------
  SELECT * FROM test_merge WHERE id IN ($1)                              |     1
- SELECT * FROM test_merge WHERE id IN (...)                             |     4
+ SELECT * FROM test_merge WHERE id IN (... [1-9 entries])               |     2
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])             |     2
+ SELECT pg_stat_statements_reset()                                      |     1
+ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" |     1
+(5 rows)
+
+-- Second order of magnitude, brace yourself
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                            query                             | calls 
+--------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN (... [100-999 entries]) |     1
+ SELECT pg_stat_statements_reset()                            |     1
+(2 rows)
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                          query                           | calls 
+----------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) |     1
+ SELECT pg_stat_statements_reset()                        |     1
+(2 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                 query                                  | calls 
+------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN (... [1-9 entries])               |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])             |     1
  SELECT pg_stat_statements_reset()                                      |     1
  SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" |     1
 (4 rows)
@@ -108,11 +161,12 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) and dat
 (0 rows)
 
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-                          query                           | calls 
-----------------------------------------------------------+-------
- SELECT * FROM test_merge WHERE id IN (...) and data = $3 |     3
- SELECT pg_stat_statements_reset()                        |     1
-(2 rows)
+                                  query                                   | calls 
+--------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN (... [1-9 entries]) and data = $3   |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries]) and data = $3 |     2
+ SELECT pg_stat_statements_reset()                                        |     1
+(3 rows)
 
 -- No constants simplification
 SELECT pg_stat_statements_reset();
@@ -147,10 +201,10 @@ SELECT * FROM test_merge_numeric WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
 (0 rows)
 
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-                       query                        | calls 
-----------------------------------------------------+-------
- SELECT * FROM test_merge_numeric WHERE id IN (...) |     1
- SELECT pg_stat_statements_reset()                  |     1
+                               query                                | calls 
+--------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_numeric WHERE id IN (... [10-99 entries]) |     1
+ SELECT pg_stat_statements_reset()                                  |     1
 (2 rows)
 
 -- Test constants evaluation, verifies a tricky part to make sure there are no
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 0b03009a053..24d7401257d 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2697,6 +2697,8 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 				last_tok_len = 0;	/* Length (in bytes) of that tok */
 	bool		skip = false; 	/* Signals that certain constants are
 								   merged together and have to be skipped */
+	int 		magnitude; 		/* Order of magnitute for number of merged
+								   constants */

 	/*
@@ -2737,7 +2739,8 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 		Assert(len_to_wrt >= 0);
 
 		/* Normal path, non merged constant */
-		if (!jstate->clocations[i].merged)
+		magnitude = jstate->clocations[i].magnitude;
+		if (magnitude == 0)
 		{
 			memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
 			n_quer_loc += len_to_wrt;
@@ -2753,12 +2756,22 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 		/* The firsts merged constant */
 		else if (!skip)
 		{
+			static const uint32 powers_of_ten[] = {
+				1, 10, 100,
+				1000, 10000, 100000,
+				1000000, 10000000, 100000000,
+				1000000000
+			};
+			int lower_merged = powers_of_ten[magnitude - 1];
+			int upper_merged = powers_of_ten[magnitude];
+
 			memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
 			n_quer_loc += len_to_wrt;
 
 			/* Skip the following until a non merged constant appear */
 			skip = true;
-			n_quer_loc += sprintf(norm_query + n_quer_loc, "...");
+			n_quer_loc += sprintf(norm_query + n_quer_loc, "... [%d-%d entries]",
+								  lower_merged, upper_merged - 1);
 		}
 		/* Otherwise the constant is merged away */
 
diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql
index 02266a92ce8..75960159a1d 100644
--- a/contrib/pg_stat_statements/sql/merging.sql
+++ b/contrib/pg_stat_statements/sql/merging.sql
@@ -27,6 +27,19 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
 SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
 
+-- Second order of magnitude, brace yourself
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
 -- More conditions in the query
 SELECT pg_stat_statements_reset();
 
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 55b0795bce1..4fd997284c9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8205,15 +8205,16 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
         with an array of different lenght.
 
         If this parameter is on, an array of constants will contribute only the
-        first and the last elements to the query identifier. It means two
-        occurences of the same query, where the only difference is number of
-        constants in the array, are going to get the same query identifier.
+        first element, the last element and the number of elements to the query
+        identifier. It means two occurences of the same query, where the only
+        difference is number of constants in the array, are going to get the
+        same query identifier if the arrays are of similar length.
 
         The parameter could be used to reduce amount of repeating data stored
         via <xref linkend="pgstatstatements"/> extension.  The default value is off.
 
         The <xref linkend="pgstatstatements"/> extension will represent such
-        queries in form <literal>'(...)'</literal>.
+        queries in form <literal>'(... [10-99 entries])'</literal>.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 8df4064cbf4..1f6e6a0e76b 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -559,7 +559,7 @@
 =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
 =# SELECT query, calls FROM pg_stat_statements;
 -[ RECORD 1 ]------------------------------
-query | SELECT * FROM test WHERE a IN (...)
+query | SELECT * FROM test WHERE a IN (... [10-99 entries])
 calls | 2
 -[ RECORD 2 ]------------------------------
 query | SELECT pg_stat_statements_reset()
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 52d45c2b093..b198fe2579e 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -37,6 +37,8 @@
 #include "nodes/queryjumble.h"
 #include "parser/scansup.h"

 /* GUC parameters */
@@ -51,7 +53,7 @@ bool		query_id_enabled = false;
 static void AppendJumble(JumbleState *jstate,
 						 const unsigned char *item, Size size);
 static void RecordConstLocation(JumbleState *jstate,
-								int location, bool merged);
+								int location, int magnitude);
 static void _jumbleNode(JumbleState *jstate, Node *node);
 static void _jumbleA_Const(JumbleState *jstate, Node *node);
 static void _jumbleList(JumbleState *jstate, Node *node);
@@ -193,12 +195,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size)
  * Record location of constant within query string of query tree that is
  * currently being walked.
  *
- * Merged argument signals that the constant represents the first or the last
- * element in a series of merged constants, and everything but the first/last
- * element contributes nothing to the jumble hash.
+ * Magnitude argument larger than zero signals that the constant represents the
+ * first or the last element in a series of merged constants, and everything
+ * but such first/last element will contribute nothing to the jumble hash. The
+ * magnitute value specifies order of magnitute (i.e. how many digits it has)
+ * for the number of elements in the series, to represent the fact of merging
+ * later on.
  */
 static void
-RecordConstLocation(JumbleState *jstate, int location, bool merged)
+RecordConstLocation(JumbleState *jstate, int location, int magnitude)
 {
 	/* -1 indicates unknown or undefined location */
 	if (location >= 0)
@@ -214,7 +219,7 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged)
 		}
 		jstate->clocations[jstate->clocations_count].location = location;
 		/* initialize lengths to -1 to simplify third-party module usage */
-		jstate->clocations[jstate->clocations_count].merged = merged;
+		jstate->clocations[jstate->clocations_count].magnitude = magnitude;
 		jstate->clocations[jstate->clocations_count].length = -1;
 		jstate->clocations_count++;
 	}
@@ -224,24 +229,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged)
  * Verify if the provided list contains could be merged down, which means it
  * contains only constant expressions.
  *
- * Return value indicates if merging is possible.
+ * Return value is the order of magnitude (i.e. how many digits it has) for
+ * length of the list (to use for representation purposes later on) if merging
+ * is possible, otherwise zero.
  *
  * Note that this function searches only for explicit Const nodes and does not
  * try to simplify expressions.
  */
-static bool
+static int
 IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst)
 {
 	ListCell   *temp;
 	Node	   *firstExpr = NULL;
 
 	if (elements == NULL)
-		return false;
+		return 0;
 
 	if (!query_id_const_merge)
 	{
 		/* Merging is disabled, process everything one by one */
-		return false;
+		return 0;
 	}
 
 	firstExpr = linitial(elements);
@@ -255,24 +262,24 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst)
 	{
 		foreach(temp, elements)
 			if (!IsA(lfirst(temp), Const))
-				return false;
+				return 0;
 
 		*firstConst = (Const *) firstExpr;
 		*lastConst = llast_node(Const, elements);
-		return true;
+		return decimalLength32(elements->length);
 	}
 
 	/*
 	 * If we end up here, it means no constants merging is possible, process
 	 * the list as usual.
 	 */
-	return false;
+	return 0;
 }
 
 #define JUMBLE_NODE(item) \
 	_jumbleNode(jstate, (Node *) expr->item)
-#define JUMBLE_LOCATION(location, merged) \
-	RecordConstLocation(jstate, expr->location, merged)
+#define JUMBLE_LOCATION(location, magnitude) \
+	RecordConstLocation(jstate, expr->location, magnitude)
 #define JUMBLE_FIELD(item) \
 	AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item))
 #define JUMBLE_FIELD_SINGLE(item) \
@@ -290,10 +297,19 @@ _jumbleArrayExpr(JumbleState *jstate, Node *node)
 {
 	ArrayExpr *expr = (ArrayExpr *) node;
 	Const *first, *last;
-	if(IsMergeableConstList(expr->elements, &first, &last))
+	int magnitude = IsMergeableConstList(expr->elements, &first, &last);
+
+	if (magnitude)
 	{
-		RecordConstLocation(jstate, first->location, true);
-		RecordConstLocation(jstate, last->location, true);
+		RecordConstLocation(jstate, first->location, magnitude);
+		RecordConstLocation(jstate, last->location, magnitude);
+
+		/*
+		 * After merging constants down we end up with only two constants, the
+		 * first and the last one. To distinguish the order of magnitute behind
+		 * merged constants, add its value into the jumble.
+		 */
+		JUMBLE_FIELD_SINGLE(magnitude);
 	}
 	else
 	{
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 225957d7b3c..cfa99efc14e 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -27,9 +27,12 @@ typedef struct LocationLen
 
 	/*
 	 * Indicates the constant represents the beginning or the end of a merged
-	 * constants interval.
+	 * constants interval. The value shows how many constants were merged away
+	 * (up to a power of 10), or in other words the order of manitude for
+	 * number of merged constants (i.e. how many digits it has). Otherwise the
+	 * value is 0, indicating that no merging was performed.
 	 */
-	bool		merged;
+	int			magnitude;
 } LocationLen;
 
 /*
-- 
2.41.0

>From e119d74ddd5cd5550246213fc66cd75cae606571 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Mon, 16 Oct 2023 16:52:27 +0200
Subject: [PATCH v15 4/4] Add query_id_const_merge_threshold

Extend query_id_const_merge to allow merging only if the number of
elements is larger than specified value, which could be configured using
new GUC query_id_const_merge_threshold.
---
 .../pg_stat_statements/expected/merging.out   | 64 +++++++++++++++++++
 contrib/pg_stat_statements/sql/merging.sql    | 17 +++++
 doc/src/sgml/config.sgml                      | 15 +++++
 doc/src/sgml/pgstatstatements.sgml            |  6 ++
 src/backend/nodes/queryjumblefuncs.c          | 12 +++-
 src/backend/utils/misc/guc_tables.c           | 11 ++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/nodes/queryjumble.h               |  1 +
 8 files changed, 126 insertions(+), 1 deletion(-)

diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out
index 1bb75a93045..81811b2db0d 100644
--- a/contrib/pg_stat_statements/expected/merging.out
+++ b/contrib/pg_stat_statements/expected/merging.out
@@ -218,4 +218,68 @@ FROM cte;
 --------
 (0 rows)
 
+-- With the threshold
+SET query_id_const_merge_threshold = 10;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                   query                                   | calls 
+---------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])                |     2
+ SELECT pg_stat_statements_reset()                                         |     1
+(3 rows)
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                         query                         | calls 
+-------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) |     1
+ SELECT pg_stat_statements_reset()                     |     1
+(2 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                 query                                  | calls 
+------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4)                  |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])             |     1
+ SELECT pg_stat_statements_reset()                                      |     1
+ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" |     1
+(4 rows)
+
 RESET query_id_const_merge;
diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql
index 75960159a1d..1dc0fef9984 100644
--- a/contrib/pg_stat_statements/sql/merging.sql
+++ b/contrib/pg_stat_statements/sql/merging.sql
@@ -68,4 +68,21 @@ WITH cte AS (
 SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
 FROM cte;
 
+-- With the threshold
+SET query_id_const_merge_threshold = 10;
+
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
 RESET query_id_const_merge;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 4fd997284c9..c31d4806c1c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8219,6 +8219,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-query-id-const-merge-threshold" xreflabel="query_id_const_merge_threshold">
+      <term><varname>query_id_const_merge_threshold</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>query_id_const_merge_threshold</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        If <xref linkend="guc-query-id-const-merge"/> parameter is enabled,
+        specifies the minimal number of element an array must have to apply
+        constants merge.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
 
     </sect2>
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 1f6e6a0e76b..eb00c1ecd2a 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -566,6 +566,12 @@ query | SELECT pg_stat_statements_reset()
 calls | 1
 </screen>
 
+   Such constants merging could be configured apply only starting from certain
+   number of constants in the array. The threshold could be specified using
+   <xref linkend="guc-query-id-const-merge"/>.
+  </para>
+
+  <para>
    But there is a small chance of hash collisions causing unrelated queries to
    be merged into one entry. (This cannot happen for queries belonging to
    different users or databases, however.)
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index b198fe2579e..5d3acf1115c 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -47,6 +47,9 @@ int			compute_query_id = COMPUTE_QUERY_ID_AUTO;
 /* Whether to merge constants in a list when computing query_id */
 bool		query_id_const_merge = false;
 
+/* Lower threshold for the list length to merge constants when computing query_id */
+bool		query_id_const_merge_threshold = 1;
+
 /* True when compute_query_id is ON, or AUTO and a module requests them */
 bool		query_id_enabled = false;
 
@@ -227,7 +230,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude)
 
 /*
  * Verify if the provided list contains could be merged down, which means it
- * contains only constant expressions.
+ * contains only constant expressions and the list contains more than
+ * query_id_const_merge_threshold elements.
  *
  * Return value is the order of magnitude (i.e. how many digits it has) for
  * length of the list (to use for representation purposes later on) if merging
@@ -251,6 +255,12 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst)
 		return 0;
 	}
 
+	if (elements->length < query_id_const_merge_threshold)
+	{
+		/* The list is not large enough */
+		return 0;
+	}
+
 	firstExpr = linitial(elements);
 
 	/*
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index fbb50adb9f9..ee239ae7302 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3518,6 +3518,17 @@ struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"query_id_const_merge_threshold", PGC_SUSET, STATS_MONITORING,
+			gettext_noop("Sets lower threshold for an array length to apply"
+						 " constants merging when computing query identifier."),
+			gettext_noop("Not used if query_id_const_merge is disabled"),
+		},
+		&query_id_const_merge_threshold,
+		1, 1, INT_MAX,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 3397780dc72..6cc30e116c4 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -629,6 +629,7 @@
 #log_planner_stats = off
 #log_executor_stats = off
 #query_id_const_merge = off
+#query_id_const_merge_threshold = 1
 
 #------------------------------------------------------------------------------
 # AUTOVACUUM
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index cfa99efc14e..8f823c3c491 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -72,6 +72,7 @@ enum ComputeQueryIdType
 /* GUC parameters */
 extern PGDLLIMPORT int compute_query_id;
 extern PGDLLIMPORT bool query_id_const_merge;
+extern PGDLLIMPORT bool query_id_const_merge_threshold;
 
 extern const char *CleanQuerytext(const char *query, int *location, int *len);
 extern JumbleState *JumbleQuery(Query *query);
-- 
2.41.0

---

From: Michael Paquier <[email-masked]>
Date: Thu, 26 Oct 2023 09:08:42 +0900

typedef struct ArrayExpr
 {

Hmm.  I am not sure that this is the best approach
implementation-wise.  Wouldn't it be better to invent a new
pg_node_attr (these can include parameters as well!), say
query_jumble_merge or query_jumble_agg_location that aggregates all
the parameters of a list to be considered as a single element.  To put
it short, we could also apply the same property to other parts of a
parsed tree, and not only an ArrayExpr's list.

 /* GUC parameters */

Not much a fan of this addition as well for an in-core GUC.  I would
suggest pushing the GUC layer to pg_stat_statements, maintaining the
computation method to use as a field of JumbleState as I suspect that
this is something we should not enforce system-wide, but at
extension-level instead.

+	/*

Not sure that this is the best thing to do either.  Instead of this
extra boolean flag, could it be simpler if we switch LocationLen so as
we track the start position and the end position of a constant in a
query string, so as we'd use one LocationLen for a whole set of Const
nodes in an ArrayExpr?  Perhaps this could just be a refactoring piece
of its own?

+	/*

This path should be benchmarked, IMO.
--
Michael

---

From: Dmitry Dolgov <[email-masked]>
Date: Fri, 27 Oct 2023 17:02:44 +0200

> On Thu, Oct 26, 2023 at 09:08:42AM +0900, Michael Paquier wrote:
>  typedef struct ArrayExpr
>  {

Sounds like an interesting idea, something like:

    typedef struct ArrayExpr
    {
        ...
        List	   *elements pg_node_attr(query_jumble_merge);

to replace simple JUMBLE_NODE(elements) with more elaborated logic.

> this is something we should not enforce system-wide, but at

I also do not particularly like an extra GUC here, but as far as I can
tell to make it pg_stat_statements GUC only it has to be something
similar to EnableQueryId (e.g. EnableQueryConstMerging), that will be
called from pgss. Does this sound better?

> nodes in an ArrayExpr?  Perhaps this could just be a refactoring piece

Sounds interesting as well, but it seems to me there is a catch. I'll
try to elaborate, bear with me:

* if the start and the end positions of a constant means the first and the
last character representing it, we need the textual length of the
constant in the query to be able to construct such a LocationLen.  The
lengths are calculated in pg_stat_statements later, not in JumbleQuery,
and it uses parser for that. Doing all of this in JumbleQuery doesn't
sound reasonable to me.

* if instead we talk about the start and the end positions in a
set of constants, that would mean locations of the first and the last
constants in the set, and everything seems fine. But for such
LocationLen to represent a single constant (not a set of constants), it
means that only the start position would be meaningful, the end position
will not be used.

The second approach is somewhat close to be simpler than the merge flag,
but assumes the ugliness for a single constant. What do you think about
this?

> This path should be benchmarked, IMO.

I can do some benchmarking here, but of course it's going to be slower
than the baseline. The main idea behind the patch is to trade this
overhead for the benefits in the future while processing pgss records,
hoping that it's going to be worth it (and in those extreme cases I'm
trying to address it's definitely worth it).

---

From: Dmitry Dolgov <[email-masked]>
Date: Tue, 31 Oct 2023 10:03:07 +0100

> On Fri, Oct 27, 2023 at 05:02:44PM +0200, Dmitry Dolgov wrote:

For clarity, here is what I had in mind for those two points.
>From 760420fc4aeb96c20d99ae205ee57670d73dc27b Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 14 Oct 2023 15:00:48 +0200
Subject: [PATCH v16 1/4] Prevent jumbling of every element in ArrayExpr

Make an array of Const expressions contribute only the first/last
elements to the jumble hash. Allow to enable this behavior via the new
pg_stat_statements parameter query_id_const_merge with the default value off.

Reviewed-by: Zhihong Yu, Sergey Dudoladov, Robert Haas, Tom Lane,
Michael Paquier, Sergei Kornilov, Alvaro Herrera, David Geier
Tested-by: Chengxi Sun
---
 contrib/pg_stat_statements/Makefile           |   2 +-
 .../pg_stat_statements/expected/merging.out   | 167 ++++++++++++++++++
 contrib/pg_stat_statements/meson.build        |   1 +
 .../pg_stat_statements/pg_stat_statements.c   |  62 ++++++-
 contrib/pg_stat_statements/sql/merging.sql    |  58 ++++++
 doc/src/sgml/pgstatstatements.sgml            |  54 +++++-
 src/backend/nodes/gen_node_support.pl         |  21 ++-
 src/backend/nodes/queryjumblefuncs.c          | 100 ++++++++++-
 src/backend/postmaster/postmaster.c           |   3 +
 src/backend/utils/misc/postgresql.conf.sample |   1 -
 src/include/nodes/primnodes.h                 |   2 +-
 src/include/nodes/queryjumble.h               |   9 +-
 12 files changed, 456 insertions(+), 24 deletions(-)
 create mode 100644 contrib/pg_stat_statements/expected/merging.out
 create mode 100644 contrib/pg_stat_statements/sql/merging.sql

index 00000000000..f286c735a36
+SET pg_stat_statements.query_id_const_merge = on;
+RESET pg_stat_statements.query_id_const_merge;
index a46f2db352b..4d47a746670 100644
@@ -266,6 +266,9 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL;
 static ExecutorEnd_hook_type prev_ExecutorEnd = NULL;
 static ProcessUtility_hook_type prev_ProcessUtility = NULL;
 
+/* An assign hook to keep query_id_const_merge in sync */
+static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra);
+
 /* Links to shared memory state */
 static pgssSharedState *pgss = NULL;
 static HTAB *pgss_hash = NULL;
@@ -293,7 +296,8 @@ static bool pgss_track_utility = true;	/* whether to track utility commands */
 static bool pgss_track_planning = false;	/* whether to track planning
 											 * duration */
 static bool pgss_save = true;	/* whether to save stats across shutdown */
-
+static bool pgss_query_id_const_merge = false;	/* request constants merging
+												 * when computing query_id */
 
 #define pgss_enabled(level) \
 	(!IsParallelWorker() && \
@@ -455,8 +459,21 @@ _PG_init(void)
 							 NULL,
 							 NULL);
 
+	DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge",
+							 "Whether to merge constants in a list when computing query_id.",
+							 NULL,
+							 &pgss_query_id_const_merge,
+							 false,
+							 PGC_SUSET,
+							 0,
+							 NULL,
+							 pgss_query_id_const_merge_assign_hook,
+							 NULL);
+
 	MarkGUCPrefixReserved("pg_stat_statements");
 
+	SetQueryIdConstMerge(pgss_query_id_const_merge);
+
 	/*
 	 * Install hooks.
 	 */
@@ -2695,6 +2712,9 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 				n_quer_loc = 0, /* Normalized query byte location */
 				last_off = 0,	/* Offset from start for previous tok */
 				last_tok_len = 0;	/* Length (in bytes) of that tok */
+	bool		skip = false; 	/* Signals that certain constants are
+								   merged together and have to be skipped */
+
 
 	/*
 	 * Get constants' lengths (core system only gives us locations).  Note
@@ -2718,7 +2738,6 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 	{
 		int			off,		/* Offset from start for cur tok */
 					tok_len;	/* Length (in bytes) of that tok */
-
 		off = jstate->clocations[i].location;
 		/* Adjust recorded location if we're dealing with partial string */
 		off -= query_loc;
@@ -2733,12 +2752,32 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 		len_to_wrt -= last_tok_len;
 
 		Assert(len_to_wrt >= 0);

@@ -2902,3 +2941,12 @@ comp_location(const void *a, const void *b)
+ * Notify query jumbling about query_id_const_merge status
+pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra)
+	SetQueryIdConstMerge(newvalue);
index 00000000000..8b589135daa
+SET pg_stat_statements.query_id_const_merge = on;
+RESET pg_stat_statements.query_id_const_merge;
index 7e7c5c9ff82..bba8e5e11ed 100644
 
   <para>
@@ -861,6 +878,35 @@
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term>
+    <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>)
+    <indexterm>
+     <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary>
+    </indexterm>
+    </term>
+
+    <listitem>
+     <para>
+      Specifies how an array of constants (e.g. for an "IN" clause)
+      contributes to the query identifier computation. Normally every element
+      of an array contributes to the query identifier, which means the same
+      query will get multiple different identifiers, one for each occurrence
+      with an array of different lenght.
+
+      If this parameter is on, an array of constants will contribute only the
+      first and the last elements to the query identifier. It means two
+      occurences of the same query, where the only difference is number of
+      constants in the array, are going to get the same query identifier.
+      Such queries are represented in form <literal>'(...)'</literal>.
+
+      The parameter could be used to reduce amount of repeating data stored
+      via <structname>pg_stat_statements</structname>.  The default value is off.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
 
   <para>
diff --git a/src/backend/nodes/gen_node_support.pl b/src/backend/nodes/gen_node_support.pl
index 72c79635781..9eb1f2dde77 100644
--- a/src/backend/nodes/gen_node_support.pl
+++ b/src/backend/nodes/gen_node_support.pl
@@ -475,6 +475,7 @@ foreach my $infile (@ARGV)
 								equal_ignore_if_zero
 								query_jumble_ignore
 								query_jumble_location
+								query_jumble_merge
 								read_write_ignore
 								write_only_relids
 								write_only_nondefault_pathtarget
@@ -1282,6 +1283,7 @@ _jumble${n}(JumbleState *jstate, Node *node)
 		my @a = @{ $node_type_info{$n}->{field_attrs}{$f} };
 		my $query_jumble_ignore = $struct_no_query_jumble;
 		my $query_jumble_location = 0;
+		my $query_jumble_merge = 0;
 
 		# extract per-field attributes
 		foreach my $a (@a)
@@ -1294,21 +1296,34 @@ _jumble${n}(JumbleState *jstate, Node *node)
 			{
 				$query_jumble_location = 1;
 			}
+			elsif ($a eq 'query_jumble_merge')
+			{
+				$query_jumble_merge = 1;
+			}
 		}
 
 		# node type
 		if (($t =~ /^(\w+)\*$/ or $t =~ /^struct\s+(\w+)\*$/)
 			and elem $1, @node_types)
 		{
-			print $jff "\tJUMBLE_NODE($f);\n"
-			  unless $query_jumble_ignore;
+			# Merge constants if requested.
+			if ($query_jumble_merge)
+			{
+				print $jff "\tJUMBLE_ELEMENTS($f);\n"
+				  unless $query_jumble_ignore;
+			}
+			else
+			{
+				print $jff "\tJUMBLE_NODE($f);\n"
+				  unless $query_jumble_ignore;
+			}
 		}
 		elsif ($t eq 'int' && $f =~ 'location$')
 		{
 			# Track the node's location only if directly requested.
 			if ($query_jumble_location)
 			{
-				print $jff "\tJUMBLE_LOCATION($f);\n"
+				print $jff "\tJUMBLE_LOCATION($f, false);\n"
 				  unless $query_jumble_ignore;
 			}
 		}
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 281907a4d83..4bc16dde6a0 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -42,13 +42,18 @@
 /* GUC parameters */
 int			compute_query_id = COMPUTE_QUERY_ID_AUTO;

 static void AppendJumble(JumbleState *jstate,
 						 const unsigned char *item, Size size);
-static void RecordConstLocation(JumbleState *jstate, int location);
+static void RecordConstLocation(JumbleState *jstate,
+								int location, bool merged);
 static void _jumbleNode(JumbleState *jstate, Node *node);
+static void _jumbleElements(JumbleState *jstate, List *elements);
 static void _jumbleA_Const(JumbleState *jstate, Node *node);
 static void _jumbleList(JumbleState *jstate, Node *node);
 static void _jumbleRangeTblEntry(JumbleState *jstate, Node *node);
@@ -148,6 +153,18 @@ EnableQueryId(void)
 		query_id_enabled = true;
 }
 
+/*
+ * Controls constants merging for query identifier computation.
+ *
+ * Third-party plugins can use this function to enable/disable merging
+ * of constants in a list when query identifier is computed.
+ */
+void
+SetQueryIdConstMerge(bool value)
+{
+	query_id_const_merge = value;
+}
+
 /*
  * AppendJumble: Append a value that is substantive in a given query to
  * the current jumble.
@@ -186,11 +203,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size)
 }
 
 /*
@@ -206,15 +227,67 @@ RecordConstLocation(JumbleState *jstate, int location)
 
+/*
+#define JUMBLE_ELEMENTS(list) \
+	_jumbleElements(jstate, (List *) expr->list)
@@ -227,6 +300,21 @@ do { \

+static void
+_jumbleElements(JumbleState *jstate, List *elements)
+{
+	Const *first, *last;
+	if(IsMergeableConstList(elements, &first, &last))
+	{
+		RecordConstLocation(jstate, first->location, true);
+		RecordConstLocation(jstate, last->location, true);
+	}
+	else
+	{
+		_jumbleNode(jstate, (Node *) elements);
+	}
+}
+
 static void
 _jumbleNode(JumbleState *jstate, Node *node)
 {
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 9cb624eab81..3e5c43ede81 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -528,6 +528,7 @@ typedef struct
 	bool		redirection_done;
 	bool		IsBinaryUpgrade;
 	bool		query_id_enabled;
+	bool		query_id_const_merge;
 	int			max_safe_fds;
 	int			MaxBackends;
 #ifdef WIN32
@@ -6075,6 +6076,7 @@ save_backend_variables(BackendParameters *param, Port *port,
 	param->redirection_done = redirection_done;
 	param->IsBinaryUpgrade = IsBinaryUpgrade;
 	param->query_id_enabled = query_id_enabled;
+	param->query_id_const_merge = query_id_const_merge;
 	param->max_safe_fds = max_safe_fds;
 
 	param->MaxBackends = MaxBackends;
@@ -6306,6 +6308,7 @@ restore_backend_variables(BackendParameters *param, Port *port)
 	redirection_done = param->redirection_done;
 	IsBinaryUpgrade = param->IsBinaryUpgrade;
 	query_id_enabled = param->query_id_enabled;
+	query_id_const_merge = param->query_id_const_merge;
 	max_safe_fds = param->max_safe_fds;
 
 	MaxBackends = param->MaxBackends;
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index d08d55c3fe4..97a8251bb2d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -629,7 +629,6 @@
 #log_planner_stats = off
 #log_executor_stats = off
 
-
 #------------------------------------------------------------------------------
 # AUTOVACUUM
 #------------------------------------------------------------------------------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 60d72a876b4..0b50e20fa69 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1304,7 +1304,7 @@ typedef struct ArrayExpr
 	/* common type of array elements */
 	Oid			element_typeid pg_node_attr(query_jumble_ignore);
 	/* the array elements or sub-arrays */
-	List	   *elements;
+	List	   *elements pg_node_attr(query_jumble_merge);
 	/* true if elements are sub-arrays */
 	bool		multidims pg_node_attr(query_jumble_ignore);
 	/* token location, or -1 if unknown */
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 7649e095aa5..c64a007ad3f 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -23,6 +23,12 @@ typedef struct LocationLen
 {
 	int			location;		/* start offset in query text */
 	int			length;			/* length in bytes, or -1 to ignore */
+
+	/*
+	 * Indicates the constant represents the beginning or the end of a merged
+	 * constants interval.
+	 */
+	bool		merged;
 } LocationLen;
 
 /*
@@ -62,12 +68,13 @@ enum ComputeQueryIdType
 /* GUC parameters */
 extern PGDLLIMPORT int compute_query_id;
 
-
 extern const char *CleanQuerytext(const char *query, int *location, int *len);
 extern JumbleState *JumbleQuery(Query *query);
 extern void EnableQueryId(void);
+extern void SetQueryIdConstMerge(bool value);
 
 extern PGDLLIMPORT bool query_id_enabled;
+extern PGDLLIMPORT bool query_id_const_merge;
 
 /*
  * Returns whether query identifier computation has been enabled, either

>From 72ed59a704b67a0b26b860a34311df9d37851a13 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Fri, 17 Feb 2023 10:17:55 +0100
Subject: [PATCH v16 2/4] Reusable decimalLength functions

 
-#include "common/int.h"
 
 /*
 
-/*

>From 0b6f74eecd2a6ec0fe7361c7933f28cfffb30a18 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sun, 15 Oct 2023 10:06:09 +0200
Subject: [PATCH v16 3/4] Merge constants in ArrayExpr into groups

 doc/src/sgml/pgstatstatements.sgml            | 11 +--
 6 files changed, 142 insertions(+), 42 deletions(-)

index f286c735a36..7400870f3f6 100644

 

 
index 4d47a746670..a5702c3d749 100644
@@ -2714,6 +2714,8 @@ generate_normalized_query(JumbleState *jstate, const char *query,

 	/*
@@ -2754,7 +2756,8 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 		Assert(len_to_wrt >= 0);
 
@@ -2770,12 +2773,22 @@ generate_normalized_query(JumbleState *jstate, const char *query,

index 8b589135daa..c515e48d50c 100644

index bba8e5e11ed..a919696abc2 100644
@@ -896,10 +896,11 @@ calls | 1
 
-      Such queries are represented in form <literal>'(...)'</literal>.
+      Such queries are represented in form <literal>'(... [10-99 entries])'</literal>.
 
       The parameter could be used to reduce amount of repeating data stored
       via <structname>pg_stat_statements</structname>.  The default value is off.
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 4bc16dde6a0..a1d4567ca66 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -37,6 +37,8 @@
 #include "nodes/queryjumble.h"
 #include "parser/scansup.h"

 /* GUC parameters */
 static void _jumbleElements(JumbleState *jstate, List *elements);
@@ -206,12 +208,15 @@ AppendJumble(JumbleState *jstate, const unsigned char *item, Size size)
@@ -227,7 +232,7 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged)
@@ -237,24 +242,26 @@ RecordConstLocation(JumbleState *jstate, int location, bool merged)
 
 	if (elements == NULL)
-		return false;
+		return 0;

 	firstExpr = linitial(elements);
@@ -268,26 +275,26 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst)
 	{
 		foreach(temp, elements)
 			if (!IsA(lfirst(temp), Const))
-				return false;
+				return 0;

 	/*
 
 #define JUMBLE_NODE(item) \
 	_jumbleNode(jstate, (Node *) expr->item)
 #define JUMBLE_ELEMENTS(list) \
 	_jumbleElements(jstate, (List *) expr->list)
-#define JUMBLE_LOCATION(location, merged) \
-	RecordConstLocation(jstate, expr->location, merged)
+#define JUMBLE_LOCATION(location, magnitude) \
+	RecordConstLocation(jstate, expr->location, magnitude)
 #define JUMBLE_FIELD(item) \
 	AppendJumble(jstate, (const unsigned char *) &(expr->item), sizeof(expr->item))
 #define JUMBLE_FIELD_SINGLE(item) \
@@ -304,10 +311,19 @@ static void
 _jumbleElements(JumbleState *jstate, List *elements)
 {
 	Const *first, *last;
-	if(IsMergeableConstList(elements, &first, &last))
+	int magnitude = IsMergeableConstList(elements, &first, &last);
+
+	if (magnitude)
 	{
-		RecordConstLocation(jstate, first->location, true);
-		RecordConstLocation(jstate, last->location, true);
+		RecordConstLocation(jstate, first->location, magnitude);
+		RecordConstLocation(jstate, last->location, magnitude);
+
+		/*
+		 * After merging constants down we end up with only two constants, the
+		 * first and the last one. To distinguish the order of magnitute behind
+		 * merged constants, add its value into the jumble.
+		 */
+		JUMBLE_FIELD_SINGLE(magnitude);
 	}
 	else
 	{
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index c64a007ad3f..8ee2e9afbb6 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -26,9 +26,12 @@ typedef struct LocationLen
 
 	/*
 
 /*
-- 
2.41.0

>From 12a515de2950a8d78e7f19e08b76aedc20a3433f Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Mon, 16 Oct 2023 16:52:27 +0200
Subject: [PATCH v16 4/4] Introduce query_id_const_merge_threshold

Replace query_id_const_merge with a threshold to allow merging only if
the number of elements is larger than specified value, which could be
configured using pg_stat_statements parameter query_id_const_merge_threshold.
---
 .../pg_stat_statements/expected/merging.out   | 68 ++++++++++++++++++-
 .../pg_stat_statements/pg_stat_statements.c   | 36 +++++-----
 contrib/pg_stat_statements/sql/merging.sql    | 21 +++++-
 doc/src/sgml/pgstatstatements.sgml            | 19 +++---
 src/backend/nodes/queryjumblefuncs.c          | 23 +++++--
 src/backend/postmaster/postmaster.c           |  6 +-
 src/include/nodes/queryjumble.h               |  4 +-
 7 files changed, 135 insertions(+), 42 deletions(-)

diff --git a/contrib/pg_stat_statements/expected/merging.out b/contrib/pg_stat_statements/expected/merging.out
index 7400870f3f6..93d59149bf0 100644
--- a/contrib/pg_stat_statements/expected/merging.out
+++ b/contrib/pg_stat_statements/expected/merging.out
@@ -36,7 +36,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
 (4 rows)
 
 -- Normal scenario, too many simple constants for an IN query
-SET pg_stat_statements.query_id_const_merge = on;
+SET pg_stat_statements.query_id_const_merge_threshold = 1;
 SELECT pg_stat_statements_reset();
  pg_stat_statements_reset 
 --------------------------
@@ -218,4 +218,68 @@ FROM cte;
 --------
 (0 rows)
 
-RESET pg_stat_statements.query_id_const_merge;
+-- With the threshold
+SET pg_stat_statements.query_id_const_merge_threshold = 10;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+ id | data 
+----+------
+(0 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                   query                                   | calls 
+---------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9) |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])                |     2
+ SELECT pg_stat_statements_reset()                                         |     1
+(3 rows)
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset 
+--------------------------
+ 
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                         query                         | calls 
+-------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4) |     1
+ SELECT pg_stat_statements_reset()                     |     1
+(2 rows)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+ id | data 
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                                 query                                  | calls 
+------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge WHERE id IN ($1, $2, $3, $4)                  |     1
+ SELECT * FROM test_merge WHERE id IN (... [10-99 entries])             |     1
+ SELECT pg_stat_statements_reset()                                      |     1
+ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" |     1
+(4 rows)
+
+RESET pg_stat_statements.query_id_const_merge_threshold;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index a5702c3d749..e02171c6767 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -266,8 +266,8 @@ static ExecutorFinish_hook_type prev_ExecutorFinish = NULL;
 static ExecutorEnd_hook_type prev_ExecutorEnd = NULL;
 static ProcessUtility_hook_type prev_ProcessUtility = NULL;
 
-/* An assign hook to keep query_id_const_merge in sync */
-static void pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra);
+/* An assign hook to keep query_id_const_merge_threshold in sync */
+static void pgss_query_id_const_merge_assign_hook(int newvalue, void *extra);
 
 /* Links to shared memory state */
 static pgssSharedState *pgss = NULL;
@@ -296,8 +296,8 @@ static bool pgss_track_utility = true;	/* whether to track utility commands */
 static bool pgss_track_planning = false;	/* whether to track planning
 											 * duration */
 static bool pgss_save = true;	/* whether to save stats across shutdown */
-static bool pgss_query_id_const_merge = false;	/* request constants merging
-												 * when computing query_id */
+static int  pgss_query_id_const_merge_threshold = 0;	/* request constants merging
+														 * when computing query_id */
 
 #define pgss_enabled(level) \
 	(!IsParallelWorker() && \
@@ -459,20 +459,22 @@ _PG_init(void)
 							 NULL,
 							 NULL);
 
-	DefineCustomBoolVariable("pg_stat_statements.query_id_const_merge",
-							 "Whether to merge constants in a list when computing query_id.",
-							 NULL,
-							 &pgss_query_id_const_merge,
-							 false,
-							 PGC_SUSET,
-							 0,
-							 NULL,
-							 pgss_query_id_const_merge_assign_hook,
-							 NULL);
+	DefineCustomIntVariable("pg_stat_statements.query_id_const_merge_threshold",
+							"Whether to merge constants in a list when computing query_id.",
+							NULL,
+							&pgss_query_id_const_merge_threshold,
+							0,
+							0,
+							INT_MAX,
+							PGC_SUSET,
+							0,
+							NULL,
+							pgss_query_id_const_merge_assign_hook,
+							NULL);
 
 	MarkGUCPrefixReserved("pg_stat_statements");
 
-	SetQueryIdConstMerge(pgss_query_id_const_merge);
+	SetQueryIdConstMerge(pgss_query_id_const_merge_threshold);
 
 	/*
 	 * Install hooks.
@@ -2956,10 +2958,10 @@ comp_location(const void *a, const void *b)
 }
 
 /*
- * Notify query jumbling about query_id_const_merge status
+ * Notify query jumbling about query_id_const_merge_threshold status
  */
 static void
-pgss_query_id_const_merge_assign_hook(bool newvalue, void *extra)
+pgss_query_id_const_merge_assign_hook(int newvalue, void *extra)
 {
 	SetQueryIdConstMerge(newvalue);
 }
diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql
index c515e48d50c..52ee4fcb216 100644
--- a/contrib/pg_stat_statements/sql/merging.sql
+++ b/contrib/pg_stat_statements/sql/merging.sql
@@ -15,7 +15,7 @@ SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 -- Normal scenario, too many simple constants for an IN query
-SET pg_stat_statements.query_id_const_merge = on;
+SET pg_stat_statements.query_id_const_merge_threshold = 1;
 
 SELECT pg_stat_statements_reset();
 SELECT * FROM test_merge WHERE id IN (1);
@@ -68,4 +68,21 @@ WITH cte AS (
 SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
 FROM cte;
 
-RESET pg_stat_statements.query_id_const_merge;
+-- With the threshold
+SET pg_stat_statements.query_id_const_merge_threshold = 10;
+
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+RESET pg_stat_statements.query_id_const_merge_threshold;
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index a919696abc2..81e6ab2c0c8 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -549,11 +549,11 @@
    In some cases, queries with visibly different texts might get merged into a
    single <structname>pg_stat_statements</structname> entry.  Normally this will happen
    only for semantically equivalent queries, or if
-   <xref linkend="guc-query-id-const-merge"/> is enabled and the only
+   <xref linkend="guc-query-id-const-merge-threshold"/> is greater than 0 and the only
    difference between queries is the length of an array with constants they contain:
 
 <screen>
-=# SET query_id_const_merge = on;
+=# SET query_id_const_merge_threshold = 1;
 =# SELECT pg_stat_statements_reset();
 =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
 =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
@@ -881,9 +881,9 @@ calls | 1
 
    <varlistentry>
     <term>
-    <varname>pg_stat_statements.query_id_const_merge</varname> (<type>bool</type>)
+    <varname>pg_stat_statements.query_id_const_merge_threshold</varname> (<type>integer</type>)
     <indexterm>
-     <primary><varname>pg_stat_statements.query_id_const_merge</varname> configuration parameter</primary>
+     <primary><varname>pg_stat_statements.query_id_const_merge_threshold</varname> configuration parameter</primary>
     </indexterm>
     </term>
 
@@ -895,11 +895,12 @@ calls | 1
       query will get multiple different identifiers, one for each occurrence
       with an array of different lenght.
 
-      If this parameter is on, an array of constants will contribute only the
-      first element, the last element and the number of elements to the query
-      identifier. It means two occurences of the same query, where the only
-      difference is number of constants in the array, are going to get the
-      same query identifier if the arrays are of similar length.
+      If this parameter is greater than 0, an array with more than
+      <varname>pg_stat_statements.query_id_const_merge_threshold</varname>
+      constants will contribute only the first element, the last element
+      and the number of elements to the query identifier. It means two
+      occurences of the same query, where the only difference is number of
+      constants in the array, are going to get the same query identifier.
       Such queries are represented in form <literal>'(... [10-99 entries])'</literal>.
 
index a1d4567ca66..10be62f1331 100644
@@ -44,8 +44,8 @@
 
-/* Whether to merge constants in a list when computing query_id */
-bool		query_id_const_merge = false;
+/* Lower threshold for the list length to merge constants when computing query_id */
+int			query_id_const_merge_threshold = 1;
 
 /* True when compute_query_id is ON, or AUTO and a module requests them */
 bool		query_id_enabled = false;
@@ -159,12 +159,14 @@ EnableQueryId(void)
  * Controls constants merging for query identifier computation.
  *
  * Third-party plugins can use this function to enable/disable merging
- * of constants in a list when query identifier is computed.
+ * of constants in a list when query identifier is computed. The argument
+ * specifies the lower threshold for an array length, above which merging will
+ * be applied.
  */
 void
-SetQueryIdConstMerge(bool value)
+SetQueryIdConstMerge(int threshold)
 {
-	query_id_const_merge = value;
+	query_id_const_merge_threshold = threshold;
 }
 
 /*
@@ -240,7 +242,8 @@ RecordConstLocation(JumbleState *jstate, int location, int magnitude)
 
 /*
@@ -258,12 +261,18 @@ IsMergeableConstList(List *elements, Const **firstConst, Const **lastConst)
 	if (elements == NULL)
 		return 0;
 
-	if (!query_id_const_merge)
+	if (query_id_const_merge_threshold < 1)
 	{
 		/* Merging is disabled, process everything one by one */
 		return 0;
 	}

 	/*
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 3e5c43ede81..3094d54bab8 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -528,7 +528,7 @@ typedef struct
 	bool		redirection_done;
 	bool		IsBinaryUpgrade;
 	bool		query_id_enabled;
-	bool		query_id_const_merge;
+	int			query_id_const_merge_threshold;
 	int			max_safe_fds;
 	int			MaxBackends;
 #ifdef WIN32
@@ -6076,7 +6076,7 @@ save_backend_variables(BackendParameters *param, Port *port,
 	param->redirection_done = redirection_done;
 	param->IsBinaryUpgrade = IsBinaryUpgrade;
 	param->query_id_enabled = query_id_enabled;
-	param->query_id_const_merge = query_id_const_merge;
+	param->query_id_const_merge_threshold = query_id_const_merge_threshold;
 	param->max_safe_fds = max_safe_fds;
 
 	param->MaxBackends = MaxBackends;
@@ -6308,7 +6308,7 @@ restore_backend_variables(BackendParameters *param, Port *port)
 	redirection_done = param->redirection_done;
 	IsBinaryUpgrade = param->IsBinaryUpgrade;
 	query_id_enabled = param->query_id_enabled;
-	query_id_const_merge = param->query_id_const_merge;
+	query_id_const_merge_threshold = param->query_id_const_merge_threshold;
 	max_safe_fds = param->max_safe_fds;
 
 	MaxBackends = param->MaxBackends;
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 8ee2e9afbb6..a9f8cfcbed9 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -74,10 +74,10 @@ extern PGDLLIMPORT int compute_query_id;
 extern const char *CleanQuerytext(const char *query, int *location, int *len);
 extern JumbleState *JumbleQuery(Query *query);
 extern void EnableQueryId(void);
-extern void SetQueryIdConstMerge(bool value);
+extern void SetQueryIdConstMerge(int threshold);
 
 extern PGDLLIMPORT bool query_id_enabled;
-extern PGDLLIMPORT bool query_id_const_merge;
+extern PGDLLIMPORT int 	query_id_const_merge_threshold;
 
 /*
  * Returns whether query identifier computation has been enabled, either
-- 
2.41.0

---

From: vignesh C <[email-masked]>
Date: Sat, 6 Jan 2024 21:04:54 +0530

> For clarity, here is what I had in mind for those two points.

CFBot shows documentation build has failed at [1] with:
[07:44:55.531] time make -s -j${BUILD_JOBS} -C doc
[07:44:57.987] postgres.sgml:572: element xref: validity error : IDREF
attribute linkend references an unknown ID
"guc-query-id-const-merge-threshold"
[07:44:58.179] make[2]: *** [Makefile:70: postgres-full.xml] Error 4
[07:44:58.179] make[2]: *** Deleting file 'postgres-full.xml'
[07:44:58.181] make[1]: *** [Makefile:8: all] Error 2
[07:44:58.182] make: *** [Makefile:16: all] Error 2

[1] - https://cirrus-ci.com/task/6688578378399744

Regards,
Vignesh

---

From: Dmitry Dolgov <[email-masked]>
Date: Mon, 8 Jan 2024 17:10:20 +0100

> On Sat, Jan 06, 2024 at 09:04:54PM +0530, vignesh C wrote:
>

Indeed, after moving the configuration option to pgss I forgot to update
its reference in the docs. Thanks for noticing, will update soon.

---

From: Dmitry Dolgov <[email-masked]>
Date: Sat, 13 Jan 2024 15:05:38 +0100

> On Mon, Jan 08, 2024 at 05:10:20PM +0100, Dmitry Dolgov wrote:

Here is the fixed version.
>From 1b99ffd68de6e82d9bbc45c18153ef965a228e28 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 14 Oct 2023 15:00:48 +0200
Subject: [PATCH v17 1/4] Prevent jumbling of every element in ArrayExpr

 doc/src/sgml/pgstatstatements.sgml            |  57 +++++-
 12 files changed, 458 insertions(+), 25 deletions(-)

 

 
 
 	/*
 
 		Assert(len_to_wrt >= 0);

index 7e7c5c9ff82..c78140f8858 100644
@@ -547,11 +547,29 @@
 
   <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
-   only for semantically equivalent queries, but there is a small chance of
-   hash collisions causing unrelated queries to be merged into one entry.
-   (This cannot happen for queries belonging to different users or databases,
-   however.)
+   single <structname>pg_stat_statements</structname> entry.  Normally this
+   will happen only for semantically equivalent queries, or if
+   <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and
+   the only difference between queries is the length of an array with constants
+   they contain:
+
+<screen>
+=# SET query_id_const_merge = on;
+=# SELECT pg_stat_statements_reset();
+=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+=# SELECT query, calls FROM pg_stat_statements;
+-[ RECORD 1 ]------------------------------
+query | SELECT * FROM test WHERE a IN (...)
+calls | 2
+-[ RECORD 2 ]------------------------------
+query | SELECT pg_stat_statements_reset()
+calls | 1
+</screen>
+
+   But there is a small chance of hash collisions causing unrelated queries to
+   be merged into one entry. (This cannot happen for queries belonging to
+   different users or databases, however.)
   </para>
 
   <para>
@@ -861,6 +879,35 @@
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term>
 
   <para>

 		# node type

 
+/*
 
 /*
 
+/*

+static void

 
-
 
 /*
 
-

 /*

>From a38b19f74c51ec48cade87e4df2b499a84c32823 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Fri, 17 Feb 2023 10:17:55 +0100
Subject: [PATCH v17 2/4] Reusable decimalLength functions

 
-#include "common/int.h"
 
 /*
 
-/*

>From 1a452c9c474812b3e670564aa7b5386928478d08 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sun, 15 Oct 2023 10:06:09 +0200
Subject: [PATCH v17 3/4] Merge constants in ArrayExpr into groups

 

 

 	/*

 

index c78140f8858..ff24153c493 100644
@@ -560,7 +560,7 @@
@@ -897,10 +897,11 @@ calls | 1

 
 
 /* GUC parameters */
 
 	if (elements == NULL)
-		return false;
+		return 0;

 
 
 	/*

 	/*
 
 /*
-- 
2.41.0

>From 5f1f55547b2bc0380f9e6ee5d729f9eb4801bbb6 Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email-masked]>
Date: Sat, 13 Jan 2024 14:52:39 +0100
Subject: [PATCH v17 4/4] Introduce query_id_const_merge_threshold

 doc/src/sgml/pgstatstatements.sgml            | 23 ++++---
 7 files changed, 137 insertions(+), 44 deletions(-)

 

 

 
 
 	/*
 	 * Install hooks.
 
 /*

 
-RESET pg_stat_statements.query_id_const_merge;
+-- With the threshold
+SET pg_stat_statements.query_id_const_merge_threshold = 10;
+
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- With gaps on the threshold
+SELECT pg_stat_statements_reset();
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+RESET pg_stat_statements.query_id_const_merge_threshold;
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index ff24153c493..5545accc560 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -549,12 +549,12 @@
    In some cases, queries with visibly different texts might get merged into a
    single <structname>pg_stat_statements</structname> entry.  Normally this
    will happen only for semantically equivalent queries, or if
-   <varname>pg_stat_statements.query_id_const_merge</varname> is enabled and
-   the only difference between queries is the length of an array with constants
-   they contain:
+   <varname>pg_stat_statements.query_id_const_merge_threshold</varname> is
+   enabled and the only difference between queries is the length of an array
+   with constants they contain:
 
 <screen>
@@ -882,9 +882,9 @@ calls | 1
 
    <varlistentry>
     <term>
 
@@ -896,11 +896,12 @@ calls | 1
       query will get multiple different identifiers, one for each occurrence
       with an array of different lenght.

 

 /*
 
 /*

 
 	/*

 
 
 /*

---

From: Peter Smith <[email-masked]>
Date: Mon, 22 Jan 2024 17:33:26 +1100

2024-01 Commitfest.

Hi, This patch has a CF status of "Needs Review" [1], but it seems
there was a CFbot test failure last time it was run [2]. Please have a
look and post an updated version if necessary.

======
[1] https://commitfest.postgresql.org/46/2837/
[2] https://cirrus-ci.com/task/6688578378399744

Kind Regards,
Peter Smith.

---

From: Dmitry Dolgov <[email-masked]>
Date: Mon, 22 Jan 2024 17:11:23 +0100

> On Mon, Jan 22, 2024 at 05:33:26PM +1100, Peter Smith wrote:
> 2024-01 Commitfest.
>

It's the same failing pipeline Vignesh C was talking above. I've fixed
the issue in the latest patch version, but looks like it wasn't picked
up yet (from what I understand, the latest build for this CF is 8 weeks
old).

---

From: Tom Lane <[email-masked]>
Date: Mon, 22 Jan 2024 11:35:22 -0500

> up yet (from what I understand, the latest build for this CF is 8 weeks

Please notice that at the moment, it's not being tested at all because
of a patch-apply failure -- that's what the little triangular symbol
means.  The rest of the display concerns the test results from the
last successfully-applied patch version.  (Perhaps that isn't a
particularly great UI design.)

If you click on the triangle you find out

== Applying patches on top of PostgreSQL commit ID b0f0a9432d0b6f53634a96715f2666f6d4ea25a1 ===
=== applying patch ./v17-0001-Prevent-jumbling-of-every-element-in-ArrayExpr.patch
patching file contrib/pg_stat_statements/Makefile
Hunk #1 FAILED at 19.
1 out of 1 hunk FAILED -- saving rejects to file contrib/pg_stat_statements/Makefile.rej
patching file contrib/pg_stat_statements/expected/merging.out
patching file contrib/pg_stat_statements/meson.build
...

			regards, tom lane

---

From: Dmitry Dolgov <[email-masked]>
Date: Mon, 22 Jan 2024 18:07:27 +0100

> On Mon, Jan 22, 2024 at 11:35:22AM -0500, Tom Lane wrote:

Oh, I see, thanks. Give me a moment, will fix this.

---

From: Dmitry Dolgov <[email-masked]>
Date: Mon, 22 Jan 2024 22:00:36 +0100

> On Mon, Jan 22, 2024 at 06:07:27PM +0100, Dmitry Dolgov wrote:

Here is it.