public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 2/3] Make explain analyze default to BUFFERS TRUE..
14+ messages / 5 participants
[nested] [flat]
* [PATCH 2/3] Make explain analyze default to BUFFERS TRUE..
@ 2020-07-23 00:20 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Justin Pryzby @ 2020-07-23 00:20 UTC (permalink / raw)
Opened question: should autoexplain too ?
---
contrib/auto_explain/auto_explain.c | 4 +-
doc/src/sgml/config.sgml | 4 +-
doc/src/sgml/perform.sgml | 1 +
doc/src/sgml/ref/explain.sgml | 5 +-
src/backend/commands/explain.c | 7 ++
.../regress/expected/incremental_sort.out | 26 ++++-
src/test/regress/expected/memoize.out | 2 +-
src/test/regress/expected/partition_prune.out | 96 +++++++++----------
src/test/regress/expected/select_parallel.out | 4 +-
src/test/regress/expected/subselect.out | 2 +-
src/test/regress/expected/tidscan.out | 6 +-
src/test/regress/sql/incremental_sort.sql | 2 +-
src/test/regress/sql/memoize.sql | 2 +-
src/test/regress/sql/partition_prune.sql | 96 +++++++++----------
src/test/regress/sql/select_parallel.sql | 4 +-
src/test/regress/sql/subselect.sql | 2 +-
src/test/regress/sql/tidscan.sql | 6 +-
17 files changed, 149 insertions(+), 120 deletions(-)
diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index e9092ba359..44ddee89da 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -26,7 +26,7 @@ PG_MODULE_MAGIC;
static int auto_explain_log_min_duration = -1; /* msec or -1 */
static bool auto_explain_log_analyze = false;
static bool auto_explain_log_verbose = false;
-static bool auto_explain_log_buffers = false;
+static bool auto_explain_log_buffers = false; // XXX
static bool auto_explain_log_wal = false;
static bool auto_explain_log_triggers = false;
static bool auto_explain_log_timing = true;
@@ -144,7 +144,7 @@ _PG_init(void)
&auto_explain_log_buffers,
false,
PGC_SUSET,
- 0,
+ 0, // XXX
NULL,
NULL,
NULL);
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3f806740d5..9a78f62434 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7612,8 +7612,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
displayed in <link linkend="monitoring-pg-stat-database-view">
<structname>pg_stat_database</structname></link>, in the output of
<xref linkend="sql-explain"/> when the <literal>BUFFERS</literal> option
- is used, by autovacuum for auto-vacuums and auto-analyzes, when
- <xref linkend="guc-log-autovacuum-min-duration"/> is set and by
+ is enabled, by autovacuum for auto-vacuums and auto-analyzes, when
+ <xref linkend="guc-log-autovacuum-min-duration"/> is set, and by
<xref linkend="pgstatstatements"/>. Only superusers can change this
setting.
</para>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e..16383b8f5f 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -731,6 +731,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
</para>
<para>
+XXX
<command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with
<literal>ANALYZE</literal> to get even more run time statistics:
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 4d758fb237..8e9e834771 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -189,8 +189,9 @@ ROLLBACK;
query processing.
The number of blocks shown for an
upper-level node includes those used by all its child nodes. In text
- format, only non-zero values are printed. It defaults to
- <literal>FALSE</literal>.
+ format, only non-zero values are printed. This parameter may only be
+ used when <literal>ANALYZE</literal> is also enabled. It defaults to
+ <literal>TRUE</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 673ad7651f..c5ade3554e 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -174,6 +174,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
bool timing_set = false;
bool summary_set = false;
bool costs_set = false;
+ bool buffers_set = false;
/* Parse options list. */
foreach(lc, stmt->options)
@@ -191,7 +192,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
es->costs = defGetBoolean(opt);
}
else if (strcmp(opt->defname, "buffers") == 0)
+ {
+ buffers_set = true;
es->buffers = defGetBoolean(opt);
+ }
else if (strcmp(opt->defname, "wal") == 0)
es->wal = defGetBoolean(opt);
else if (strcmp(opt->defname, "settings") == 0)
@@ -244,6 +248,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
/* if the costs option was not set explicitly, set default value */
es->costs = (costs_set) ? es->costs : es->costs && !regress;
+ /* if the buffers option was not set explicitly, set default value */
+ es->buffers = (buffers_set) ? es->buffers : es->analyze && !regress;
+
if (es->wal && !es->analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 545e301e48..43de2745cf 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -52,7 +52,7 @@ declare
line text;
begin
for line in
- execute 'explain (analyze, costs off, summary off, timing off) ' || query
+ execute 'explain (analyze, regress) ' || query
loop
out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g');
return next;
@@ -574,7 +574,17 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
"Average Sort Space Used": "NN"+
} +
}, +
- "Parent Relationship": "Outer" +
+ "Local Hit Blocks": 0, +
+ "Temp Read Blocks": 0, +
+ "Local Read Blocks": 0, +
+ "Shared Hit Blocks": 9, +
+ "Shared Read Blocks": 0, +
+ "Parent Relationship": "Outer", +
+ "Temp Written Blocks": 0, +
+ "Local Dirtied Blocks": 0, +
+ "Local Written Blocks": 0, +
+ "Shared Dirtied Blocks": 0, +
+ "Shared Written Blocks": 0 +
} +
]
(1 row)
@@ -776,6 +786,9 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
"Average Sort Space Used": "NN"+
} +
}, +
+ "Local Hit Blocks": 0, +
+ "Temp Read Blocks": 0, +
+ "Local Read Blocks": 0, +
"Pre-sorted Groups": { +
"Group Count": 5, +
"Sort Methods Used": [ +
@@ -787,7 +800,14 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
"Average Sort Space Used": "NN"+
} +
}, +
- "Parent Relationship": "Outer" +
+ "Shared Hit Blocks": 14, +
+ "Shared Read Blocks": 0, +
+ "Parent Relationship": "Outer", +
+ "Temp Written Blocks": 0, +
+ "Local Dirtied Blocks": 0, +
+ "Local Written Blocks": 0, +
+ "Shared Dirtied Blocks": 0, +
+ "Shared Written Blocks": 0 +
} +
]
(1 row)
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 9a025c4a7a..0a10c97894 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -10,7 +10,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
query)
loop
if hide_hitmiss = true then
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c77..111f5fc3a6 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1766,7 +1766,7 @@ create table ab_a3_b3 partition of ab_a3 for values in (3);
set enable_indexonlyscan = off;
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
+explain (analyze, regress) execute ab_q1 (2, 2, 3);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1779,7 +1779,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
(8 rows)
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+explain (analyze, regress) execute ab_q1 (1, 2, 3);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1802,7 +1802,7 @@ deallocate ab_q1;
-- Runtime pruning after optimizer pruning
prepare ab_q1 (int, int) as
select a from ab where a between $1 and $2 and b < 3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
+explain (analyze, regress) execute ab_q1 (2, 2);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1813,7 +1813,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
(6 rows)
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+explain (analyze, regress) execute ab_q1 (2, 4);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1832,7 +1832,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
-- different levels of partitioning.
prepare ab_q2 (int, int) as
select a from ab where a between $1 and $2 and b < (select 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+explain (analyze, regress) execute ab_q2 (2, 2);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1891,7 +1891,7 @@ begin;
-- Test run-time pruning using stable functions
create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
-- Ensure pruning works using a stable function containing no Vars
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
+explain (analyze, regress) select * from list_part where a = list_part_fn(1);
QUERY PLAN
------------------------------------------------------------------
Append (actual rows=1 loops=1)
@@ -1901,7 +1901,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
(4 rows)
-- Ensure pruning does not take place when the function has a Var parameter
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
+explain (analyze, regress) select * from list_part where a = list_part_fn(a);
QUERY PLAN
------------------------------------------------------------------
Append (actual rows=4 loops=1)
@@ -1916,7 +1916,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
(9 rows)
-- Ensure pruning does not take place when the expression contains a Var.
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
+explain (analyze, regress) select * from list_part where a = list_part_fn(1) + a;
QUERY PLAN
------------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1952,7 +1952,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
$1)
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
@@ -2260,7 +2260,7 @@ reset parallel_tuple_cost;
reset min_parallel_table_scan_size;
reset max_parallel_workers_per_gather;
-- Test run-time partition pruning with an initplan
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
QUERY PLAN
-------------------------------------------------------------------------
@@ -2319,7 +2319,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1
(52 rows)
-- Test run-time partition pruning with UNION ALL parents
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
QUERY PLAN
-------------------------------------------------------------------------------
@@ -2363,7 +2363,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
(37 rows)
-- A case containing a UNION ALL with a non-partitioned child.
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
QUERY PLAN
-------------------------------------------------------------------------------
@@ -2422,7 +2422,7 @@ union all
select tableoid::regclass,a,b from ab
) ab where a = $1 and b = (select -10);
-- Ensure the xy_1 subplan is not pruned.
-explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
+explain (analyze, regress) execute ab_q6(1);
QUERY PLAN
--------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2463,7 +2463,7 @@ deallocate ab_q5;
deallocate ab_q6;
-- UPDATE on a partition subtree has been seen to have problems.
insert into ab values (1,2);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
QUERY PLAN
-------------------------------------------------------------------------------------------
@@ -2512,7 +2512,7 @@ table ab;
-- Test UPDATE where source relation has run-time pruning enabled
truncate ab;
insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
QUERY PLAN
------------------------------------------------------------------------------
@@ -2567,7 +2567,7 @@ create index tprt6_idx on tprt_6 (col1);
insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
set enable_hashjoin = off;
set enable_mergejoin = off;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2588,7 +2588,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
Index Cond: (col1 < tbl1.col1)
(15 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2633,7 +2633,7 @@ order by tbl1.col1, tprt.col1;
-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2654,7 +2654,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
Index Cond: (col1 < tbl1.col1)
(15 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2718,7 +2718,7 @@ order by tbl1.col1, tprt.col1;
-- Last partition
delete from tbl1;
insert into tbl1 values (4400);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2750,7 +2750,7 @@ order by tbl1.col1, tprt.col1;
-- No matching partition
delete from tbl1;
insert into tbl1 values (10000);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
-------------------------------------------------------------------
@@ -2790,7 +2790,7 @@ alter table part_cab attach partition part_abc_p1 for values in(3);
prepare part_abc_q1 (int, int, int) as
select * from part_abc where a = $1 and b = $2 and c = $3;
-- Single partition should be scanned.
-explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+explain (analyze, regress) execute part_abc_q1 (1, 2, 3);
QUERY PLAN
----------------------------------------------------------
Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1)
@@ -2815,7 +2815,7 @@ select * from listp where b = 1;
-- partitions before finally detecting the correct set of 2nd level partitions
-- which match the given parameter.
prepare q1 (int,int) as select * from listp where b in ($1,$2);
-explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
+explain (analyze, regress) execute q1 (1,1);
QUERY PLAN
-------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2824,7 +2824,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
Filter: (b = ANY (ARRAY[$1, $2]))
(4 rows)
-explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
+explain (analyze, regress) execute q1 (2,2);
QUERY PLAN
-------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2834,7 +2834,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
(4 rows)
-- Try with no matching partitions.
-explain (analyze, costs off, summary off, timing off) execute q1 (0,0);
+explain (analyze, regress) execute q1 (0,0);
QUERY PLAN
--------------------------------
Append (actual rows=0 loops=1)
@@ -2845,7 +2845,7 @@ deallocate q1;
-- Test more complex cases where a not-equal condition further eliminates partitions.
prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
-- Both partitions allowed by IN clause, but one disallowed by <> clause
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
+explain (analyze, regress) execute q1 (1,2,2,0);
QUERY PLAN
-------------------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2855,7 +2855,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
(4 rows)
-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
+explain (analyze, regress) execute q1 (1,2,2,1);
QUERY PLAN
--------------------------------
Append (actual rows=0 loops=1)
@@ -2863,7 +2863,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
(2 rows)
-- Ensure Params that evaluate to NULL properly prune away all partitions
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select null::int);
QUERY PLAN
------------------------------------------------------
@@ -2888,7 +2888,7 @@ create table stable_qual_pruning2 partition of stable_qual_pruning
create table stable_qual_pruning3 partition of stable_qual_pruning
for values from ('3000-02-01') to ('3000-03-01');
-- comparison against a stable value requires run-time pruning
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < localtimestamp;
QUERY PLAN
--------------------------------------------------------------------------------------
@@ -2901,7 +2901,7 @@ select * from stable_qual_pruning where a < localtimestamp;
(6 rows)
-- timestamp < timestamptz comparison is only stable, not immutable
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
QUERY PLAN
--------------------------------------------------------------------------------------
@@ -2912,7 +2912,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
(4 rows)
-- check ScalarArrayOp cases
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
QUERY PLAN
@@ -2921,7 +2921,7 @@ select * from stable_qual_pruning
One-Time Filter: false
(2 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
QUERY PLAN
@@ -2930,7 +2930,7 @@ select * from stable_qual_pruning
Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[]))
(2 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
QUERY PLAN
@@ -2941,7 +2941,7 @@ select * from stable_qual_pruning
Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP]))
(4 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
QUERY PLAN
@@ -2950,7 +2950,7 @@ select * from stable_qual_pruning
Subplans Removed: 3
(2 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
QUERY PLAN
@@ -2961,7 +2961,7 @@ select * from stable_qual_pruning
Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[]))
(4 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(null::timestamptz[]);
QUERY PLAN
@@ -2989,7 +2989,7 @@ create table mc3p1 partition of mc3p
create table mc3p2 partition of mc3p
for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from mc3p where a < 3 and abs(b) = 1;
QUERY PLAN
--------------------------------------------------------
@@ -3009,7 +3009,7 @@ select * from mc3p where a < 3 and abs(b) = 1;
--
prepare ps1 as
select * from mc3p where a = $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps1(1);
QUERY PLAN
--------------------------------------------------------
@@ -3024,7 +3024,7 @@ execute ps1(1);
deallocate ps1;
prepare ps2 as
select * from mc3p where a <= $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps2(1);
QUERY PLAN
--------------------------------------------------------
@@ -3046,7 +3046,7 @@ insert into boolvalues values('t'),('f');
create table boolp (a bool) partition by list (a);
create table boolp_t partition of boolp for values in('t');
create table boolp_f partition of boolp for values in('f');
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where value);
QUERY PLAN
-----------------------------------------------------------
@@ -3061,7 +3061,7 @@ select * from boolp where a = (select value from boolvalues where value);
Filter: (a = $0)
(9 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where not value);
QUERY PLAN
-----------------------------------------------------------
@@ -3090,7 +3090,7 @@ insert into ma_test select x,x from generate_series(0,29) t(x);
create index on ma_test (b);
analyze ma_test;
prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
-explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
+explain (analyze, regress) execute mt_q1(15);
QUERY PLAN
-----------------------------------------------------------------------------------------
Merge Append (actual rows=2 loops=1)
@@ -3111,7 +3111,7 @@ execute mt_q1(15);
25
(2 rows)
-explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
+explain (analyze, regress) execute mt_q1(25);
QUERY PLAN
-----------------------------------------------------------------------------------------
Merge Append (actual rows=1 loops=1)
@@ -3129,7 +3129,7 @@ execute mt_q1(25);
(1 row)
-- Ensure MergeAppend behaves correctly when no subplans match
-explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
+explain (analyze, regress) execute mt_q1(35);
QUERY PLAN
--------------------------------------
Merge Append (actual rows=0 loops=1)
@@ -3145,7 +3145,7 @@ execute mt_q1(35);
deallocate mt_q1;
prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
-- Ensure output list looks sane when the MergeAppend has no subplans.
-explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
+explain (analyze, verbose, regress) execute mt_q2 (35);
QUERY PLAN
--------------------------------------------
Limit (actual rows=0 loops=1)
@@ -3157,7 +3157,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35
deallocate mt_q2;
-- ensure initplan params properly prune partitions
-explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
+explain (analyze, regress) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Merge Append (actual rows=20 loops=1)
@@ -3607,7 +3607,7 @@ create table listp (a int, b int) partition by list (a);
create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2) partition by list(b);
create table listp2_10 partition of listp2 for values in (10);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select 2) and b <> 10;
QUERY PLAN
--------------------------------------------------
@@ -3738,7 +3738,7 @@ create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3);
create table rangep_100_to_200 partition of rangep for values from (100) to (200);
create index on rangep (a);
-- Ensure run-time pruning works on the nested Merge Append
-explain (analyze on, costs off, timing off, summary off)
+explain (analyze on, regress)
select * from rangep where b IN((select 1),(select 2)) order by a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index e3facf4a61..36b853213e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -568,7 +568,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, timing off, summary off, costs off)
+ explain (analyze, regress)
select * from
(select ten from tenk1 where ten < 100 order by ten) ss
right join (values (1),(2),(3)) v(x) on true
@@ -1043,7 +1043,7 @@ explain (costs off)
-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 1;
-EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
+EXPLAIN (analyze, regress) SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Gather (actual rows=10000 loops=1)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 0742626033..ec75bd2132 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1531,7 +1531,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, summary off, timing off, costs off)
+ explain (analyze, regress)
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
loop
ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index 13c3c360c2..bbb8b97e95 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -189,7 +189,7 @@ FETCH NEXT FROM c;
(1 row)
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, REGRESS)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
QUERY PLAN
---------------------------------------------------
@@ -205,7 +205,7 @@ FETCH NEXT FROM c;
(1 row)
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, REGRESS)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
QUERY PLAN
---------------------------------------------------
@@ -229,7 +229,7 @@ FETCH NEXT FROM c;
(0 rows)
-- should error out
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, REGRESS)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ERROR: cursor "c" is not positioned on a row
ROLLBACK;
diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql
index d8768a6b54..3d5231d3b7 100644
--- a/src/test/regress/sql/incremental_sort.sql
+++ b/src/test/regress/sql/incremental_sort.sql
@@ -26,7 +26,7 @@ declare
line text;
begin
for line in
- execute 'explain (analyze, costs off, summary off, timing off) ' || query
+ execute 'explain (analyze, regress) ' || query
loop
out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g');
return next;
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index 548cc3eee3..27c92bfd6c 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -11,7 +11,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
query)
loop
if hide_hitmiss = true then
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d70bd8610c..47d0bc25fc 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -375,8 +375,8 @@ set enable_indexonlyscan = off;
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+explain (analyze, regress) execute ab_q1 (2, 2, 3);
+explain (analyze, regress) execute ab_q1 (1, 2, 3);
deallocate ab_q1;
@@ -384,15 +384,15 @@ deallocate ab_q1;
prepare ab_q1 (int, int) as
select a from ab where a between $1 and $2 and b < 3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+explain (analyze, regress) execute ab_q1 (2, 2);
+explain (analyze, regress) execute ab_q1 (2, 4);
-- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at
-- different levels of partitioning.
prepare ab_q2 (int, int) as
select a from ab where a between $1 and $2 and b < (select 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+explain (analyze, regress) execute ab_q2 (2, 2);
-- As above, but swap the PARAM_EXEC Param to the first partition level
prepare ab_q3 (int, int) as
@@ -429,13 +429,13 @@ begin;
create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
-- Ensure pruning works using a stable function containing no Vars
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
+explain (analyze, regress) select * from list_part where a = list_part_fn(1);
-- Ensure pruning does not take place when the function has a Var parameter
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
+explain (analyze, regress) select * from list_part where a = list_part_fn(a);
-- Ensure pruning does not take place when the expression contains a Var.
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
+explain (analyze, regress) select * from list_part where a = list_part_fn(1) + a;
rollback;
@@ -458,7 +458,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
$1)
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
@@ -541,15 +541,15 @@ reset min_parallel_table_scan_size;
reset max_parallel_workers_per_gather;
-- Test run-time partition pruning with an initplan
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
-- Test run-time partition pruning with UNION ALL parents
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
-- A case containing a UNION ALL with a non-partitioned child.
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
@@ -569,7 +569,7 @@ union all
) ab where a = $1 and b = (select -10);
-- Ensure the xy_1 subplan is not pruned.
-explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
+explain (analyze, regress) execute ab_q6(1);
-- Ensure we see just the xy_1 row.
execute ab_q6(100);
@@ -586,14 +586,14 @@ deallocate ab_q6;
-- UPDATE on a partition subtree has been seen to have problems.
insert into ab values (1,2);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
table ab;
-- Test UPDATE where source relation has run-time pruning enabled
truncate ab;
insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
select tableoid::regclass, * from ab;
@@ -624,10 +624,10 @@ insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
set enable_hashjoin = off;
set enable_mergejoin = off;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -640,10 +640,10 @@ order by tbl1.col1, tprt.col1;
-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -657,7 +657,7 @@ order by tbl1.col1, tprt.col1;
-- Last partition
delete from tbl1;
insert into tbl1 values (4400);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -667,7 +667,7 @@ order by tbl1.col1, tprt.col1;
-- No matching partition
delete from tbl1;
insert into tbl1 values (10000);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -690,7 +690,7 @@ prepare part_abc_q1 (int, int, int) as
select * from part_abc where a = $1 and b = $2 and c = $3;
-- Single partition should be scanned.
-explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+explain (analyze, regress) execute part_abc_q1 (1, 2, 3);
deallocate part_abc_q1;
@@ -710,12 +710,12 @@ select * from listp where b = 1;
-- which match the given parameter.
prepare q1 (int,int) as select * from listp where b in ($1,$2);
-explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
+explain (analyze, regress) execute q1 (1,1);
-explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
+explain (analyze, regress) execute q1 (2,2);
-- Try with no matching partitions.
-explain (analyze, costs off, summary off, timing off) execute q1 (0,0);
+explain (analyze, regress) execute q1 (0,0);
deallocate q1;
@@ -723,13 +723,13 @@ deallocate q1;
prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
-- Both partitions allowed by IN clause, but one disallowed by <> clause
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
+explain (analyze, regress) execute q1 (1,2,2,0);
-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
+explain (analyze, regress) execute q1 (1,2,2,1);
-- Ensure Params that evaluate to NULL properly prune away all partitions
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select null::int);
drop table listp;
@@ -746,30 +746,30 @@ create table stable_qual_pruning3 partition of stable_qual_pruning
for values from ('3000-02-01') to ('3000-03-01');
-- comparison against a stable value requires run-time pruning
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < localtimestamp;
-- timestamp < timestamptz comparison is only stable, not immutable
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
-- check ScalarArrayOp cases
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(null::timestamptz[]);
@@ -789,7 +789,7 @@ create table mc3p2 partition of mc3p
for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from mc3p where a < 3 and abs(b) = 1;
--
@@ -799,12 +799,12 @@ select * from mc3p where a < 3 and abs(b) = 1;
--
prepare ps1 as
select * from mc3p where a = $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps1(1);
deallocate ps1;
prepare ps2 as
select * from mc3p where a <= $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps2(1);
deallocate ps2;
@@ -818,10 +818,10 @@ create table boolp (a bool) partition by list (a);
create table boolp_t partition of boolp for values in('t');
create table boolp_f partition of boolp for values in('f');
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where value);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where not value);
drop table boolp;
@@ -841,12 +841,12 @@ create index on ma_test (b);
analyze ma_test;
prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
-explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
+explain (analyze, regress) execute mt_q1(15);
execute mt_q1(15);
-explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
+explain (analyze, regress) execute mt_q1(25);
execute mt_q1(25);
-- Ensure MergeAppend behaves correctly when no subplans match
-explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
+explain (analyze, regress) execute mt_q1(35);
execute mt_q1(35);
deallocate mt_q1;
@@ -854,12 +854,12 @@ deallocate mt_q1;
prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
-- Ensure output list looks sane when the MergeAppend has no subplans.
-explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
+explain (analyze, verbose, regress) execute mt_q2 (35);
deallocate mt_q2;
-- ensure initplan params properly prune partitions
-explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
+explain (analyze, regress) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
reset enable_seqscan;
reset enable_sort;
@@ -1039,7 +1039,7 @@ create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2) partition by list(b);
create table listp2_10 partition of listp2 for values in (10);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select 2) and b <> 10;
--
@@ -1107,7 +1107,7 @@ create table rangep_100_to_200 partition of rangep for values from (100) to (200
create index on rangep (a);
-- Ensure run-time pruning works on the nested Merge Append
-explain (analyze on, costs off, timing off, summary off)
+explain (analyze on, regress)
select * from rangep where b IN((select 1),(select 2)) order by a;
reset enable_sort;
drop table rangep;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 24d688ffc8..a7b8557bed 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -227,7 +227,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, timing off, summary off, costs off)
+ explain (analyze, regress)
select * from
(select ten from tenk1 where ten < 100 order by ten) ss
right join (values (1),(2),(3)) v(x) on true
@@ -394,7 +394,7 @@ explain (costs off)
-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 1;
-EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
+EXPLAIN (analyze, regress) SELECT * FROM tenk1;
ROLLBACK TO SAVEPOINT settings;
-- provoke error in worker
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index e879999708..5803be5617 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -802,7 +802,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, summary off, timing off, costs off)
+ explain (analyze, regress)
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
loop
ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b6..7160327138 100644
--- a/src/test/regress/sql/tidscan.sql
+++ b/src/test/regress/sql/tidscan.sql
@@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan;
FETCH NEXT FROM c; -- skip one row
FETCH NEXT FROM c;
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, REGRESS)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
FETCH NEXT FROM c;
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, REGRESS)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
SELECT * FROM tidscan;
-- position cursor past any rows
FETCH NEXT FROM c;
-- should error out
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, REGRESS)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ROLLBACK;
--
2.17.0
--Q/AGl/UrDvkbRExF
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0003-Add-explain-MACHINE.patch"
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-02-14 14:39 Julien Rouhaud <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Julien Rouhaud @ 2025-02-14 14:39 UTC (permalink / raw)
To: Dmitry Dolgov <[email protected]>; +Cc: Sami Imseih <[email protected]>; Álvaro Herrera <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
On Fri, Feb 14, 2025 at 03:20:24PM +0100, Dmitry Dolgov wrote:
>
> Btw, there was another mistake in the last version introducing
> "$1 /*, ... */" format, the constant position has to be of course
> calculated as usual.
I'm not sure what you mean here, but just in case:
> +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 /*, ... */) AND data = $3 | 3
> + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
> +(2 rows)
There seems to be an off-by-1 error in parameter numbering when merging them.
Note that the query text as-is can still be successfully be used in an EXPLAIN
(GENERIC_PLAN), but it might cause problem to third party tools that try to do
something smarter about the parameters.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-02-14 14:56 Dmitry Dolgov <[email protected]>
parent: Julien Rouhaud <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Dmitry Dolgov @ 2025-02-14 14:56 UTC (permalink / raw)
To: Julien Rouhaud <[email protected]>; +Cc: Sami Imseih <[email protected]>; Álvaro Herrera <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
> On Fri, Feb 14, 2025 at 10:39:45PM GMT, Julien Rouhaud wrote:
> There seems to be an off-by-1 error in parameter numbering when merging them.
There are indeed three constants, but the second is not visible in the
query text. Maybe makes sense to adjust the number in this case, let me
try.
> Note that the query text as-is can still be successfully be used in an EXPLAIN
> (GENERIC_PLAN), but it might cause problem to third party tools that try to do
> something smarter about the parameters.
Since the normalized query will be a valid one now, I hope that such
cases will be rare. On top of that it always will be option to not
enable constants squashing and avoid any troubles. Or do you have some
particular scenario of what might be problematic?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-02-14 15:12 Julien Rouhaud <[email protected]>
parent: Dmitry Dolgov <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Julien Rouhaud @ 2025-02-14 15:12 UTC (permalink / raw)
To: Dmitry Dolgov <[email protected]>; +Cc: Sami Imseih <[email protected]>; Álvaro Herrera <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
On Fri, Feb 14, 2025 at 03:56:32PM +0100, Dmitry Dolgov wrote:
> > On Fri, Feb 14, 2025 at 10:39:45PM GMT, Julien Rouhaud wrote:
> > There seems to be an off-by-1 error in parameter numbering when merging them.
>
> There are indeed three constants, but the second is not visible in the
> query text. Maybe makes sense to adjust the number in this case, let me
> try.
Thanks!
>
> > Note that the query text as-is can still be successfully be used in an EXPLAIN
> > (GENERIC_PLAN), but it might cause problem to third party tools that try to do
> > something smarter about the parameters.
>
> Since the normalized query will be a valid one now, I hope that such
> cases will be rare. On top of that it always will be option to not
> enable constants squashing and avoid any troubles.
It might not always be an option. I have seen application that create
thousands of duplicated queryids just because they have a non deterministic
amount of parameters they put in such IN () clauses. If that leads to a total
number of unique (dbid, userid, queryid, toplevel) too big for a reasonable
pg_stat_statements.max, they the only choice might be to enable the new merging
parameter or deactivating pg_stat_statements.
> Or do you have some
> particular scenario of what might be problematic?
I don't have a very specific scenario. It's mostly for things like trying to
"un-jumble" a query, you may need to loop through the parameters and a missing
number could be problematic. But since the overall number of parameters might
change from execution to execution that's probably the least of the problems to
deal with with this merging feature.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-02-14 15:40 Dmitry Dolgov <[email protected]>
parent: Julien Rouhaud <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Dmitry Dolgov @ 2025-02-14 15:40 UTC (permalink / raw)
To: Julien Rouhaud <[email protected]>; +Cc: Sami Imseih <[email protected]>; Álvaro Herrera <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
> On Fri, Feb 14, 2025 at 11:12:25PM GMT, Julien Rouhaud wrote:
> > > There seems to be an off-by-1 error in parameter numbering when merging them.
> >
> > There are indeed three constants, but the second is not visible in the
> > query text. Maybe makes sense to adjust the number in this case, let me
> > try.
This should do it. The last patch for today, otherwise I'll probably add
more bugs than features :)
From 122cac8eda36af877ac471322f681aa6ff46d61b Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email protected]>
Date: Tue, 3 Dec 2024 14:55:45 +0100
Subject: [PATCH v27] 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 first/last
elements to the jumble hash. Allow to enable this behavior via the new
pg_stat_statements parameter query_id_squash_values with the default value off.
Reviewed-by: Zhihong Yu, Sergey Dudoladov, Robert Haas, Tom Lane,
Michael Paquier, Sergei Kornilov, Alvaro Herrera, David Geier, Sutou Kouhei,
Sami Imseih, Julien Rouhaud
Tested-by: Chengxi Sun, Yasuo Honda
---
contrib/pg_stat_statements/Makefile | 2 +-
.../pg_stat_statements/expected/merging.out | 465 ++++++++++++++++++
contrib/pg_stat_statements/meson.build | 1 +
.../pg_stat_statements/pg_stat_statements.c | 63 ++-
contrib/pg_stat_statements/sql/merging.sql | 180 +++++++
doc/src/sgml/config.sgml | 28 ++
doc/src/sgml/pgstatstatements.sgml | 28 +-
src/backend/nodes/gen_node_support.pl | 21 +-
src/backend/nodes/queryjumblefuncs.c | 166 ++++++-
src/backend/postmaster/launch_backend.c | 3 +
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/include/nodes/nodes.h | 3 +
src/include/nodes/primnodes.h | 2 +-
src/include/nodes/queryjumble.h | 8 +-
15 files changed, 956 insertions(+), 26 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 241c02587b..eef8d69cc4 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -20,7 +20,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 entry_timestamp privileges extended \
- parallel cleanup oldextversions
+ parallel 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..ecf0a66a6b
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/merging.out
@@ -0,0 +1,465 @@
+--
+-- 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() IS NOT NULL AS t;
+ t
+---
+ t
+(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() IS NOT NULL AS t | 1
+(4 rows)
+
+-- Normal scenario, too many simple constants for an IN query
+SET query_id_squash_values = on;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(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) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 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 /*, ... */) | 4
+ SELECT * FROM test_merge WHERE id IN ($1) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 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() IS NOT NULL AS t;
+ t
+---
+ t
+(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 /*, ... */) AND data = $2 | 3
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Multiple merged intervals
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
+ AND data 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)
+ AND data 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)
+ AND data 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 /*, ... */)+| 3
+ AND data IN ($2 /*, ... */) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- No constants simplification for OpExpr
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+-- In the following two queries the operator expressions (+) and (@) have
+-- different oppno, and will be given different query_id if merged, even though
+-- the normalized query will be the same
+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 * FROM test_merge WHERE id IN
+ (@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-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
+ ($1 + $2, $3 + $4, $5 + $6, $7 + $8, $9 + $10, $11 + $12, $13 + $14, $15 + $16, $17 + $18) |
+ SELECT * FROM test_merge WHERE id IN +| 1
+ (@ $1, @ $2, @ $3, @ $4, @ $5, @ $6, @ $7, @ $8, @ $9) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
+
+-- FuncExpr
+-- Verify multiple type representation end up with the same query_id
+CREATE TABLE test_float (data float);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT data FROM test_float WHERE data IN (1, 2);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN (1, '2');
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN ('1', 2);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN ('1', '2');
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN (1.0, 1.0);
+ data
+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+-----------------------------------------------------------+-------
+ SELECT data FROM test_float WHERE data IN ($1 /*, ... */) | 5
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Numeric type, implicit cast is merged
+CREATE TABLE test_merge_numeric (id int, data numeric(5, 2));
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_numeric WHERE data 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 data IN ($1 /*, ... */) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Bigint, implicit cast is merged
+CREATE TABLE test_merge_bigint (id int, data bigint);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_bigint WHERE data 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_bigint WHERE data IN ($1 /*, ... */) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Bigint, explicit cast is not merged
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_bigint WHERE data IN
+ (1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint,
+ 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_bigint WHERE data IN +| 1
+ ($1::bigint, $2::bigint, $3::bigint, $4::bigint, $5::bigint, $6::bigint,+|
+ $7::bigint, $8::bigint, $9::bigint, $10::bigint, $11::bigint) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Bigint, long tokens with parenthesis
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_bigint WHERE id IN
+ (abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700),
+ abs(800), abs(900), abs(1000), ((abs(1100))));
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+-------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_bigint WHERE id IN +| 1
+ (abs($1), abs($2), abs($3), abs($4), abs($5), abs($6), abs($7),+|
+ abs($8), abs($9), abs($10), ((abs($11)))) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- CoerceViaIO, SubLink instead of a Const
+CREATE TABLE test_merge_jsonb (id int, data jsonb);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_jsonb WHERE data IN
+ ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb,
+ (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb,
+ (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb,
+ (SELECT '"10"')::jsonb);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_jsonb WHERE data IN +| 1
+ ((SELECT $1)::jsonb, (SELECT $2)::jsonb, (SELECT $3)::jsonb,+|
+ (SELECT $4)::jsonb, (SELECT $5)::jsonb, (SELECT $6)::jsonb,+|
+ (SELECT $7)::jsonb, (SELECT $8)::jsonb, (SELECT $9)::jsonb,+|
+ (SELECT $10)::jsonb) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- CoerceViaIO
+-- Create some dummy type to force CoerceViaIO
+CREATE TYPE casttesttype;
+CREATE FUNCTION casttesttype_in(cstring)
+ RETURNS casttesttype
+ AS 'textin'
+ LANGUAGE internal STRICT IMMUTABLE;
+NOTICE: return type casttesttype is only a shell
+CREATE FUNCTION casttesttype_out(casttesttype)
+ RETURNS cstring
+ AS 'textout'
+ LANGUAGE internal STRICT IMMUTABLE;
+NOTICE: argument type casttesttype is only a shell
+LINE 1: CREATE FUNCTION casttesttype_out(casttesttype)
+ ^
+CREATE TYPE casttesttype (
+ internallength = variable,
+ input = casttesttype_in,
+ output = casttesttype_out,
+ alignment = int4
+);
+CREATE CAST (int4 AS casttesttype) WITH INOUT;
+CREATE FUNCTION casttesttype_eq(casttesttype, casttesttype)
+returns boolean language sql immutable as $$
+ SELECT true
+$$;
+CREATE OPERATOR = (
+ leftarg = casttesttype,
+ rightarg = casttesttype,
+ procedure = casttesttype_eq,
+ commutator = =);
+CREATE TABLE test_merge_cast (id int, data casttesttype);
+-- Use the introduced type to construct a list of CoerceViaIO around Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_cast WHERE data IN
+ (1::int4::casttesttype, 2::int4::casttesttype, 3::int4::casttesttype,
+ 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype,
+ 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype,
+ 10::int4::casttesttype, 11::int4::casttesttype);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT * FROM test_merge_cast WHERE data IN +| 1
+ ($1 /*, ... */::int4::casttesttype) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Some casting expression are simplified to Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_jsonb WHERE data IN
+ (('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
+ ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb,
+ ( '"9"')::jsonb, ( '"10"')::jsonb);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT * FROM test_merge_jsonb WHERE data IN +| 1
+ (($1 /*, ... */)::jsonb) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- RelabelType
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
+ 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 /*, ... */::oid) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Test constants evaluation in a CTE, which was causing issues in the past
+WITH cte AS (
+ SELECT 'const' as const FROM test_merge
+)
+SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
+FROM cte;
+ result
+--------
+(0 rows)
+
+-- Simple array would be merged as well
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
+ array
+------------------------
+ {1,2,3,4,5,6,7,8,9,10}
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT ARRAY[$1 /*, ... */] | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+RESET query_id_squash_values;
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 4446af58c5..8a96aff625 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -56,6 +56,7 @@ tests += {
'parallel',
'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 bebf8134eb..dcc65fc1a6 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -295,7 +295,6 @@ static bool pgss_track_planning = false; /* whether to track planning
* duration */
static bool pgss_save = true; /* whether to save stats across shutdown */
-
#define pgss_enabled(level) \
(!IsParallelWorker() && \
(pgss_track == PGSS_TRACK_ALL || \
@@ -2809,6 +2808,13 @@ 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 merged_interval = false; /* Currently processed constants
+ belong to a merged constants
+ interval. */
+ int skipped_constants = 0; /* To adjust positions of visible
+ constants in the presense of a merged
+ constanst interval. */
+
/*
* Get constants' lengths (core system only gives us locations). Note
@@ -2832,7 +2838,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;
@@ -2847,13 +2852,57 @@ 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 */
+ if (!jstate->clocations[i].merged)
+ {
+ 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 -
+ skipped_constants);
+
+ /* In case previous constants were merged away, stop doing that */
+ merged_interval = false;
+ }
+ else if (!merged_interval)
+ {
+ /*
+ * We are not inside a merged interval yet, which means it is the
+ * the first merged constant.
+ *
+ * A merged constants interval must be represented via two
+ * constants with the merged flag. Currently we are at the first,
+ * verify there is another one.
+ */
+ Assert(i + 1 < jstate->clocations_count);
+ Assert(jstate->clocations[i + 1].merged);
+
+ memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
+ n_quer_loc += len_to_wrt;
+
+ /* Remember to skip until a non merged constant appears */
+ merged_interval = true;
+
+ /* Mark the interval in the normalized query */
+ n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d /*, ... */",
+ i + 1 + jstate->highest_extern_param_id -
+ skipped_constants);
+
+ skipped_constants++;
+ }
+ else
+ {
+ /*
+ * If it's a merged constant during a merged_interval, it has to
+ * close it.
+ */
+ merged_interval = false;
+ }
+ /* Otherwise the constant is merged away, move forward */
quer_loc = off + tok_len;
last_off = off;
last_tok_len = tok_len;
diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql
new file mode 100644
index 0000000000..282466f9b9
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/merging.sql
@@ -0,0 +1,180 @@
+--
+-- 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() IS NOT NULL AS t;
+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_squash_values = on;
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+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";
+
+-- More conditions in the query
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+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";
+
+-- Multiple merged intervals
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
+ AND data 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)
+ AND data 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)
+ AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- No constants simplification for OpExpr
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+-- In the following two queries the operator expressions (+) and (@) have
+-- different oppno, and will be given different query_id if merged, even though
+-- the normalized query will be the same
+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);
+SELECT * FROM test_merge WHERE id IN
+ (@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9');
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- FuncExpr
+
+-- Verify multiple type representation end up with the same query_id
+CREATE TABLE test_float (data float);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT data FROM test_float WHERE data IN (1, 2);
+SELECT data FROM test_float WHERE data IN (1, '2');
+SELECT data FROM test_float WHERE data IN ('1', 2);
+SELECT data FROM test_float WHERE data IN ('1', '2');
+SELECT data FROM test_float WHERE data IN (1.0, 1.0);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Numeric type, implicit cast is merged
+CREATE TABLE test_merge_numeric (id int, data numeric(5, 2));
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_numeric WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Bigint, implicit cast is merged
+CREATE TABLE test_merge_bigint (id int, data bigint);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_bigint WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Bigint, explicit cast is not merged
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_bigint WHERE data IN
+ (1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint,
+ 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Bigint, long tokens with parenthesis
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_bigint WHERE id IN
+ (abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700),
+ abs(800), abs(900), abs(1000), ((abs(1100))));
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CoerceViaIO, SubLink instead of a Const
+CREATE TABLE test_merge_jsonb (id int, data jsonb);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_jsonb WHERE data IN
+ ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb,
+ (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb,
+ (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb,
+ (SELECT '"10"')::jsonb);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CoerceViaIO
+
+-- Create some dummy type to force CoerceViaIO
+CREATE TYPE casttesttype;
+
+CREATE FUNCTION casttesttype_in(cstring)
+ RETURNS casttesttype
+ AS 'textin'
+ LANGUAGE internal STRICT IMMUTABLE;
+
+CREATE FUNCTION casttesttype_out(casttesttype)
+ RETURNS cstring
+ AS 'textout'
+ LANGUAGE internal STRICT IMMUTABLE;
+
+CREATE TYPE casttesttype (
+ internallength = variable,
+ input = casttesttype_in,
+ output = casttesttype_out,
+ alignment = int4
+);
+
+CREATE CAST (int4 AS casttesttype) WITH INOUT;
+
+CREATE FUNCTION casttesttype_eq(casttesttype, casttesttype)
+returns boolean language sql immutable as $$
+ SELECT true
+$$;
+
+CREATE OPERATOR = (
+ leftarg = casttesttype,
+ rightarg = casttesttype,
+ procedure = casttesttype_eq,
+ commutator = =);
+
+CREATE TABLE test_merge_cast (id int, data casttesttype);
+
+-- Use the introduced type to construct a list of CoerceViaIO around Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_cast WHERE data IN
+ (1::int4::casttesttype, 2::int4::casttesttype, 3::int4::casttesttype,
+ 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype,
+ 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype,
+ 10::int4::casttesttype, 11::int4::casttesttype);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Some casting expression are simplified to Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_jsonb WHERE data IN
+ (('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
+ ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb,
+ ( '"9"')::jsonb, ( '"10"')::jsonb);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- RelabelType
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Test constants evaluation in a CTE, which was causing issues in the past
+WITH cte AS (
+ SELECT 'const' as const FROM test_merge
+)
+SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
+FROM cte;
+
+-- Simple array would be merged as well
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+RESET query_id_squash_values;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5e4f201e09..4ef10e9cbd 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8436,6 +8436,34 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-query-id-squash-values" xreflabel="query_id_squash_values">
+ <term><varname>query_id_squash_values</varname> (<type>bool</type>)
+ <indexterm>
+ <primary><varname>query_id_squash_values</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies how an array of constants (e.g. for an <literal>IN</literal>
+ 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 length.
+
+ 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>'($1 /*, ... */)'</literal>.
+
+ The parameter could be used to reduce amount of repeating data stored
+ via <xref linkend="pgstatstatements"/>. Only constants are affected,
+ bind parameters cannot benefit from this functionality. The default
+ value is <literal>off</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-log-statement-stats">
<term><varname>log_statement_stats</varname> (<type>boolean</type>)
<indexterm>
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 501b468e9a..0ef9785854 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -621,11 +621,28 @@
<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>query_id_squash_values</varname> is enabled and the only difference
+ between queries is the length of an array with constants they contain:
+
+<screen>
+=# SET query_id_squash_values = 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 ($1 /*, ... */)
+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>
@@ -956,6 +973,7 @@
</para>
</listitem>
</varlistentry>
+
</variablelist>
<para>
diff --git a/src/backend/nodes/gen_node_support.pl b/src/backend/nodes/gen_node_support.pl
index 1a657f7e0a..c421664879 100644
--- a/src/backend/nodes/gen_node_support.pl
+++ b/src/backend/nodes/gen_node_support.pl
@@ -476,6 +476,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
@@ -1283,6 +1284,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)
@@ -1295,21 +1297,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 'ParseLoc')
{
# 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 b103a28193..c3c5edff32 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -32,9 +32,13 @@
*/
#include "postgres.h"
+#include "access/transam.h"
+#include "catalog/pg_proc.h"
#include "common/hashfn.h"
#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
#include "nodes/queryjumble.h"
+#include "utils/lsyscache.h"
#include "parser/scansup.h"
#define JUMBLE_SIZE 1024 /* query serialization buffer size */
@@ -42,6 +46,9 @@
/* GUC parameters */
int compute_query_id = COMPUTE_QUERY_ID_AUTO;
+/* Whether to merge constants in a list when computing query_id */
+bool query_id_squash_values = false;
+
/*
* True when compute_query_id is ON or AUTO, and a module requests them.
*
@@ -53,8 +60,10 @@ 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, 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 _jumbleVariableSetStmt(JumbleState *jstate, Node *node);
@@ -198,11 +207,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)
@@ -218,15 +231,127 @@ 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 few simple cases where we can deduce that the expression is a
+ * constant:
+ *
+ * - Simplify the expression, if it's wrapped into RelabelType and CoerceViaIO.
+ * - If it's a FuncExpr, check if the function is an immutable builtin
+ * function doing implicit cast with constant arguments.
+ * - Otherwise test if the expression is a simple Const.
+ *
+ * We could also handle some simple OpExpr here as well, but since such queries
+ * will also have opno jumbled, this might lead to a confusing situation where
+ * two different queries end up with the same normalized query but different
+ * query_id.
+ *
+ * The argument known_immutable_funcs contains known function OIDs that were
+ * already proven to be immutable. If the expression to verify is a FuncExpr,
+ * we first check this list, and only if not found, test the function
+ * volatility and store the result back. Since most of the time constants
+ * merging will be dealing with same type of expressions, this avoids
+ * performing func_volatile over and over for the same functions.
+ *
+ * Note that we intentionally do not recurse on the function arguments and only
+ * test them for being Const expression for simplicity.
+ */
+static bool
+IsMergeableConst(Node *element, List **known_immutable_funcs)
+{
+ if (IsA(element, RelabelType))
+ element = (Node *) ((RelabelType *) element)->arg;
+
+ if (IsA(element, CoerceViaIO))
+ element = (Node *) ((CoerceViaIO *) element)->arg;
+
+ if(IsA(element, FuncExpr))
+ {
+ FuncExpr *func = (FuncExpr *) element;
+ ListCell *temp;
+
+ if (func->funcid > FirstGenbkiObjectId)
+ return false;
+
+ if (func->funcformat != COERCE_IMPLICIT_CAST)
+ return false;
+
+ if (!list_member_oid(*known_immutable_funcs, func->funcid))
+ {
+ /* Not found in the cache, verify and add if needed */
+ if(func_volatile(func->funcid) != PROVOLATILE_IMMUTABLE)
+ return false;
+
+ *known_immutable_funcs = lappend_oid(*known_immutable_funcs,
+ func->funcid);
+ }
+
+ foreach(temp, func->args)
+ {
+ Node *arg = lfirst(temp);
+
+ if (!IsA(arg, Const))
+ return false;
+ }
+
+ return true;
+ }
+
+ if (!IsA(element, Const))
+ return false;
+
+ return true;
+}
+
+/*
+ * Verify if the provided list 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, Node **firstExpr, Node **lastExpr)
+{
+ ListCell *temp;
+
+ /* To keep track of immutable functions in elements */
+ List *immutable_funcs = NIL;
+
+ /* A mergeable list needs to contain at least two elements */
+ if (elements == NIL || list_length(elements) < 2)
+ return false;
+
+ if (!query_id_squash_values)
+ {
+ /* Merging is disabled, process everything one by one */
+ return false;
+ }
+
+ foreach(temp, elements)
+ {
+ if (!IsMergeableConst(lfirst(temp), &immutable_funcs))
+ return false;
+ }
+ *firstExpr = linitial(elements);
+ *lastExpr = llast(elements);
+
+ return true;
+}
+
#define JUMBLE_NODE(item) \
_jumbleNode(jstate, (Node *) expr->item)
-#define JUMBLE_LOCATION(location) \
- RecordConstLocation(jstate, expr->location)
+#define JUMBLE_ELEMENTS(list) \
+ _jumbleElements(jstate, (List *) expr->list)
+#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) \
@@ -239,6 +364,33 @@ do { \
#include "queryjumblefuncs.funcs.c"
+static void
+_jumbleElements(JumbleState *jstate, List *elements)
+{
+ Node *first, *last;
+ if (IsMergeableConstList(elements, &first, &last))
+ {
+ /*
+ * Both first and last constants have to be recorded. The first one
+ * will indicate the merged interval, the last one will tell us the
+ * length of the interval within the query text.
+ *
+ * Note that for the last exression we actually need not the expression
+ * location (which is the leftmost expression), but where it ends. For
+ * the limited set of supported cases now (implicit coerce via
+ * FuncExpr, Const) it's fine to use exprLocation, but if more complex
+ * composite expressions will be supported, e.g. OpExpr or FuncExpr as
+ * an explicit call, the rightmost expression will be needed.
+ */
+ RecordConstLocation(jstate, exprLocation(first), true);
+ RecordConstLocation(jstate, exprLocation(last), true);
+ }
+ else
+ {
+ _jumbleNode(jstate, (Node *) elements);
+ }
+}
+
static void
_jumbleNode(JumbleState *jstate, Node *node)
{
@@ -375,5 +527,5 @@ _jumbleVariableSetStmt(JumbleState *jstate, Node *node)
if (expr->jumble_args)
JUMBLE_NODE(args);
JUMBLE_FIELD(is_local);
- JUMBLE_LOCATION(location);
+ JUMBLE_LOCATION(location, false);
}
diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c
index a97a1eda6d..052b700390 100644
--- a/src/backend/postmaster/launch_backend.c
+++ b/src/backend/postmaster/launch_backend.c
@@ -115,6 +115,7 @@ typedef struct
bool redirection_done;
bool IsBinaryUpgrade;
bool query_id_enabled;
+ bool query_id_squash_values;
int max_safe_fds;
int MaxBackends;
int num_pmchild_slots;
@@ -744,6 +745,7 @@ save_backend_variables(BackendParameters *param,
param->redirection_done = redirection_done;
param->IsBinaryUpgrade = IsBinaryUpgrade;
param->query_id_enabled = query_id_enabled;
+ param->query_id_squash_values = query_id_squash_values;
param->max_safe_fds = max_safe_fds;
param->MaxBackends = MaxBackends;
@@ -1004,6 +1006,7 @@ restore_backend_variables(BackendParameters *param)
redirection_done = param->redirection_done;
IsBinaryUpgrade = param->IsBinaryUpgrade;
query_id_enabled = param->query_id_enabled;
+ query_id_squash_values = param->query_id_squash_values;
max_safe_fds = param->max_safe_fds;
MaxBackends = param->MaxBackends;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 226af43fe2..eca30ba1a8 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2106,6 +2106,16 @@ struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
+ {
+ {"query_id_squash_values", PGC_USERSET, STATS_MONITORING,
+ gettext_noop("Allows to merge constants in a list when computing "
+ "query_id."),
+ },
+ &query_id_squash_values,
+ 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 d472987ed4..15939394bc 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -645,12 +645,12 @@
# - Monitoring -
#compute_query_id = auto
+#query_id_squash_values = off
#log_statement_stats = off
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
-
#------------------------------------------------------------------------------
# VACUUMING
#------------------------------------------------------------------------------
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 580238bfab..5656302544 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -108,6 +108,9 @@ typedef enum NodeTag
* - query_jumble_location: Mark the field as a location to track. This is
* only allowed for integer fields that include "location" in their name.
*
+ * - query_jumble_merge: Allow to merge the field values for the query
+ * jumbling.
+ *
* - read_as(VALUE): In nodeRead(), replace the field's value with VALUE.
*
* - read_write_ignore: Ignore the field for read/write. This is only allowed
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 839e71d52f..89587d4c10 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1394,7 +1394,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 50eb956658..d2f1c1e310 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,12 @@ 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 PGDLLIMPORT bool query_id_enabled;
+extern PGDLLIMPORT bool query_id_squash_values;
/*
* Returns whether query identifier computation has been enabled, either
base-commit: 773c51dd39ada5f107a3656377a9611ff89132f1
--
2.45.1
Attachments:
[text/plain] v27-0001-Prevent-jumbling-of-every-element-in-ArrayExpr.patch (46.9K, ../../mbeuidwietzdlsekb3ihsqqpy34vm4q4jof5ycmqa6s3gvyv4t@5mzg7op5yx7o/2-v27-0001-Prevent-jumbling-of-every-element-in-ArrayExpr.patch)
download | inline diff:
From 122cac8eda36af877ac471322f681aa6ff46d61b Mon Sep 17 00:00:00 2001
From: Dmitrii Dolgov <[email protected]>
Date: Tue, 3 Dec 2024 14:55:45 +0100
Subject: [PATCH v27] 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 first/last
elements to the jumble hash. Allow to enable this behavior via the new
pg_stat_statements parameter query_id_squash_values with the default value off.
Reviewed-by: Zhihong Yu, Sergey Dudoladov, Robert Haas, Tom Lane,
Michael Paquier, Sergei Kornilov, Alvaro Herrera, David Geier, Sutou Kouhei,
Sami Imseih, Julien Rouhaud
Tested-by: Chengxi Sun, Yasuo Honda
---
contrib/pg_stat_statements/Makefile | 2 +-
.../pg_stat_statements/expected/merging.out | 465 ++++++++++++++++++
contrib/pg_stat_statements/meson.build | 1 +
.../pg_stat_statements/pg_stat_statements.c | 63 ++-
contrib/pg_stat_statements/sql/merging.sql | 180 +++++++
doc/src/sgml/config.sgml | 28 ++
doc/src/sgml/pgstatstatements.sgml | 28 +-
src/backend/nodes/gen_node_support.pl | 21 +-
src/backend/nodes/queryjumblefuncs.c | 166 ++++++-
src/backend/postmaster/launch_backend.c | 3 +
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/include/nodes/nodes.h | 3 +
src/include/nodes/primnodes.h | 2 +-
src/include/nodes/queryjumble.h | 8 +-
15 files changed, 956 insertions(+), 26 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 241c02587b..eef8d69cc4 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -20,7 +20,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 entry_timestamp privileges extended \
- parallel cleanup oldextversions
+ parallel 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..ecf0a66a6b
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/merging.out
@@ -0,0 +1,465 @@
+--
+-- 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() IS NOT NULL AS t;
+ t
+---
+ t
+(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() IS NOT NULL AS t | 1
+(4 rows)
+
+-- Normal scenario, too many simple constants for an IN query
+SET query_id_squash_values = on;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(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) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 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 /*, ... */) | 4
+ SELECT * FROM test_merge WHERE id IN ($1) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 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() IS NOT NULL AS t;
+ t
+---
+ t
+(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 /*, ... */) AND data = $2 | 3
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Multiple merged intervals
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
+ AND data 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)
+ AND data 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)
+ AND data 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 /*, ... */)+| 3
+ AND data IN ($2 /*, ... */) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- No constants simplification for OpExpr
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+-- In the following two queries the operator expressions (+) and (@) have
+-- different oppno, and will be given different query_id if merged, even though
+-- the normalized query will be the same
+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 * FROM test_merge WHERE id IN
+ (@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-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
+ ($1 + $2, $3 + $4, $5 + $6, $7 + $8, $9 + $10, $11 + $12, $13 + $14, $15 + $16, $17 + $18) |
+ SELECT * FROM test_merge WHERE id IN +| 1
+ (@ $1, @ $2, @ $3, @ $4, @ $5, @ $6, @ $7, @ $8, @ $9) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
+
+-- FuncExpr
+-- Verify multiple type representation end up with the same query_id
+CREATE TABLE test_float (data float);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT data FROM test_float WHERE data IN (1, 2);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN (1, '2');
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN ('1', 2);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN ('1', '2');
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data IN (1.0, 1.0);
+ data
+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+-----------------------------------------------------------+-------
+ SELECT data FROM test_float WHERE data IN ($1 /*, ... */) | 5
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Numeric type, implicit cast is merged
+CREATE TABLE test_merge_numeric (id int, data numeric(5, 2));
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_numeric WHERE data 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 data IN ($1 /*, ... */) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Bigint, implicit cast is merged
+CREATE TABLE test_merge_bigint (id int, data bigint);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_bigint WHERE data 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_bigint WHERE data IN ($1 /*, ... */) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Bigint, explicit cast is not merged
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_bigint WHERE data IN
+ (1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint,
+ 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_bigint WHERE data IN +| 1
+ ($1::bigint, $2::bigint, $3::bigint, $4::bigint, $5::bigint, $6::bigint,+|
+ $7::bigint, $8::bigint, $9::bigint, $10::bigint, $11::bigint) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Bigint, long tokens with parenthesis
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_bigint WHERE id IN
+ (abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700),
+ abs(800), abs(900), abs(1000), ((abs(1100))));
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+-------------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_bigint WHERE id IN +| 1
+ (abs($1), abs($2), abs($3), abs($4), abs($5), abs($6), abs($7),+|
+ abs($8), abs($9), abs($10), ((abs($11)))) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- CoerceViaIO, SubLink instead of a Const
+CREATE TABLE test_merge_jsonb (id int, data jsonb);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_jsonb WHERE data IN
+ ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb,
+ (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb,
+ (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb,
+ (SELECT '"10"')::jsonb);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------------------------+-------
+ SELECT * FROM test_merge_jsonb WHERE data IN +| 1
+ ((SELECT $1)::jsonb, (SELECT $2)::jsonb, (SELECT $3)::jsonb,+|
+ (SELECT $4)::jsonb, (SELECT $5)::jsonb, (SELECT $6)::jsonb,+|
+ (SELECT $7)::jsonb, (SELECT $8)::jsonb, (SELECT $9)::jsonb,+|
+ (SELECT $10)::jsonb) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- CoerceViaIO
+-- Create some dummy type to force CoerceViaIO
+CREATE TYPE casttesttype;
+CREATE FUNCTION casttesttype_in(cstring)
+ RETURNS casttesttype
+ AS 'textin'
+ LANGUAGE internal STRICT IMMUTABLE;
+NOTICE: return type casttesttype is only a shell
+CREATE FUNCTION casttesttype_out(casttesttype)
+ RETURNS cstring
+ AS 'textout'
+ LANGUAGE internal STRICT IMMUTABLE;
+NOTICE: argument type casttesttype is only a shell
+LINE 1: CREATE FUNCTION casttesttype_out(casttesttype)
+ ^
+CREATE TYPE casttesttype (
+ internallength = variable,
+ input = casttesttype_in,
+ output = casttesttype_out,
+ alignment = int4
+);
+CREATE CAST (int4 AS casttesttype) WITH INOUT;
+CREATE FUNCTION casttesttype_eq(casttesttype, casttesttype)
+returns boolean language sql immutable as $$
+ SELECT true
+$$;
+CREATE OPERATOR = (
+ leftarg = casttesttype,
+ rightarg = casttesttype,
+ procedure = casttesttype_eq,
+ commutator = =);
+CREATE TABLE test_merge_cast (id int, data casttesttype);
+-- Use the introduced type to construct a list of CoerceViaIO around Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_cast WHERE data IN
+ (1::int4::casttesttype, 2::int4::casttesttype, 3::int4::casttesttype,
+ 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype,
+ 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype,
+ 10::int4::casttesttype, 11::int4::casttesttype);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT * FROM test_merge_cast WHERE data IN +| 1
+ ($1 /*, ... */::int4::casttesttype) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Some casting expression are simplified to Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge_jsonb WHERE data IN
+ (('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
+ ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb,
+ ( '"9"')::jsonb, ( '"10"')::jsonb);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT * FROM test_merge_jsonb WHERE data IN +| 1
+ (($1 /*, ... */)::jsonb) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- RelabelType
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_merge WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
+ 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 /*, ... */::oid) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- Test constants evaluation in a CTE, which was causing issues in the past
+WITH cte AS (
+ SELECT 'const' as const FROM test_merge
+)
+SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
+FROM cte;
+ result
+--------
+(0 rows)
+
+-- Simple array would be merged as well
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
+ array
+------------------------
+ {1,2,3,4,5,6,7,8,9,10}
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT ARRAY[$1 /*, ... */] | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+RESET query_id_squash_values;
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 4446af58c5..8a96aff625 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -56,6 +56,7 @@ tests += {
'parallel',
'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 bebf8134eb..dcc65fc1a6 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -295,7 +295,6 @@ static bool pgss_track_planning = false; /* whether to track planning
* duration */
static bool pgss_save = true; /* whether to save stats across shutdown */
-
#define pgss_enabled(level) \
(!IsParallelWorker() && \
(pgss_track == PGSS_TRACK_ALL || \
@@ -2809,6 +2808,13 @@ 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 merged_interval = false; /* Currently processed constants
+ belong to a merged constants
+ interval. */
+ int skipped_constants = 0; /* To adjust positions of visible
+ constants in the presense of a merged
+ constanst interval. */
+
/*
* Get constants' lengths (core system only gives us locations). Note
@@ -2832,7 +2838,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;
@@ -2847,13 +2852,57 @@ 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 */
+ if (!jstate->clocations[i].merged)
+ {
+ 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 -
+ skipped_constants);
+
+ /* In case previous constants were merged away, stop doing that */
+ merged_interval = false;
+ }
+ else if (!merged_interval)
+ {
+ /*
+ * We are not inside a merged interval yet, which means it is the
+ * the first merged constant.
+ *
+ * A merged constants interval must be represented via two
+ * constants with the merged flag. Currently we are at the first,
+ * verify there is another one.
+ */
+ Assert(i + 1 < jstate->clocations_count);
+ Assert(jstate->clocations[i + 1].merged);
+
+ memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
+ n_quer_loc += len_to_wrt;
+
+ /* Remember to skip until a non merged constant appears */
+ merged_interval = true;
+
+ /* Mark the interval in the normalized query */
+ n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d /*, ... */",
+ i + 1 + jstate->highest_extern_param_id -
+ skipped_constants);
+
+ skipped_constants++;
+ }
+ else
+ {
+ /*
+ * If it's a merged constant during a merged_interval, it has to
+ * close it.
+ */
+ merged_interval = false;
+ }
+ /* Otherwise the constant is merged away, move forward */
quer_loc = off + tok_len;
last_off = off;
last_tok_len = tok_len;
diff --git a/contrib/pg_stat_statements/sql/merging.sql b/contrib/pg_stat_statements/sql/merging.sql
new file mode 100644
index 0000000000..282466f9b9
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/merging.sql
@@ -0,0 +1,180 @@
+--
+-- 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() IS NOT NULL AS t;
+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_squash_values = on;
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+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";
+
+-- More conditions in the query
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+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";
+
+-- Multiple merged intervals
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+SELECT * FROM test_merge WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
+ AND data 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)
+ AND data 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)
+ AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- No constants simplification for OpExpr
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+-- In the following two queries the operator expressions (+) and (@) have
+-- different oppno, and will be given different query_id if merged, even though
+-- the normalized query will be the same
+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);
+SELECT * FROM test_merge WHERE id IN
+ (@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9');
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- FuncExpr
+
+-- Verify multiple type representation end up with the same query_id
+CREATE TABLE test_float (data float);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT data FROM test_float WHERE data IN (1, 2);
+SELECT data FROM test_float WHERE data IN (1, '2');
+SELECT data FROM test_float WHERE data IN ('1', 2);
+SELECT data FROM test_float WHERE data IN ('1', '2');
+SELECT data FROM test_float WHERE data IN (1.0, 1.0);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Numeric type, implicit cast is merged
+CREATE TABLE test_merge_numeric (id int, data numeric(5, 2));
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_numeric WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Bigint, implicit cast is merged
+CREATE TABLE test_merge_bigint (id int, data bigint);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_bigint WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Bigint, explicit cast is not merged
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_bigint WHERE data IN
+ (1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint,
+ 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Bigint, long tokens with parenthesis
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_bigint WHERE id IN
+ (abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700),
+ abs(800), abs(900), abs(1000), ((abs(1100))));
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CoerceViaIO, SubLink instead of a Const
+CREATE TABLE test_merge_jsonb (id int, data jsonb);
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_jsonb WHERE data IN
+ ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb,
+ (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb,
+ (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb,
+ (SELECT '"10"')::jsonb);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CoerceViaIO
+
+-- Create some dummy type to force CoerceViaIO
+CREATE TYPE casttesttype;
+
+CREATE FUNCTION casttesttype_in(cstring)
+ RETURNS casttesttype
+ AS 'textin'
+ LANGUAGE internal STRICT IMMUTABLE;
+
+CREATE FUNCTION casttesttype_out(casttesttype)
+ RETURNS cstring
+ AS 'textout'
+ LANGUAGE internal STRICT IMMUTABLE;
+
+CREATE TYPE casttesttype (
+ internallength = variable,
+ input = casttesttype_in,
+ output = casttesttype_out,
+ alignment = int4
+);
+
+CREATE CAST (int4 AS casttesttype) WITH INOUT;
+
+CREATE FUNCTION casttesttype_eq(casttesttype, casttesttype)
+returns boolean language sql immutable as $$
+ SELECT true
+$$;
+
+CREATE OPERATOR = (
+ leftarg = casttesttype,
+ rightarg = casttesttype,
+ procedure = casttesttype_eq,
+ commutator = =);
+
+CREATE TABLE test_merge_cast (id int, data casttesttype);
+
+-- Use the introduced type to construct a list of CoerceViaIO around Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_cast WHERE data IN
+ (1::int4::casttesttype, 2::int4::casttesttype, 3::int4::casttesttype,
+ 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype,
+ 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype,
+ 10::int4::casttesttype, 11::int4::casttesttype);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Some casting expression are simplified to Const
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge_jsonb WHERE data IN
+ (('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
+ ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb,
+ ( '"9"')::jsonb, ( '"10"')::jsonb);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- RelabelType
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_merge WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Test constants evaluation in a CTE, which was causing issues in the past
+WITH cte AS (
+ SELECT 'const' as const FROM test_merge
+)
+SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
+FROM cte;
+
+-- Simple array would be merged as well
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+RESET query_id_squash_values;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 5e4f201e09..4ef10e9cbd 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8436,6 +8436,34 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-query-id-squash-values" xreflabel="query_id_squash_values">
+ <term><varname>query_id_squash_values</varname> (<type>bool</type>)
+ <indexterm>
+ <primary><varname>query_id_squash_values</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies how an array of constants (e.g. for an <literal>IN</literal>
+ 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 length.
+
+ 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>'($1 /*, ... */)'</literal>.
+
+ The parameter could be used to reduce amount of repeating data stored
+ via <xref linkend="pgstatstatements"/>. Only constants are affected,
+ bind parameters cannot benefit from this functionality. The default
+ value is <literal>off</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-log-statement-stats">
<term><varname>log_statement_stats</varname> (<type>boolean</type>)
<indexterm>
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 501b468e9a..0ef9785854 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -621,11 +621,28 @@
<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>query_id_squash_values</varname> is enabled and the only difference
+ between queries is the length of an array with constants they contain:
+
+<screen>
+=# SET query_id_squash_values = 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 ($1 /*, ... */)
+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>
@@ -956,6 +973,7 @@
</para>
</listitem>
</varlistentry>
+
</variablelist>
<para>
diff --git a/src/backend/nodes/gen_node_support.pl b/src/backend/nodes/gen_node_support.pl
index 1a657f7e0a..c421664879 100644
--- a/src/backend/nodes/gen_node_support.pl
+++ b/src/backend/nodes/gen_node_support.pl
@@ -476,6 +476,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
@@ -1283,6 +1284,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)
@@ -1295,21 +1297,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 'ParseLoc')
{
# 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 b103a28193..c3c5edff32 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -32,9 +32,13 @@
*/
#include "postgres.h"
+#include "access/transam.h"
+#include "catalog/pg_proc.h"
#include "common/hashfn.h"
#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
#include "nodes/queryjumble.h"
+#include "utils/lsyscache.h"
#include "parser/scansup.h"
#define JUMBLE_SIZE 1024 /* query serialization buffer size */
@@ -42,6 +46,9 @@
/* GUC parameters */
int compute_query_id = COMPUTE_QUERY_ID_AUTO;
+/* Whether to merge constants in a list when computing query_id */
+bool query_id_squash_values = false;
+
/*
* True when compute_query_id is ON or AUTO, and a module requests them.
*
@@ -53,8 +60,10 @@ 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, 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 _jumbleVariableSetStmt(JumbleState *jstate, Node *node);
@@ -198,11 +207,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)
@@ -218,15 +231,127 @@ 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 few simple cases where we can deduce that the expression is a
+ * constant:
+ *
+ * - Simplify the expression, if it's wrapped into RelabelType and CoerceViaIO.
+ * - If it's a FuncExpr, check if the function is an immutable builtin
+ * function doing implicit cast with constant arguments.
+ * - Otherwise test if the expression is a simple Const.
+ *
+ * We could also handle some simple OpExpr here as well, but since such queries
+ * will also have opno jumbled, this might lead to a confusing situation where
+ * two different queries end up with the same normalized query but different
+ * query_id.
+ *
+ * The argument known_immutable_funcs contains known function OIDs that were
+ * already proven to be immutable. If the expression to verify is a FuncExpr,
+ * we first check this list, and only if not found, test the function
+ * volatility and store the result back. Since most of the time constants
+ * merging will be dealing with same type of expressions, this avoids
+ * performing func_volatile over and over for the same functions.
+ *
+ * Note that we intentionally do not recurse on the function arguments and only
+ * test them for being Const expression for simplicity.
+ */
+static bool
+IsMergeableConst(Node *element, List **known_immutable_funcs)
+{
+ if (IsA(element, RelabelType))
+ element = (Node *) ((RelabelType *) element)->arg;
+
+ if (IsA(element, CoerceViaIO))
+ element = (Node *) ((CoerceViaIO *) element)->arg;
+
+ if(IsA(element, FuncExpr))
+ {
+ FuncExpr *func = (FuncExpr *) element;
+ ListCell *temp;
+
+ if (func->funcid > FirstGenbkiObjectId)
+ return false;
+
+ if (func->funcformat != COERCE_IMPLICIT_CAST)
+ return false;
+
+ if (!list_member_oid(*known_immutable_funcs, func->funcid))
+ {
+ /* Not found in the cache, verify and add if needed */
+ if(func_volatile(func->funcid) != PROVOLATILE_IMMUTABLE)
+ return false;
+
+ *known_immutable_funcs = lappend_oid(*known_immutable_funcs,
+ func->funcid);
+ }
+
+ foreach(temp, func->args)
+ {
+ Node *arg = lfirst(temp);
+
+ if (!IsA(arg, Const))
+ return false;
+ }
+
+ return true;
+ }
+
+ if (!IsA(element, Const))
+ return false;
+
+ return true;
+}
+
+/*
+ * Verify if the provided list 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, Node **firstExpr, Node **lastExpr)
+{
+ ListCell *temp;
+
+ /* To keep track of immutable functions in elements */
+ List *immutable_funcs = NIL;
+
+ /* A mergeable list needs to contain at least two elements */
+ if (elements == NIL || list_length(elements) < 2)
+ return false;
+
+ if (!query_id_squash_values)
+ {
+ /* Merging is disabled, process everything one by one */
+ return false;
+ }
+
+ foreach(temp, elements)
+ {
+ if (!IsMergeableConst(lfirst(temp), &immutable_funcs))
+ return false;
+ }
+ *firstExpr = linitial(elements);
+ *lastExpr = llast(elements);
+
+ return true;
+}
+
#define JUMBLE_NODE(item) \
_jumbleNode(jstate, (Node *) expr->item)
-#define JUMBLE_LOCATION(location) \
- RecordConstLocation(jstate, expr->location)
+#define JUMBLE_ELEMENTS(list) \
+ _jumbleElements(jstate, (List *) expr->list)
+#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) \
@@ -239,6 +364,33 @@ do { \
#include "queryjumblefuncs.funcs.c"
+static void
+_jumbleElements(JumbleState *jstate, List *elements)
+{
+ Node *first, *last;
+ if (IsMergeableConstList(elements, &first, &last))
+ {
+ /*
+ * Both first and last constants have to be recorded. The first one
+ * will indicate the merged interval, the last one will tell us the
+ * length of the interval within the query text.
+ *
+ * Note that for the last exression we actually need not the expression
+ * location (which is the leftmost expression), but where it ends. For
+ * the limited set of supported cases now (implicit coerce via
+ * FuncExpr, Const) it's fine to use exprLocation, but if more complex
+ * composite expressions will be supported, e.g. OpExpr or FuncExpr as
+ * an explicit call, the rightmost expression will be needed.
+ */
+ RecordConstLocation(jstate, exprLocation(first), true);
+ RecordConstLocation(jstate, exprLocation(last), true);
+ }
+ else
+ {
+ _jumbleNode(jstate, (Node *) elements);
+ }
+}
+
static void
_jumbleNode(JumbleState *jstate, Node *node)
{
@@ -375,5 +527,5 @@ _jumbleVariableSetStmt(JumbleState *jstate, Node *node)
if (expr->jumble_args)
JUMBLE_NODE(args);
JUMBLE_FIELD(is_local);
- JUMBLE_LOCATION(location);
+ JUMBLE_LOCATION(location, false);
}
diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c
index a97a1eda6d..052b700390 100644
--- a/src/backend/postmaster/launch_backend.c
+++ b/src/backend/postmaster/launch_backend.c
@@ -115,6 +115,7 @@ typedef struct
bool redirection_done;
bool IsBinaryUpgrade;
bool query_id_enabled;
+ bool query_id_squash_values;
int max_safe_fds;
int MaxBackends;
int num_pmchild_slots;
@@ -744,6 +745,7 @@ save_backend_variables(BackendParameters *param,
param->redirection_done = redirection_done;
param->IsBinaryUpgrade = IsBinaryUpgrade;
param->query_id_enabled = query_id_enabled;
+ param->query_id_squash_values = query_id_squash_values;
param->max_safe_fds = max_safe_fds;
param->MaxBackends = MaxBackends;
@@ -1004,6 +1006,7 @@ restore_backend_variables(BackendParameters *param)
redirection_done = param->redirection_done;
IsBinaryUpgrade = param->IsBinaryUpgrade;
query_id_enabled = param->query_id_enabled;
+ query_id_squash_values = param->query_id_squash_values;
max_safe_fds = param->max_safe_fds;
MaxBackends = param->MaxBackends;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 226af43fe2..eca30ba1a8 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2106,6 +2106,16 @@ struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
+ {
+ {"query_id_squash_values", PGC_USERSET, STATS_MONITORING,
+ gettext_noop("Allows to merge constants in a list when computing "
+ "query_id."),
+ },
+ &query_id_squash_values,
+ 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 d472987ed4..15939394bc 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -645,12 +645,12 @@
# - Monitoring -
#compute_query_id = auto
+#query_id_squash_values = off
#log_statement_stats = off
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
-
#------------------------------------------------------------------------------
# VACUUMING
#------------------------------------------------------------------------------
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 580238bfab..5656302544 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -108,6 +108,9 @@ typedef enum NodeTag
* - query_jumble_location: Mark the field as a location to track. This is
* only allowed for integer fields that include "location" in their name.
*
+ * - query_jumble_merge: Allow to merge the field values for the query
+ * jumbling.
+ *
* - read_as(VALUE): In nodeRead(), replace the field's value with VALUE.
*
* - read_write_ignore: Ignore the field for read/write. This is only allowed
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 839e71d52f..89587d4c10 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1394,7 +1394,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 50eb956658..d2f1c1e310 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,12 @@ 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 PGDLLIMPORT bool query_id_enabled;
+extern PGDLLIMPORT bool query_id_squash_values;
/*
* Returns whether query identifier computation has been enabled, either
base-commit: 773c51dd39ada5f107a3656377a9611ff89132f1
--
2.45.1
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-17 19:05 Álvaro Herrera <[email protected]>
parent: Dmitry Dolgov <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2025-03-17 19:05 UTC (permalink / raw)
To: Dmitry Dolgov <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; Sami Imseih <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
On 2025-Feb-14, Dmitry Dolgov wrote:
> This should do it. The last patch for today, otherwise I'll probably add
> more bugs than features :)
Thank you. I've spent some time with this patch in the last few days,
and I propose a few changes. I renamed everything from "merge" to
"squash"; apart from that, it's mostly docs and code comments changes,
but I also removed the addition of a boolean argument to JUMBLE_LOCATION
which AFAICS is unnecessary, and did away with the business of checking
for function immutability. I also changed the code layout of
generate_normalized_query(); no functional changes, I just reordered the
code blocks (which caused a couple of lines to appear repeated that
weren't before).
You can see my patch on top of yours here:
https://github.com/alvherre/postgres/commits/query_id_squash_values/
and the CI run here:
https://cirrus-ci.com/build/5660053472018432
In addition, here I attach the complete patch on top of current master.
Unless there's some opposition to this, I intend to push this tomorrow.
I have to admit that looking at this part of the test,
+SELECT * FROM test_squash_cast WHERE data IN
+ (1::int4::casttesttype, 2::int4::casttesttype, 3::int4::casttesttype,
+ 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype,
+ 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype,
+ 10::int4::casttesttype, 11::int4::casttesttype);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT * FROM test_squash_cast WHERE data IN +| 1
+ ($1 /*, ... */::int4::casttesttype) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
and
+SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id IN ($1 /*, ... */::oid) | 1
I am tempted to say that explicit casts should also be considered
squashable (that is, in IsSquashableConst() also allow the case of
func->funcformat == COERCE_EXPLICIT_CAST). That would also squash
queries such as this one:
+SELECT * FROM test_squash_bigint WHERE data IN
+ (1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint,
+ 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint);
+ id | data
+----+------
+(0 rows)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash_bigint WHERE data IN +| 1
+ ($1::bigint, $2::bigint, $3::bigint, $4::bigint, $5::bigint, $6::bigint,+|
+ $7::bigint, $8::bigint, $9::bigint, $10::bigint, $11::bigint) |
I, frankly, see little argument for making a distinction here. We can
still discuss whether we prefer it one way or the other; we don't need
that decision to prevent me from pushing the patch I here attach, I think.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"The saddest aspect of life right now is that science gathers knowledge faster
than society gathers wisdom." (Isaac Asimov)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-17 19:14 Álvaro Herrera <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Álvaro Herrera @ 2025-03-17 19:14 UTC (permalink / raw)
To: Dmitry Dolgov <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; Sami Imseih <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
On 2025-Mar-17, Álvaro Herrera wrote:
> You can see my patch on top of yours here:
> https://github.com/alvherre/postgres/commits/query_id_squash_values/
> and the CI run here:
> https://cirrus-ci.com/build/5660053472018432
Heh, this blew up on bogus SGML markup :-( Fixed and running again:
https://cirrus-ci.com/build/4822893680394240
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Once again, thank you and all of the developers for your hard work on
PostgreSQL. This is by far the most pleasant management experience of
any database I've worked on." (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-18 09:45 Dmitry Dolgov <[email protected]>
parent: Álvaro Herrera <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Dmitry Dolgov @ 2025-03-18 09:45 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; Sami Imseih <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
> On Mon, Mar 17, 2025 at 08:14:16PM GMT, Álvaro Herrera wrote:
> On 2025-Mar-17, Álvaro Herrera wrote:
>
> > You can see my patch on top of yours here:
> > https://github.com/alvherre/postgres/commits/query_id_squash_values/
> > and the CI run here:
> > https://cirrus-ci.com/build/5660053472018432
>
> Heh, this blew up on bogus SGML markup :-( Fixed and running again:
> https://cirrus-ci.com/build/4822893680394240
Thanks, much appreciated! I've inspected the diff between patches and run few
tests, at the first glance everything looks fine.
> I am tempted to say that explicit casts should also be considered
> squashable (that is, in IsSquashableConst() also allow the case of
> func->funcformat == COERCE_EXPLICIT_CAST).
Well, I admit I may have been burned too much by the initial reception of the
patch and handled it too conservatively in this regard. Originally I also had a
concern about normalized queries representation for explicit cast case, but it
was resolved by Julien's suggestion to switch to the /* ... */ format.
> I have to admit that I am leaning towards removing the immutability
> constraint. The reason is that we already require the function to be
> boostrapped (due to the OID test) and to have implicit cast form, so
> that limits which functions are recognized; the only ones there that are
> not immutable are:
>
> castsource │ casttarget │ castfunc
> ─────────────────────────────┼──────────────────────────┼──────────────────────────────────────────
> text │ regclass │ regclass(text)
> character varying │ regclass │ regclass(text)
> date │ timestamp with time zone │ timestamptz(date)
> time without time zone │ time with time zone │ timetz(time without time zone)
> timestamp without time zone │ timestamp with time zone │ timestamptz(timestamp without time zone)
Agree, when put together with the OID limitation it doesn't look so bad.
Somehow I was thinking about the Sami's proposal and the discussion in more
abstract terms, as if we talk about any arbitrary mutable functions to squash
-- I still would be cautious about hiding non-bootstrapped mutable functions.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-18 18:33 Álvaro Herrera <[email protected]>
parent: Dmitry Dolgov <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Álvaro Herrera @ 2025-03-18 18:33 UTC (permalink / raw)
To: Dmitry Dolgov <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; Sami Imseih <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
On 2025-Mar-18, Dmitry Dolgov wrote:
> Thanks, much appreciated! I've inspected the diff between patches and
> run few tests, at the first glance everything looks fine.
Thanks for looking once more.
> > I am tempted to say that explicit casts should also be considered
> > squashable (that is, in IsSquashableConst() also allow the case of
> > func->funcformat == COERCE_EXPLICIT_CAST).
>
> Well, I admit I may have been burned too much by the initial reception
> of the patch and handled it too conservatively in this regard.
I can totally understand that. I have added this and pushed. Hopefully
nobody will hate this too much, and some people might even like it.
By the way, I'm still open to adding the 'powers' mechanism that was
discussed earlier and other simple additions on top of what's there now,
if you have some spare energy to spend on this. (For full disclosure:
the "powers" thing was discussed in a developer's meeting last year, and
several people said they'd prefer to stick with 0001 and forget about
powers, on the grounds that it produced query texts that were weird and
invalid SQL. But now that we have the commented-out syntax, it's no
longer invalid SQL, and maybe it's not so weird either.)
But there are other proposals such as handling Params and whatnot. At
this point, what we'd need for that is a more extensive test suite to
show that we're not breaking other things ...
> Agree, when put together with the OID limitation it doesn't look so bad.
> Somehow I was thinking about the Sami's proposal and the discussion in more
> abstract terms,
Yeah, that happened to me too, and then I checked again and realized
that my initial impression was wrong.
> as if we talk about any arbitrary mutable functions to squash -- I
> still would be cautious about hiding non-bootstrapped mutable
> functions.
Yeah, absolutely.
One thing I noticed while going over the code, is that we say in some
code comments that the list of elements only contributes the first and
last elements to the jumble. But this is not true -- the list actually
contributes _nothing at all_ to the jumble. I don't think this causes
any terrible problems, but maybe somebody will discover that I'm wrong
on that. This isn't trivial to solve, because if you try to add
anything to the jumble from there, you'd break the first/last location
pair matching. We could maybe fix this by returning the actual
bottommost Const node from IsSquashableConstList() instead of whatever
is wrapping it, and then arrange for _jumbleConst() to receive a boolean
that turns off jumbling of the location.
However, contributing nothing already makes such a query different from
another query that has exactly one element, because that one jumbles
that element. It could only be confused (in the sense of identical
query_ids) with another list that has zero elements.
Anyway, something to play with.
BTW, it's fun to execute a query that's literally
select col from tab where col in (1 /*, ... */);
and then
select col from tab where col in (1, 2);
because now you have two entries in pg_stat_statements with visibly the
same query text, but two different query_ids. I'm not terribly worried
about this, because who uses a literal "/*, ... */" in a query anyway?
And even if they do, it's easily explained. But jesters could probably
get a good laugh messing about with these reports.
Thanks for keeping at this for so long!
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-18 18:54 Sami Imseih <[email protected]>
parent: Álvaro Herrera <[email protected]>
1 sibling, 2 replies; 14+ messages in thread
From: Sami Imseih @ 2025-03-18 18:54 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Dmitry Dolgov <[email protected]>; Julien Rouhaud <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
I want to mention that the current patch does not deal
with external parameters ( prepared statements ) [0] [1]. This could be a
follow-up, as it may need some further discussion. It is important to
address this case, IMO.
[0]
https://www.postgresql.org/message-id/CAA5RZ0uGfxXyzhp9N5nfsS%2BZqF5ngEMC3YtBPtLoeK8EPsjHbw%40mail.g...
[1]
https://www.postgresql.org/message-id/blauoky77sash2qzrbnz6ilfyi7odtvxtdr4ifg4hq4bpqp2uk%406z5yjfsxt...
Regards,
Sami
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-18 20:17 Álvaro Herrera <[email protected]>
parent: Sami Imseih <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Álvaro Herrera @ 2025-03-18 20:17 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Dmitry Dolgov <[email protected]>; Julien Rouhaud <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
On 2025-Mar-18, Sami Imseih wrote:
> I want to mention that the current patch does not deal
> with external parameters ( prepared statements ) [0] [1]. This could be a
> follow-up, as it may need some further discussion. It is important to
> address this case, IMO.
Yes, I realize that. Feel free to send a patch.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"E pur si muove" (Galileo Galilei)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-19 07:31 Dmitry Dolgov <[email protected]>
parent: Álvaro Herrera <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Dmitry Dolgov @ 2025-03-19 07:31 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Julien Rouhaud <[email protected]>; Sami Imseih <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
> On Tue, Mar 18, 2025 at 07:33:25PM GMT, Álvaro Herrera wrote:
>
> By the way, I'm still open to adding the 'powers' mechanism that was
> discussed earlier and other simple additions on top of what's there now,
> if you have some spare energy to spend on this. (For full disclosure:
> the "powers" thing was discussed in a developer's meeting last year, and
> several people said they'd prefer to stick with 0001 and forget about
> powers, on the grounds that it produced query texts that were weird and
> invalid SQL. But now that we have the commented-out syntax, it's no
> longer invalid SQL, and maybe it's not so weird either.)
>
> But there are other proposals such as handling Params and whatnot. At
> this point, what we'd need for that is a more extensive test suite to
> show that we're not breaking other things ...
Yes, I'm planning to continue working on this topic, there are still
plenty of things that could be improved.
> One thing I noticed while going over the code, is that we say in some
> code comments that the list of elements only contributes the first and
> last elements to the jumble. But this is not true -- the list actually
> contributes _nothing at all_ to the jumble. I don't think this causes
> any terrible problems, but maybe somebody will discover that I'm wrong
> on that. This isn't trivial to solve, because if you try to add
> anything to the jumble from there, you'd break the first/last location
> pair matching. We could maybe fix this by returning the actual
> bottommost Const node from IsSquashableConstList() instead of whatever
> is wrapping it, and then arrange for _jumbleConst() to receive a boolean
> that turns off jumbling of the location.
>
> However, contributing nothing already makes such a query different from
> another query that has exactly one element, because that one jumbles
> that element. It could only be confused (in the sense of identical
> query_ids) with another list that has zero elements.
>
> Anyway, something to play with.
Yep, I don't see this as an immediate problem as well, but will do some
experiments with that.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-19 07:32 Dmitry Dolgov <[email protected]>
parent: Sami Imseih <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: Dmitry Dolgov @ 2025-03-19 07:32 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; Julien Rouhaud <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
> On Tue, Mar 18, 2025 at 02:54:18PM GMT, Sami Imseih wrote:
> I want to mention that the current patch does not deal
> with external parameters ( prepared statements ) [0] [1]. This could be a
> follow-up, as it may need some further discussion. It is important to
> address this case, IMO.
Sure, it's important and I'm planning to tackle this next. If you want,
we can collaborate on a patch for that.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: pg_stat_statements and "IN" conditions
@ 2025-03-22 20:59 Sami Imseih <[email protected]>
parent: Dmitry Dolgov <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Sami Imseih @ 2025-03-22 20:59 UTC (permalink / raw)
To: Dmitry Dolgov <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; Julien Rouhaud <[email protected]>; Kirill Reshke <[email protected]>; Sergei Kornilov <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; Sutou Kouhei <[email protected]>
> Sure, it's important and I'm planning to tackle this next. If you want,
> we can collaborate on a patch for that.
I spent some time looking some more at this, and I believe all that needs
to be done is check for a PRAM node with a type of PARAM_EXTERN.
During planning the planner turns the Param into a Const during
eval_const_expressions_mutator.
If it's as simple as I think it is, I hope we can get this committed for 18.
If not, and a longer discussion is needed, a new thread can be started
for this.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v1-0001-Allow-query-jumble-to-squash-a-list-external-paramet.patch (4.2K, ../../CAA5RZ0upwUPkTFWAYLFUVCCND9kw13U5Dc-EpAZCFTgjgWP2dw@mail.gmail.com/2-v1-0001-Allow-query-jumble-to-squash-a-list-external-paramet.patch)
download | inline diff:
From 76afc3c74f222d3bf5551e7b45cf736453ae91c9 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)"
<[email protected]>
Date: Fri, 21 Mar 2025 05:37:59 +0000
Subject: [PATCH 1/1] Allow query jumble to squash a list external parameters
62d712ecf now allows query jumbling to squash a list of constants,
but not constants that are passed as external parameters. This patch
now allows the squashing of constant values supplied as external parameters
(e.g., $1, $2), as is the case with prepared statements.
---
.../pg_stat_statements/expected/squashing.out | 38 +++++++++++++++++++
contrib/pg_stat_statements/sql/squashing.sql | 12 ++++++
src/backend/nodes/queryjumblefuncs.c | 20 ++++++++--
3 files changed, 66 insertions(+), 4 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/squashing.out b/contrib/pg_stat_statements/expected/squashing.out
index 55aa5109433..370d91642d4 100644
--- a/contrib/pg_stat_statements/expected/squashing.out
+++ b/contrib/pg_stat_statements/expected/squashing.out
@@ -333,6 +333,44 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
+-- Test bind parameters
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_squash_bigint WHERE data IN ($1, $2, $3) \bind 1 2 3
+;
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash_bigint WHERE data IN ($1, $2, $3, $4) \bind 1 2 3 4
+;
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash_bigint WHERE data IN
+ ($1::bigint, $2::bigint, $3::bigint, $4::bigint) \bind 1 2 3 4
+;
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash_bigint WHERE data 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_squash_bigint WHERE data IN ($1 /*, ... */) | 4
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
-- CoerceViaIO
-- Create some dummy type to force CoerceViaIO
CREATE TYPE casttesttype;
diff --git a/contrib/pg_stat_statements/sql/squashing.sql b/contrib/pg_stat_statements/sql/squashing.sql
index 56ee8ccb9a1..3ff251a59ee 100644
--- a/contrib/pg_stat_statements/sql/squashing.sql
+++ b/contrib/pg_stat_statements/sql/squashing.sql
@@ -106,6 +106,18 @@ SELECT * FROM test_squash_jsonb WHERE data IN
(SELECT '"10"')::jsonb);
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- Test bind parameters
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_squash_bigint WHERE data IN ($1, $2, $3) \bind 1 2 3
+;
+SELECT * FROM test_squash_bigint WHERE data IN ($1, $2, $3, $4) \bind 1 2 3 4
+;
+SELECT * FROM test_squash_bigint WHERE data IN
+ ($1::bigint, $2::bigint, $3::bigint, $4::bigint) \bind 1 2 3 4
+;
+SELECT * FROM test_squash_bigint WHERE data IN (1, 2, 3, 4);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
-- CoerceViaIO
-- Create some dummy type to force CoerceViaIO
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 189bfda610a..de405ab08f3 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -244,7 +244,8 @@ RecordConstLocation(JumbleState *jstate, int location, bool squashed)
* - Ignore a possible wrapping RelabelType and CoerceViaIO.
* - If it's a FuncExpr, check that the function is an implicit
* cast and its arguments are Const.
- * - Otherwise test if the expression is a simple Const.
+ * - Otherwise test if the expression is a simple Const or an
+ * external parameter.
*/
static bool
IsSquashableConst(Node *element)
@@ -278,10 +279,21 @@ IsSquashableConst(Node *element)
return true;
}
- if (!IsA(element, Const))
- return false;
+ switch (nodeTag(element))
+ {
+ case T_Const:
+ return true;
+ case T_Param:
+ {
+ Param *param = (Param *) element;
- return true;
+ return param->paramkind == PARAM_EXTERN;
+ }
+ default:
+ break;
+ }
+
+ return false;
}
/*
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-03-22 20:59 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-23 00:20 [PATCH 2/3] Make explain analyze default to BUFFERS TRUE.. Justin Pryzby <[email protected]>
2025-02-14 14:39 Re: pg_stat_statements and "IN" conditions Julien Rouhaud <[email protected]>
2025-02-14 14:56 ` Re: pg_stat_statements and "IN" conditions Dmitry Dolgov <[email protected]>
2025-02-14 15:12 ` Re: pg_stat_statements and "IN" conditions Julien Rouhaud <[email protected]>
2025-02-14 15:40 ` Re: pg_stat_statements and "IN" conditions Dmitry Dolgov <[email protected]>
2025-03-17 19:05 ` Re: pg_stat_statements and "IN" conditions Álvaro Herrera <[email protected]>
2025-03-17 19:14 ` Re: pg_stat_statements and "IN" conditions Álvaro Herrera <[email protected]>
2025-03-18 09:45 ` Re: pg_stat_statements and "IN" conditions Dmitry Dolgov <[email protected]>
2025-03-18 18:33 ` Re: pg_stat_statements and "IN" conditions Álvaro Herrera <[email protected]>
2025-03-18 18:54 ` Re: pg_stat_statements and "IN" conditions Sami Imseih <[email protected]>
2025-03-18 20:17 ` Re: pg_stat_statements and "IN" conditions Álvaro Herrera <[email protected]>
2025-03-19 07:32 ` Re: pg_stat_statements and "IN" conditions Dmitry Dolgov <[email protected]>
2025-03-22 20:59 ` Re: pg_stat_statements and "IN" conditions Sami Imseih <[email protected]>
2025-03-19 07:31 ` Re: pg_stat_statements and "IN" conditions Dmitry Dolgov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox