aboutsummaryrefslogtreecommitdiff
path: root/contrib
diff options
context:
space:
mode:
Diffstat (limited to 'contrib')
-rw-r--r--contrib/pg_overexplain/expected/pg_overexplain.out12
-rw-r--r--contrib/pg_stat_statements/expected/level_tracking.out182
-rw-r--r--contrib/pg_stat_statements/expected/planning.out10
-rw-r--r--contrib/pg_stat_statements/expected/select.out32
-rw-r--r--contrib/pg_stat_statements/expected/squashing.out535
-rw-r--r--contrib/pg_stat_statements/expected/utility.out2
-rw-r--r--contrib/pg_stat_statements/pg_stat_statements.c83
-rw-r--r--contrib/pg_stat_statements/sql/planning.sql4
-rw-r--r--contrib/pg_stat_statements/sql/select.sql8
-rw-r--r--contrib/pg_stat_statements/sql/squashing.sql186
10 files changed, 783 insertions, 271 deletions
diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out
index cb5c396c519..6de02323d7c 100644
--- a/contrib/pg_overexplain/expected/pg_overexplain.out
+++ b/contrib/pg_overexplain/expected/pg_overexplain.out
@@ -37,7 +37,7 @@ EXPLAIN (DEBUG) SELECT 1;
Subplans Needing Rewind: none
Relation OIDs: none
Executor Parameter Types: none
- Parse Location: 16 for 8 bytes
+ Parse Location: 0 to end
(11 rows)
EXPLAIN (RANGE_TABLE) SELECT 1;
@@ -119,7 +119,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: none
- Parse Location: 41 to end
+ Parse Location: 0 to end
RTI 1 (relation, inherited, in-from-clause):
Eref: vegetables (id, name, genus)
Relation: vegetables
@@ -240,7 +240,7 @@ $$);
<Subplans-Needing-Rewind>none</Subplans-Needing-Rewind> +
<Relation-OIDs>NNN...</Relation-OIDs> +
<Executor-Parameter-Types>none</Executor-Parameter-Types> +
- <Parse-Location>53 to end</Parse-Location> +
+ <Parse-Location>0 to end</Parse-Location> +
</PlannedStmt> +
<Range-Table> +
<Range-Table-Entry> +
@@ -344,7 +344,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: none
- Parse Location: 28 to end
+ Parse Location: 0 to end
(37 rows)
SET debug_parallel_query = false;
@@ -372,7 +372,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: 0
- Parse Location: 28 to end
+ Parse Location: 0 to end
(15 rows)
-- Create an index, and then attempt to force a nested loop with inner index
@@ -436,7 +436,7 @@ $$);
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: 23
- Parse Location: 75 for 62 bytes
+ Parse Location: 0 to end
(47 rows)
RESET enable_hashjoin;
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index 75e785e1719..8213fcd2e61 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -206,37 +206,37 @@ EXPLAIN (COSTS OFF) SELECT 1 UNION SELECT 2;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+--------------------------------------------------------------------
- f | 1 | DELETE FROM stats_track_tab
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2)
+ f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2);
t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table)
+ f | 1 | EXPLAIN (COSTS OFF) (TABLE test_table);
t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2))
+ f | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2));
t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab
+ f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab;
t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1))
- t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab +
- | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
+ f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1));
+ t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab +
+ | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
| | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab +
+ | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
t | 1 | EXPLAIN (COSTS OFF) SELECT $1
t | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2
+ f | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2;
+ f | 1 | EXPLAIN (COSTS OFF) SELECT $1;
t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab
+ f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab;
t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2
+ f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2;
t | 1 | EXPLAIN (COSTS OFF) VALUES ($1)
- f | 1 | INSERT INTO stats_track_tab VALUES (($1))
- f | 1 | MERGE INTO stats_track_tab +
- | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
- f | 1 | SELECT $1
- f | 1 | SELECT $1 UNION SELECT $2
- f | 1 | SELECT $1, $2
+ f | 1 | EXPLAIN (COSTS OFF) VALUES ($1);
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | TABLE stats_track_tab
- f | 1 | TABLE test_table
- f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2
- f | 1 | VALUES ($1)
- f | 1 | VALUES ($1, $2)
(23 rows)
-- EXPLAIN - top-level tracking.
@@ -405,20 +405,20 @@ EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4\; EXPLAIN (COSTS OFF) (SELECT
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+-----------------------------------------------------------------
- f | 1 | (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ toplevel | calls | query
+----------+-------+---------------------------------------------------------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3)
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6
+ f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3); EXPLAIN (COSTS OFF) (SELECT 1, 2, 3, 4);
t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3, $4)
+ f | 1 | EXPLAIN (COSTS OFF) (SELECT 1, 2, 3); EXPLAIN (COSTS OFF) (SELECT $1, $2, $3, $4);
t | 1 | EXPLAIN (COSTS OFF) SELECT $1
t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2
t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 UNION SELECT $3, $4
- f | 1 | SELECT $1
- f | 1 | SELECT $1, $2
- f | 1 | SELECT $1, $2 UNION SELECT $3, $4
- f | 1 | SELECT $1, $2, $3
- f | 1 | SELECT $1, $2, $3, $4
+ f | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 UNION SELECT $3, $4; EXPLAIN (COSTS OFF) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5;
+ f | 1 | EXPLAIN (COSTS OFF) SELECT $1; EXPLAIN (COSTS OFF) SELECT 1, 2;
+ f | 1 | EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4; EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6;
+ f | 1 | EXPLAIN (COSTS OFF) SELECT 1; EXPLAIN (COSTS OFF) SELECT $1, $2;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(13 rows)
@@ -494,29 +494,29 @@ EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1))\; EXPLAIN (COSTS OF
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+--------------------------------------------------------------------
- f | 1 | DELETE FROM stats_track_tab
- f | 1 | DELETE FROM stats_track_tab WHERE x = $1
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------------------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table)
t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2))
t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab
t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = $1
+ f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = $1;
+ f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = 1;
t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ($1), ($2)
t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1))
+ f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)); EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (1), (2);
+ f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1)); EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ($1), ($2);
t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab
+ f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; EXPLAIN (COSTS OFF) (TABLE test_table);
+ f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; EXPLAIN (COSTS OFF) (TABLE test_table);
t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1
t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2
+ f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1;
+ f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1 WHERE x = 1; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1;
t | 1 | EXPLAIN (COSTS OFF) VALUES ($1)
- f | 1 | INSERT INTO stats_track_tab VALUES ($1), ($2)
- f | 1 | INSERT INTO stats_track_tab VALUES (($1))
+ f | 1 | EXPLAIN (COSTS OFF) VALUES ($1); EXPLAIN (COSTS OFF) (VALUES (1, 2));
+ f | 1 | EXPLAIN (COSTS OFF) VALUES (1); EXPLAIN (COSTS OFF) (VALUES ($1, $2));
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | TABLE stats_track_tab
- f | 1 | TABLE test_table
- f | 1 | UPDATE stats_track_tab SET x = $1
- f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2
- f | 1 | VALUES ($1)
- f | 1 | VALUES ($1, $2)
(21 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -547,18 +547,21 @@ EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------------
- t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab +
- | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
- | | WHEN MATCHED THEN UPDATE SET x = id +
+ toplevel | calls | query
+----------+-------+------------------------------------------------------------------------------------------------
+ t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab +
+ | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
| | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab +
+ | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); EXPLAIN (COSTS OFF) SELECT 1, 2, 3, 4, 5;
+ f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab +
+ | | USING (SELECT id FROM generate_series(1, 10) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5;
t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5
- f | 1 | MERGE INTO stats_track_tab +
- | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id+
- | | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
- f | 1 | SELECT $1, $2, $3, $4, $5
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
@@ -786,29 +789,29 @@ EXPLAIN (COSTS OFF) WITH a AS (select 4) SELECT 1 UNION SELECT 2;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+------------------------------------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+-------------------------------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3))
+ f | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3));
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) DELETE FROM stats_track_tab
+ f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) DELETE FROM stats_track_tab;
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2))
- t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab +
- | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
+ f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2));
+ t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab +
+ | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
| | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
+ f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab +
+ | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id);
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2
+ f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2;
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3
+ f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3;
t | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3
+ f | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | WITH a AS (SELECT $1) (SELECT $2, $3)
- f | 1 | WITH a AS (SELECT $1) DELETE FROM stats_track_tab
- f | 1 | WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2))
- f | 1 | WITH a AS (SELECT $1) MERGE INTO stats_track_tab +
- | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id)
- f | 1 | WITH a AS (SELECT $1) SELECT $2
- f | 1 | WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3
- f | 1 | WITH a AS (select $1) SELECT $2 UNION SELECT $3
(15 rows)
-- EXPLAIN with CTEs - top-level tracking
@@ -918,13 +921,14 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+------------------------------------------------------------------------------
- t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) +
+ toplevel | calls | query
+----------+-------+-------------------------------------------------------------------------------
+ t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) +
| | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab
+ f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) +
+ | | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab;
t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1
- f | 1 | SELECT $1
- f | 1 | SELECT * FROM stats_track_tab
+ f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
@@ -1047,10 +1051,10 @@ SELECT toplevel, calls, query FROM pg_stat_statements
toplevel | calls | query
----------+-------+-----------------------------------------------------------------
t | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1
+ f | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1;
t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss
- f | 1 | SELECT $1
+ f | 1 | PREPARE test_prepare_pgss AS select generate_series($1, $2)
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | select generate_series($1, $2)
(5 rows)
-- CREATE TABLE AS, top-level tracking.
@@ -1088,10 +1092,10 @@ EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------------------------
t | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1
- f | 1 | SELECT $1
+ f | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1;
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(3 rows)
@@ -1136,14 +1140,14 @@ CLOSE foocur;
COMMIT;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------
+ toplevel | calls | query
+----------+-------+----------------------------------------------------------
t | 1 | BEGIN
t | 1 | CLOSE foocur
t | 1 | COMMIT
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
+ f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
t | 1 | FETCH FORWARD 1 FROM foocur
- f | 1 | SELECT * from stats_track_tab
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(7 rows)
@@ -1203,25 +1207,25 @@ COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout;
2
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C";
- toplevel | calls | query
-----------+-------+---------------------------------------------------------------------------
+ toplevel | calls | query
+----------+-------+-----------------------------------------------------------------------------
+ f | 1 | COPY (DELETE FROM stats_track_tab WHERE x = $1 RETURNING x) TO stdout
t | 1 | COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout
+ f | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x) TO stdout
t | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) TO stdout
- t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
+ f | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT $1 id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
| | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout
+ t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id +
+ | | WHEN MATCHED THEN UPDATE SET x = id +
+ | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout
+ f | 1 | COPY (SELECT $1 UNION SELECT $2) TO stdout
+ f | 1 | COPY (SELECT $1) TO stdout
t | 1 | COPY (SELECT 1 UNION SELECT 2) TO stdout
t | 1 | COPY (SELECT 1) TO stdout
+ f | 1 | COPY (UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x) TO stdout
t | 1 | COPY (UPDATE stats_track_tab SET x = 2 WHERE x = 1 RETURNING x) TO stdout
- f | 1 | DELETE FROM stats_track_tab WHERE x = $1 RETURNING x
- f | 1 | INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x
- f | 1 | MERGE INTO stats_track_tab USING (SELECT $1 id) ON x = id +
- | | WHEN MATCHED THEN UPDATE SET x = id +
- | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x
- f | 1 | SELECT $1
- f | 1 | SELECT $1 UNION SELECT $2
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
- f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x
(13 rows)
-- COPY - top-level tracking.
diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out
index 3ee1928cbe9..9effd11fdc8 100644
--- a/contrib/pg_stat_statements/expected/planning.out
+++ b/contrib/pg_stat_statements/expected/planning.out
@@ -58,7 +58,7 @@ SELECT 42;
(1 row)
SELECT plans, calls, rows, query FROM pg_stat_statements
- WHERE query NOT LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
+ WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
plans | calls | rows | query
-------+-------+------+----------------------------------------------------------
0 | 1 | 0 | ALTER TABLE stats_plan_test ADD COLUMN x int
@@ -72,10 +72,10 @@ SELECT plans, calls, rows, query FROM pg_stat_statements
-- for the prepared statement we expect at least one replan, but cache
-- invalidations could force more
SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements
- WHERE query LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
- plans_ok | calls | rows | query
-----------+-------+------+--------------------------------------
- t | 4 | 4 | SELECT COUNT(*) FROM stats_plan_test
+ WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+ plans_ok | calls | rows | query
+----------+-------+------+-------------------------------------------------------
+ t | 4 | 4 | PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test
(1 row)
-- Cleanup
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
index 038ae110364..75c896f3885 100644
--- a/contrib/pg_stat_statements/expected/select.out
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -208,6 +208,7 @@ DEALLOCATE pgss_test;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
calls | rows | query
-------+------+------------------------------------------------------------------------------
+ 1 | 1 | PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3
4 | 4 | SELECT $1 +
| | -- but this one will appear +
| | AS "text"
@@ -221,7 +222,6 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
2 | 2 | SELECT $1 AS "int" ORDER BY 1
1 | 2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i
1 | 1 | SELECT $1 || $2
- 1 | 1 | SELECT $1, $2 LIMIT $3
2 | 2 | SELECT DISTINCT $1 AS "int"
0 | 0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
@@ -267,6 +267,36 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 0
(4 rows)
+-- with the last element being an explicit function call with an argument, ensure
+-- the normalization of the squashing interval is correct.
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT WHERE 1 IN (1, int4(1), int4(2));
+--
+(1 row)
+
+SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2)]);
+--
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+------------------------------------------------------------------------+-------
+ SELECT WHERE $1 IN ($2 /*, ... */) | 2
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 0
+(3 rows)
+
--
-- queries with locking clauses
--
diff --git a/contrib/pg_stat_statements/expected/squashing.out b/contrib/pg_stat_statements/expected/squashing.out
index 7b138af098c..7b935d464ec 100644
--- a/contrib/pg_stat_statements/expected/squashing.out
+++ b/contrib/pg_stat_statements/expected/squashing.out
@@ -2,9 +2,11 @@
-- Const squashing functionality
--
CREATE EXTENSION pg_stat_statements;
+--
+-- Simple Lists
+--
CREATE TABLE test_squash (id int, data int);
--- IN queries
--- Normal scenario, too many simple constants for an IN query
+-- single element will not be squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
@@ -16,42 +18,150 @@ SELECT * FROM test_squash WHERE id IN (1);
----+------
(0 rows)
+SELECT ARRAY[1];
+ array
+-------
+ {1}
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id IN ($1) | 1
+ SELECT ARRAY[$1] | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
+
+-- more than 1 element in a list will be squashed
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
SELECT * FROM test_squash WHERE id IN (1, 2, 3);
id | data
----+------
(0 rows)
+SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4);
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5);
+ id | data
+----+------
+(0 rows)
+
+SELECT ARRAY[1, 2, 3];
+ array
+---------
+ {1,2,3}
+(1 row)
+
+SELECT ARRAY[1, 2, 3, 4];
+ array
+-----------
+ {1,2,3,4}
+(1 row)
+
+SELECT ARRAY[1, 2, 3, 4, 5];
+ array
+-------------
+ {1,2,3,4,5}
+(1 row)
+
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
-------------------------------------------------------+-------
- SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 1
- SELECT * FROM test_squash WHERE id IN ($1) | 1
+ SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 3
+ SELECT ARRAY[$1 /*, ... */] | 3
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(3 rows)
-SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
+-- built-in functions will be squashed
+-- the IN and ARRAY forms of this statement will have the same queryId
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT WHERE 1 IN (1, int4(1), int4(2), 2);
+--
+(1 row)
+
+SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2), 2]);
+--
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT WHERE $1 IN ($2 /*, ... */) | 2
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(2 rows)
+
+-- external parameters will not be squashed
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) \bind 1 2 3 4 5
+;
id | data
----+------
(0 rows)
-SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
+SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) \bind 1 2 3 4 5
+;
id | data
----+------
(0 rows)
-SELECT * FROM test_squash 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";
+ query | calls
+---------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) | 1
+ SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
+
+-- neither are prepared statements
+-- the IN and ARRAY forms of this statement will have the same queryId
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+PREPARE p1(int, int, int, int, int) AS
+SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5);
+EXECUTE p1(1, 2, 3, 4, 5);
+ id | data
+----+------
+(0 rows)
+
+DEALLOCATE p1;
+PREPARE p1(int, int, int, int, int) AS
+SELECT * FROM test_squash WHERE id = ANY(ARRAY[$1, $2, $3, $4, $5]);
+EXECUTE p1(1, 2, 3, 4, 5);
id | data
----+------
(0 rows)
+DEALLOCATE p1;
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
-------------------------------------------------------------------------+-------
- SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 4
- SELECT * FROM test_squash 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)
+ query | calls
+------------------------------------------------------------+-------
+ DEALLOCATE $1 | 2
+ PREPARE p1(int, int, int, int, int) AS +| 2
+ SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) |
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
-- More conditions in the query
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
@@ -75,10 +185,25 @@ SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) AND da
----+------
(0 rows)
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]) AND data = 2;
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AND data = 2;
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[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_squash WHERE id IN ($1 /*, ... */) AND data = $2 | 3
+ SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) AND data = $2 | 6
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
@@ -107,24 +232,46 @@ SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
----+------
(0 rows)
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9])
+ AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]);
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10])
+ AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11])
+ AND data = ANY (ARRAY[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_squash WHERE id IN ($1 /*, ... */)+| 3
+ SELECT * FROM test_squash WHERE id IN ($1 /*, ... */)+| 6
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 squashed, even though
--- the normalized query will be the same
+-- No constants squashing for OpExpr
+-- The IN and ARRAY forms of this statement will have the same queryId
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
SELECT * FROM test_squash WHERE id IN
(1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9);
id | data
@@ -137,19 +284,35 @@ SELECT * FROM test_squash WHERE id IN
----+------
(0 rows)
+SELECT * FROM test_squash WHERE id = ANY(ARRAY
+ [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_squash WHERE id = ANY(ARRAY
+ [@ '-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_squash WHERE id IN +| 1
+ SELECT * FROM test_squash WHERE id IN +| 2
($1 + $2, $3 + $4, $5 + $6, $7 + $8, $9 + $10, $11 + $12, $13 + $14, $15 + $16, $17 + $18) |
- SELECT * FROM test_squash WHERE id IN +| 1
+ SELECT * FROM test_squash WHERE id IN +| 2
(@ $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);
+-- The casted ARRAY expressions will have the same queryId as the IN clause
+-- form of the query
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
@@ -181,12 +344,38 @@ SELECT data FROM test_float WHERE data IN (1.0, 1.0);
------
(0 rows)
+SELECT data FROM test_float WHERE data = ANY(ARRAY['1'::double precision, '2'::double precision]);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data = ANY(ARRAY[1.0::double precision, 1.0::double precision]);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data = ANY(ARRAY[1, 2]);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data = ANY(ARRAY[1, '2']);
+ data
+------
+(0 rows)
+
+SELECT data FROM test_float WHERE data = ANY(ARRAY['1', 2]);
+ 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)
+ query | calls
+--------------------------------------------------------------------+-------
+ SELECT data FROM test_float WHERE data = ANY(ARRAY[$1 /*, ... */]) | 3
+ SELECT data FROM test_float WHERE data IN ($1 /*, ... */) | 7
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
-- Numeric type, implicit cast is squashed
CREATE TABLE test_squash_numeric (id int, data numeric(5, 2));
@@ -201,12 +390,18 @@ SELECT * FROM test_squash_numeric WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
----+------
(0 rows)
+SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[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_squash_numeric WHERE data IN ($1 /*, ... */) | 1
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+ query | calls
+--------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[$1 /*, ... */]) | 1
+ SELECT * FROM test_squash_numeric WHERE data IN ($1 /*, ... */) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
-- Bigint, implicit cast is squashed
CREATE TABLE test_squash_bigint (id int, data bigint);
@@ -221,14 +416,20 @@ SELECT * FROM test_squash_bigint WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1
----+------
(0 rows)
+SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[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_squash_bigint WHERE data IN ($1 /*, ... */) | 1
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
-(2 rows)
+ query | calls
+-------------------------------------------------------------------------+-------
+ SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[$1 /*, ... */]) | 1
+ SELECT * FROM test_squash_bigint WHERE data IN ($1 /*, ... */) | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(3 rows)
--- Bigint, explicit cast is not squashed
+-- Bigint, explicit cast is squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
@@ -242,15 +443,22 @@ SELECT * FROM test_squash_bigint WHERE data IN
----+------
(0 rows)
+SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[
+ 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) |
+ SELECT * FROM test_squash_bigint WHERE data IN +| 2
+ ($1 /*, ... */) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
--- Bigint, long tokens with parenthesis
+-- Bigint, long tokens with parenthesis, will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
@@ -264,44 +472,47 @@ SELECT * FROM test_squash_bigint WHERE id IN
----+------
(0 rows)
+SELECT * FROM test_squash_bigint WHERE id = ANY(ARRAY[
+ 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_squash_bigint WHERE id IN +| 1
+ SELECT * FROM test_squash_bigint WHERE id IN +| 2
(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_squash_jsonb (id int, data jsonb);
+-- Multiple FuncExpr's. Will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
-SELECT * FROM test_squash_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 WHERE 1 IN (1::int::bigint::int, 2::int::bigint::int);
+--
+(1 row)
+
+SELECT WHERE 1 = ANY(ARRAY[1::int::bigint::int, 2::int::bigint::int]);
+--
+(1 row)
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls
-----------------------------------------------------------------------+-------
- SELECT * FROM test_squash_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
+ query | calls
+----------------------------------------------------+-------
+ SELECT WHERE $1 IN ($2 /*, ... */) | 2
+ 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)
@@ -349,15 +560,25 @@ SELECT * FROM test_squash_cast WHERE data IN
----+------
(0 rows)
+SELECT * FROM test_squash_cast WHERE data = ANY (ARRAY
+ [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 * FROM test_squash_cast WHERE data IN +| 2
+ ($1 /*, ... */) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
-- Some casting expression are simplified to Const
+CREATE TABLE test_squash_jsonb (id int, data jsonb);
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
@@ -366,8 +587,16 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash_jsonb WHERE data IN
(('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
- ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb,
- ( '"9"')::jsonb, ( '"10"')::jsonb);
+ ('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb,
+ ('"9"')::jsonb, ('"10"')::jsonb);
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash_jsonb WHERE data = ANY (ARRAY
+ [('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
+ ('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb,
+ ('"9"')::jsonb, ('"10"')::jsonb]);
id | data
----+------
(0 rows)
@@ -375,28 +604,144 @@ SELECT * FROM test_squash_jsonb WHERE data IN
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls
----------------------------------------------------+-------
- SELECT * FROM test_squash_jsonb WHERE data IN +| 1
- (($1 /*, ... */)::jsonb) |
+ SELECT * FROM test_squash_jsonb WHERE data IN +| 2
+ ($1 /*, ... */) |
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
(2 rows)
+-- CoerceViaIO, SubLink instead of a Const. Will not squash
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT * FROM test_squash_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 * FROM test_squash_jsonb WHERE data = ANY(ARRAY
+ [(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_squash_jsonb WHERE data IN +| 2
+ ((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)
+
+-- Multiple CoerceViaIO wrapping a constant. Will not squash
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT WHERE 1 IN (1::text::int::text::int, 1::text::int::text::int);
+--
+(1 row)
+
+SELECT WHERE 1 = ANY(ARRAY[1::text::int::text::int, 1::text::int::text::int]);
+--
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+-------------------------------------------------------------------------+-------
+ SELECT WHERE $1 IN ($2::text::int::text::int, $3::text::int::text::int) | 2
+ 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_squash WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
+-- if there is only one level of RelabelType, the list will be squashable
+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 ARRAY[1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid];
+ array
+---------------------
+ {1,2,3,4,5,6,7,8,9}
+(1 row)
+
+-- if there is at least one element with multiple levels of RelabelType,
+-- the list will not be squashable
+SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid::int::oid);
+ id | data
+----+------
+(0 rows)
+
+SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::oid, 2::oid::int::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
- SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+ query | calls
+--------------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id IN +| 1
+ ($1 /*, ... */) |
+ SELECT * FROM test_squash WHERE id IN ($1::oid, $2::oid::int::oid) | 2
+ SELECT ARRAY[$1 /*, ... */] | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+(4 rows)
+
+--
+-- edge cases
+--
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+-- for nested arrays, only constants are squashed
+SELECT ARRAY[
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
+ ];
+ array
+-----------------------------------------------------------------------------------------------
+ {{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},{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
+ ARRAY[$1 /*, ... */], +|
+ ARRAY[$2 /*, ... */], +|
+ ARRAY[$3 /*, ... */], +|
+ ARRAY[$4 /*, ... */] +|
+ ] |
+ 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
@@ -409,23 +754,59 @@ FROM cte;
--------
(0 rows)
--- Simple array would be squashed 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}
+-- Rewritten as an OpExpr, so it will not be squashed
+select where '1' IN ('1'::int, '2'::int::text);
+--
+(1 row)
+
+-- Rewritten as an ArrayExpr, so it will be squashed
+select where '1' IN ('1'::int, '2'::int);
+--
(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
+ select where $1 IN ($2 /*, ... */) | 1
+ select where $1 IN ($2::int, $3::int::text) | 1
+(3 rows)
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+-- Both of these queries will be rewritten as an ArrayExpr, so they
+-- will be squashed, and have a similar queryId
+select where '1' IN ('1'::int::text, '2'::int::text);
+--
+(1 row)
+
+select where '1' = ANY (array['1'::int::text, '2'::int::text]);
+--
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls
+----------------------------------------------------+-------
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
+ select where $1 IN ($2 /*, ... */) | 2
(2 rows)
+--
+-- cleanup
+--
+DROP TABLE test_squash;
+DROP TABLE test_float;
+DROP TABLE test_squash_numeric;
+DROP TABLE test_squash_bigint;
+DROP TABLE test_squash_cast CASCADE;
+DROP TABLE test_squash_jsonb;
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index aa4f0f7e628..060d4416dd7 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -540,7 +540,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-------+------+----------------------------------------------------
2 | 0 | DEALLOCATE $1
2 | 0 | DEALLOCATE ALL
- 2 | 2 | SELECT $1 AS a
+ 2 | 2 | PREPARE stat_select AS SELECT $1 AS a
1 | 1 | SELECT $1 as a
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 129001c70c8..ecc7f2fb266 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2810,14 +2810,12 @@ generate_normalized_query(JumbleState *jstate, const char *query,
{
char *norm_query;
int query_len = *query_len_p;
- int i,
- norm_query_buflen, /* Space allowed for norm_query */
+ int norm_query_buflen, /* Space allowed for norm_query */
len_to_wrt, /* Length (in bytes) to write */
quer_loc = 0, /* Source query byte location */
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 in_squashed = false; /* in a run of squashed consts? */
int num_constants_replaced = 0;
/*
@@ -2832,16 +2830,13 @@ generate_normalized_query(JumbleState *jstate, const char *query,
* certainly isn't more than 11 bytes, even if n reaches INT_MAX. We
* could refine that limit based on the max value of n for the current
* query, but it hardly seems worth any extra effort to do so.
- *
- * Note this also gives enough room for the commented-out ", ..." list
- * syntax used by constant squashing.
*/
norm_query_buflen = query_len + jstate->clocations_count * 10;
/* Allocate result buffer */
norm_query = palloc(norm_query_buflen + 1);
- for (i = 0; i < jstate->clocations_count; i++)
+ for (int i = 0; i < jstate->clocations_count; i++)
{
int off, /* Offset from start for cur tok */
tok_len; /* Length (in bytes) of that tok */
@@ -2856,65 +2851,24 @@ generate_normalized_query(JumbleState *jstate, const char *query,
if (tok_len < 0)
continue; /* ignore any duplicates */
+ /* Copy next chunk (what precedes the next constant) */
+ len_to_wrt = off - last_off;
+ 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;
+
/*
- * What to do next depends on whether we're squashing constant lists,
- * and whether we're already in a run of such constants.
+ * And insert a param symbol in place of the constant token; and, if
+ * we have a squashable list, insert a placeholder comment starting
+ * from the list's second value.
*/
- if (!jstate->clocations[i].squashed)
- {
- /*
- * This location corresponds to a constant not to be squashed.
- * Print what comes before the constant ...
- */
- len_to_wrt = off - last_off;
- len_to_wrt -= last_tok_len;
+ n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d%s",
+ num_constants_replaced + 1 + jstate->highest_extern_param_id,
+ jstate->clocations[i].squashed ? " /*, ... */" : "");
+ num_constants_replaced++;
- Assert(len_to_wrt >= 0);
-
- memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
- n_quer_loc += len_to_wrt;
-
- /* ... and then a param symbol replacing the constant itself */
- n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d",
- num_constants_replaced++ + 1 + jstate->highest_extern_param_id);
-
- /* In case previous constants were merged away, stop doing that */
- in_squashed = false;
- }
- else if (!in_squashed)
- {
- /*
- * This location is the start position of a run of constants to be
- * squashed, so we need to print the representation of starting a
- * group of stashed constants.
- *
- * Print what comes before the constant ...
- */
- len_to_wrt = off - last_off;
- len_to_wrt -= last_tok_len;
- Assert(len_to_wrt >= 0);
- Assert(i + 1 < jstate->clocations_count);
- Assert(jstate->clocations[i + 1].squashed);
- memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
- n_quer_loc += len_to_wrt;
-
- /* ... and then start a run of squashed constants */
- n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d /*, ... */",
- num_constants_replaced++ + 1 + jstate->highest_extern_param_id);
-
- /* The next location will match the block below, to end the run */
- in_squashed = true;
- }
- else
- {
- /*
- * The second location of a run of squashable elements; this
- * indicates its end.
- */
- in_squashed = false;
- }
-
- /* Otherwise the constant is squashed away -- move forward */
+ /* move forward */
quer_loc = off + tok_len;
last_off = off;
last_tok_len = tok_len;
@@ -3005,6 +2959,9 @@ fill_in_constant_lengths(JumbleState *jstate, const char *query,
Assert(loc >= 0);
+ if (locs[i].squashed)
+ continue; /* squashable list, ignore */
+
if (loc <= last_loc)
continue; /* Duplicate constant, ignore */
diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql
index 9cfe206b3b0..46f5d9b951c 100644
--- a/contrib/pg_stat_statements/sql/planning.sql
+++ b/contrib/pg_stat_statements/sql/planning.sql
@@ -20,11 +20,11 @@ SELECT 42;
SELECT 42;
SELECT 42;
SELECT plans, calls, rows, query FROM pg_stat_statements
- WHERE query NOT LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
+ WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
-- for the prepared statement we expect at least one replan, but cache
-- invalidations could force more
SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements
- WHERE query LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C";
+ WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
-- Cleanup
DROP TABLE stats_plan_test;
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
index 189d405512f..11662cde08c 100644
--- a/contrib/pg_stat_statements/sql/select.sql
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -87,6 +87,14 @@ SELECT WHERE (1, 2) IN ((1, 2), (2, 3));
SELECT WHERE (3, 4) IN ((5, 6), (8, 7));
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- with the last element being an explicit function call with an argument, ensure
+-- the normalization of the squashing interval is correct.
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT WHERE 1 IN (1, int4(1), int4(2));
+SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2)]);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
--
-- queries with locking clauses
--
diff --git a/contrib/pg_stat_statements/sql/squashing.sql b/contrib/pg_stat_statements/sql/squashing.sql
index 03efd4b40c8..bd3243ec9cd 100644
--- a/contrib/pg_stat_statements/sql/squashing.sql
+++ b/contrib/pg_stat_statements/sql/squashing.sql
@@ -3,101 +3,160 @@
--
CREATE EXTENSION pg_stat_statements;
-CREATE TABLE test_squash (id int, data int);
+--
+-- Simple Lists
+--
--- IN queries
+CREATE TABLE test_squash (id int, data int);
--- Normal scenario, too many simple constants for an IN query
+-- single element will not be squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash WHERE id IN (1);
+SELECT ARRAY[1];
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- more than 1 element in a list will be squashed
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash WHERE id IN (1, 2, 3);
+SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4);
+SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5);
+SELECT ARRAY[1, 2, 3];
+SELECT ARRAY[1, 2, 3, 4];
+SELECT ARRAY[1, 2, 3, 4, 5];
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);
-SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
-SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+-- built-in functions will be squashed
+-- the IN and ARRAY forms of this statement will have the same queryId
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT WHERE 1 IN (1, int4(1), int4(2), 2);
+SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2), 2]);
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
--- More conditions in the query
+-- external parameters will not be squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) \bind 1 2 3 4 5
+;
+SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) \bind 1 2 3 4 5
+;
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- neither are prepared statements
+-- the IN and ARRAY forms of this statement will have the same queryId
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+PREPARE p1(int, int, int, int, int) AS
+SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5);
+EXECUTE p1(1, 2, 3, 4, 5);
+DEALLOCATE p1;
+PREPARE p1(int, int, int, int, int) AS
+SELECT * FROM test_squash WHERE id = ANY(ARRAY[$1, $2, $3, $4, $5]);
+EXECUTE p1(1, 2, 3, 4, 5);
+DEALLOCATE p1;
+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_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) AND data = 2;
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AND data = 2;
SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) AND data = 2;
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]) AND data = 2;
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AND data = 2;
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[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 squashed intervals
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-
SELECT * FROM test_squash 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_squash 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_squash 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 * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9])
+ AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]);
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10])
+ AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
+SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11])
+ AND data = ANY (ARRAY[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 squashed, even though
--- the normalized query will be the same
+-- No constants squashing for OpExpr
+-- The IN and ARRAY forms of this statement will have the same queryId
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash WHERE id IN
(1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9);
SELECT * FROM test_squash WHERE id IN
(@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9');
+SELECT * FROM test_squash WHERE id = ANY(ARRAY
+ [1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9]);
+SELECT * FROM test_squash WHERE id = ANY(ARRAY
+ [@ '-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);
+-- The casted ARRAY expressions will have the same queryId as the IN clause
+-- form of the query
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 data FROM test_float WHERE data = ANY(ARRAY['1'::double precision, '2'::double precision]);
+SELECT data FROM test_float WHERE data = ANY(ARRAY[1.0::double precision, 1.0::double precision]);
+SELECT data FROM test_float WHERE data = ANY(ARRAY[1, 2]);
+SELECT data FROM test_float WHERE data = ANY(ARRAY[1, '2']);
+SELECT data FROM test_float WHERE data = ANY(ARRAY['1', 2]);
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-- Numeric type, implicit cast is squashed
CREATE TABLE test_squash_numeric (id int, data numeric(5, 2));
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash_numeric WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[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 squashed
CREATE TABLE test_squash_bigint (id int, data bigint);
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash_bigint WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11);
+SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[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 squashed
+-- Bigint, explicit cast is squashed
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
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);
+SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[
+ 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
+-- Bigint, long tokens with parenthesis, will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash_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 * FROM test_squash_bigint WHERE id = ANY(ARRAY[
+ 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_squash_jsonb (id int, data jsonb);
+-- Multiple FuncExpr's. Will not squash
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-SELECT * FROM test_squash_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 WHERE 1 IN (1::int::bigint::int, 2::int::bigint::int);
+SELECT WHERE 1 = ANY(ARRAY[1::int::bigint::int, 2::int::bigint::int]);
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+--
-- CoerceViaIO
+--
-- Create some dummy type to force CoerceViaIO
CREATE TYPE casttesttype;
@@ -141,19 +200,73 @@ SELECT * FROM test_squash_cast WHERE data IN
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 * FROM test_squash_cast WHERE data = ANY (ARRAY
+ [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
+CREATE TABLE test_squash_jsonb (id int, data jsonb);
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT * FROM test_squash_jsonb WHERE data IN
(('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb,
- ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb,
- ( '"9"')::jsonb, ( '"10"')::jsonb);
+ ('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb,
+ ('"9"')::jsonb, ('"10"')::jsonb);
+SELECT * FROM test_squash_jsonb WHERE data = ANY (ARRAY
+ [('"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";
+-- CoerceViaIO, SubLink instead of a Const. Will not squash
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT * FROM test_squash_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 * FROM test_squash_jsonb WHERE data = ANY(ARRAY
+ [(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";
+
+-- Multiple CoerceViaIO wrapping a constant. Will not squash
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT WHERE 1 IN (1::text::int::text::int, 1::text::int::text::int);
+SELECT WHERE 1 = ANY(ARRAY[1::text::int::text::int, 1::text::int::text::int]);
+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_squash WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid);
+-- if there is only one level of RelabelType, the list will be squashable
+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);
+SELECT ARRAY[1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid];
+-- if there is at least one element with multiple levels of RelabelType,
+-- the list will not be squashable
+SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid::int::oid);
+SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::oid, 2::oid::int::oid]);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- edge cases
+--
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+-- for nested arrays, only constants are squashed
+SELECT ARRAY[
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
+ ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
+ ];
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
@@ -163,7 +276,26 @@ WITH cte AS (
SELECT ARRAY['a', 'b', 'c', const::varchar] AS result
FROM cte;
--- Simple array would be squashed as well
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
+-- Rewritten as an OpExpr, so it will not be squashed
+select where '1' IN ('1'::int, '2'::int::text);
+-- Rewritten as an ArrayExpr, so it will be squashed
+select where '1' IN ('1'::int, '2'::int);
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+-- Both of these queries will be rewritten as an ArrayExpr, so they
+-- will be squashed, and have a similar queryId
+select where '1' IN ('1'::int::text, '2'::int::text);
+select where '1' = ANY (array['1'::int::text, '2'::int::text]);
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- cleanup
+--
+DROP TABLE test_squash;
+DROP TABLE test_float;
+DROP TABLE test_squash_numeric;
+DROP TABLE test_squash_bigint;
+DROP TABLE test_squash_cast CASCADE;
+DROP TABLE test_squash_jsonb;
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-13 08:23:16 +0000