diff options
Diffstat (limited to 'contrib/pg_stat_statements/expected')
| -rw-r--r-- | contrib/pg_stat_statements/expected/cursors.out | 141 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/extended.out | 94 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/level_tracking.out | 237 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/planning.out | 10 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/select.out | 61 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/squashing.out | 543 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/utility.out | 4 |
7 files changed, 912 insertions, 178 deletions
diff --git a/contrib/pg_stat_statements/expected/cursors.out b/contrib/pg_stat_statements/expected/cursors.out index 0fc4b2c098d..6afb48ace92 100644 --- a/contrib/pg_stat_statements/expected/cursors.out +++ b/contrib/pg_stat_statements/expected/cursors.out @@ -57,8 +57,8 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 0 | COMMIT 1 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1 - 1 | 1 | FETCH 1 IN cursor_stats_1 - 1 | 1 | FETCH 1 IN cursor_stats_2 + 1 | 1 | FETCH $1 IN cursor_stats_1 + 1 | 1 | FETCH $1 IN cursor_stats_2 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (9 rows) @@ -68,3 +68,140 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t; t (1 row) +-- Normalization of FETCH statements +BEGIN; +DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series(1, 10); +-- implicit directions +FETCH pgss_cursor; +-- +(1 row) + +FETCH 1 pgss_cursor; +-- +(1 row) + +FETCH 2 pgss_cursor; +-- +(2 rows) + +FETCH -1 pgss_cursor; +-- +(1 row) + +-- explicit NEXT +FETCH NEXT pgss_cursor; +-- +(1 row) + +-- explicit PRIOR +FETCH PRIOR pgss_cursor; +-- +(1 row) + +-- explicit FIRST +FETCH FIRST pgss_cursor; +-- +(1 row) + +-- explicit LAST +FETCH LAST pgss_cursor; +-- +(1 row) + +-- explicit ABSOLUTE +FETCH ABSOLUTE 1 pgss_cursor; +-- +(1 row) + +FETCH ABSOLUTE 2 pgss_cursor; +-- +(1 row) + +FETCH ABSOLUTE -1 pgss_cursor; +-- +(1 row) + +-- explicit RELATIVE +FETCH RELATIVE 1 pgss_cursor; +-- +(0 rows) + +FETCH RELATIVE 2 pgss_cursor; +-- +(0 rows) + +FETCH RELATIVE -1 pgss_cursor; +-- +(1 row) + +-- explicit FORWARD +FETCH ALL pgss_cursor; +-- +(0 rows) + +-- explicit FORWARD ALL +FETCH FORWARD ALL pgss_cursor; +-- +(0 rows) + +-- explicit FETCH FORWARD +FETCH FORWARD pgss_cursor; +-- +(0 rows) + +FETCH FORWARD 1 pgss_cursor; +-- +(0 rows) + +FETCH FORWARD 2 pgss_cursor; +-- +(0 rows) + +FETCH FORWARD -1 pgss_cursor; +-- +(1 row) + +-- explicit FETCH BACKWARD +FETCH BACKWARD pgss_cursor; +-- +(1 row) + +FETCH BACKWARD 1 pgss_cursor; +-- +(1 row) + +FETCH BACKWARD 2 pgss_cursor; +-- +(2 rows) + +FETCH BACKWARD -1 pgss_cursor; +-- +(1 row) + +-- explicit BACKWARD ALL +FETCH BACKWARD ALL pgss_cursor; +-- +(6 rows) + +COMMIT; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | query +-------+-------------------------------------------------------------------- + 1 | BEGIN + 1 | COMMIT + 1 | DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series($1, $2) + 3 | FETCH ABSOLUTE $1 pgss_cursor + 1 | FETCH ALL pgss_cursor + 1 | FETCH BACKWARD ALL pgss_cursor + 4 | FETCH BACKWARD pgss_cursor + 1 | FETCH FIRST pgss_cursor + 1 | FETCH FORWARD ALL pgss_cursor + 4 | FETCH FORWARD pgss_cursor + 1 | FETCH LAST pgss_cursor + 1 | FETCH NEXT pgss_cursor + 1 | FETCH PRIOR pgss_cursor + 3 | FETCH RELATIVE $1 pgss_cursor + 4 | FETCH pgss_cursor + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(16 rows) + diff --git a/contrib/pg_stat_statements/expected/extended.out b/contrib/pg_stat_statements/expected/extended.out index 04a05943372..1bfd0c1ca24 100644 --- a/contrib/pg_stat_statements/expected/extended.out +++ b/contrib/pg_stat_statements/expected/extended.out @@ -68,3 +68,97 @@ 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 (4 rows) +-- Various parameter numbering patterns +-- Unique query IDs with parameter numbers switched. +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT WHERE ($1::int, 7) IN ((8, $2::int), ($3::int, 9)) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE ($2::int, 10) IN ((11, $3::int), ($1::int, 12)) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE $1::int IN ($2::int, $3::int) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE $2::int IN ($3::int, $1::int) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT WHERE $3::int IN ($1::int, $2::int) \bind '1' '2' '3' \g +-- +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +--------------------------------------------------------------+------- + SELECT WHERE $1::int IN ($2 /*, ... */) | 1 + SELECT WHERE $1::int IN ($2 /*, ... */) | 1 + SELECT WHERE $1::int IN ($2 /*, ... */) | 1 + SELECT WHERE ($1::int, $4) IN (($5, $2::int), ($3::int, $6)) | 1 + SELECT WHERE ($2::int, $4) IN (($5, $3::int), ($1::int, $6)) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(6 rows) + +-- Two groups of two queries with the same query ID. +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT WHERE '1'::int IN ($1::int, '2'::int) \bind '1' \g +-- +(1 row) + +SELECT WHERE '4'::int IN ($1::int, '5'::int) \bind '2' \g +-- +(0 rows) + +SELECT WHERE $2::int IN ($1::int, '1'::int) \bind '1' '2' \g +-- +(0 rows) + +SELECT WHERE $2::int IN ($1::int, '2'::int) \bind '3' '4' \g +-- +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------+------- + SELECT WHERE $1::int IN ($2 /*, ... */) | 2 + SELECT WHERE $1::int IN ($2 /*, ... */) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- no squashable list, the parameters id's are kept as-is +SELECT WHERE $3 = $1 AND $2 = $4 \bind 1 2 1 2 \g +-- +(1 row) + +-- squashable list, so the parameter IDs will be re-assigned +SELECT WHERE 1 IN (1, 2, 3) AND $3 = $1 AND $2 = $4 \bind 1 2 1 2 \g +-- +(1 row) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------+------- + SELECT WHERE $1 IN ($2 /*, ... */) AND $3 = $4 AND $5 = $6 | 1 + SELECT WHERE $3 = $1 AND $2 = $4 | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index 03bea14d5da..8e8388dd5cb 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 - t | 1 | FETCH FORWARD 1 FROM foocur - f | 1 | SELECT * from stats_track_tab + f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; + t | 1 | FETCH FORWARD $1 FROM foocur t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (7 rows) @@ -1172,7 +1176,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements t | 1 | CLOSE foocur t | 1 | COMMIT t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab - t | 1 | FETCH FORWARD 1 FROM foocur + t | 1 | FETCH FORWARD $1 FROM foocur t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (6 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. @@ -1319,6 +1323,57 @@ SELECT toplevel, calls, query FROM pg_stat_statements t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (4 rows) +-- DO block --- multiple inner queries with separators +SET pg_stat_statements.track = 'all'; +SET pg_stat_statements.track_utility = TRUE; +CREATE TABLE pgss_do_util_tab_1 (a int); +CREATE TABLE pgss_do_util_tab_2 (a int); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +DO $$ +DECLARE BEGIN + EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table'; + EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2'; +END $$; +SELECT toplevel, calls, rows, query FROM pg_stat_statements + WHERE toplevel IS FALSE + ORDER BY query COLLATE "C"; + toplevel | calls | rows | query +----------+-------+------+------------------------------------- + f | 1 | 0 | CREATE TABLE pgss_do_table (id INT) + f | 1 | 0 | DROP TABLE pgss_do_table + f | 1 | 0 | SELECT a FROM pgss_do_util_tab_1 + f | 1 | 0 | SELECT a FROM pgss_do_util_tab_2 +(4 rows) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- Note the extra semicolon at the end of the query. +DO $$ +DECLARE BEGIN + EXECUTE 'CREATE TABLE pgss_do_table (id INT); DROP TABLE pgss_do_table;'; + EXECUTE 'SELECT a FROM pgss_do_util_tab_1; SELECT a FROM pgss_do_util_tab_2;'; +END $$; +SELECT toplevel, calls, rows, query FROM pg_stat_statements + WHERE toplevel IS FALSE + ORDER BY query COLLATE "C"; + toplevel | calls | rows | query +----------+-------+------+------------------------------------- + f | 1 | 0 | CREATE TABLE pgss_do_table (id INT) + f | 1 | 0 | DROP TABLE pgss_do_table + f | 1 | 0 | SELECT a FROM pgss_do_util_tab_1 + f | 1 | 0 | SELECT a FROM pgss_do_util_tab_2 +(4 rows) + +DROP TABLE pgss_do_util_tab_1, pgss_do_util_tab_2; -- PL/pgSQL function - top-level tracking. SET pg_stat_statements.track = 'top'; SET pg_stat_statements.track_utility = FALSE; 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 09476a7b699..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 @@ -238,6 +238,65 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t; t (1 row) +-- normalization of constants and parameters, with constant locations +-- recorded one or more times. +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT WHERE '1' IN ('1'::int, '3'::int::text); +-- +(1 row) + +SELECT WHERE (1, 2) IN ((1, 2), (2, 3)); +-- +(1 row) + +SELECT WHERE (3, 4) IN ((5, 6), (8, 7)); +-- +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT WHERE $1 IN ($2::int, $3::int::text) | 1 + SELECT WHERE ($1, $2) IN (($3, $4), ($5, $6)) | 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 +(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..f952f47ef7b 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 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 /*, ... */) | 1 + SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1 /*, ... */]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- prepared statements will also 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) + +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 /*, ... */) | + 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,152 @@ 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 are squashed +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 /*, ... */) | 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); +-- However many layers of RelabelType there are, 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) + +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) + +-- RelabelType together with CoerceViaIO is also squashable +SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::oid::text::int::oid, 2::oid::int::oid]); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::text::int::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 +| 5 + ($1 /*, ... */) | + SELECT ARRAY[$1 /*, ... */] | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 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 +762,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 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 ARRAY[$1 /*, ... */] | 1 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..e4d6564ea5b 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) @@ -702,7 +702,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 13 | CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas 1 | 10 | CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_series($2, $3) a 1 | 0 | DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv - 1 | 5 | FETCH FORWARD 5 pgss_cursor + 1 | 5 | FETCH FORWARD $1 pgss_cursor 1 | 7 | FETCH FORWARD ALL pgss_cursor 1 | 1 | FETCH NEXT pgss_cursor 1 | 13 | REFRESH MATERIALIZED VIEW pgss_matv |