From 8948a8f94d45f134bf1047b496c4f52580c955db Mon Sep 17 00:00:00 2001 From: Anthonin Bonnefoy Date: Mon, 7 Oct 2024 10:45:49 +0200 Subject: Add tests covering pgss nested queries What pgss reports for nested statements can be confusing. Some statements like CreateTableAs, DeclareCursor and CreateMaterializedView don't jumble the nested query and thus won't report it in pgss. Explain explicitely Jumble the nested query and will appear in pgss. However, the reported query string will be the same as the top level statement. Multi statements queries are also not handled correctly. The statement length used in the top RawStmt is not propagated in the nested statement. Thus, it fallbacks to the "use the whole query string" default which is reported to pgss. This patch creates tests to cover the current behaviour. --- .../expected/level_tracking.out | 1100 +++++++++++++++++ .../pg_stat_statements/sql/level_tracking.sql | 229 +++- 2 files changed, 1328 insertions(+), 1 deletion(-) diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index bb65e98ce09..8c5c1b5b55d 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -112,6 +112,1106 @@ SELECT toplevel, calls, query FROM pg_stat_statements t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (2 rows) +-- Explain - all-level tracking. +CREATE TABLE test_table (x int); +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) SELECT 1; + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (SELECT 1, 2); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) TABLE stats_track_tab; + QUERY PLAN +----------------------------- + Seq Scan on stats_track_tab +(1 row) + +explain (costs off) (TABLE test_table); + QUERY PLAN +------------------------ + Seq Scan on test_table +(1 row) + +explain (costs off) VALUES(1); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (VALUES(1, 2)); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1; + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + +explain (costs off) DELETE FROM stats_track_tab; + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + +explain (costs off) INSERT INTO stats_track_tab VALUES ((1)); + QUERY PLAN +--------------------------- + Insert on stats_track_tab + -> Result +(2 rows) + +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); + QUERY PLAN +------------------------------------------------------- + Merge on stats_track_tab + -> Hash Right Join + Hash Cond: (stats_track_tab.x = id.id) + -> Seq Scan on stats_track_tab + -> Hash + -> Function Scan on generate_series id +(6 rows) + +explain (costs off) SELECT 1 UNION SELECT 2; + QUERY PLAN +-------------------------- + Unique + -> Sort + Sort Key: (1) + -> Append + -> Result + -> Result +(6 rows) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+------------------------------------------------------------------------------------------------------------ + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 1 | explain (costs off) (SELECT $1, $2) + 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) 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) + t | 1 | explain (costs off) SELECT $1 + t | 1 | explain (costs off) SELECT $1 UNION SELECT $2 + t | 1 | explain (costs off) TABLE stats_track_tab + t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2 + t | 1 | explain (costs off) VALUES($1) + f | 1 | explain (costs off) (SELECT $1, $2); + f | 1 | explain (costs off) (TABLE test_table); + f | 1 | explain (costs off) (VALUES($1, $2)); + f | 1 | explain (costs off) DELETE FROM stats_track_tab; + f | 1 | explain (costs off) INSERT INTO stats_track_tab VALUES (($1)); + 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); + f | 1 | explain (costs off) SELECT $1 UNION SELECT $2; + f | 1 | explain (costs off) SELECT $1; + f | 1 | explain (costs off) TABLE stats_track_tab; + f | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2; + f | 1 | explain (costs off) VALUES($1); +(23 rows) + +-- Explain - top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) SELECT 1; + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (SELECT 1, 2); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) TABLE stats_track_tab; + QUERY PLAN +----------------------------- + Seq Scan on stats_track_tab +(1 row) + +explain (costs off) (TABLE test_table); + QUERY PLAN +------------------------ + Seq Scan on test_table +(1 row) + +explain (costs off) VALUES(1); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (VALUES(1, 2)); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1; + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + +explain (costs off) DELETE FROM stats_track_tab; + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + +explain (costs off) INSERT INTO stats_track_tab VALUES ((1)); + QUERY PLAN +--------------------------- + Insert on stats_track_tab + -> Result +(2 rows) + +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); + QUERY PLAN +------------------------------------------------------- + Merge on stats_track_tab + -> Hash Right Join + Hash Cond: (stats_track_tab.x = id.id) + -> Seq Scan on stats_track_tab + -> Hash + -> Function Scan on generate_series id +(6 rows) + +explain (costs off) SELECT 1 UNION SELECT 2; + QUERY PLAN +-------------------------- + Unique + -> Sort + Sort Key: (1) + -> Append + -> Result + -> Result +(6 rows) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+------------------------------------------------------------------------------------------------------------ + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 1 | explain (costs off) (SELECT $1, $2) + 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) 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) + t | 1 | explain (costs off) SELECT $1 + t | 1 | explain (costs off) SELECT $1 UNION SELECT $2 + t | 1 | explain (costs off) TABLE stats_track_tab + t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2 + t | 1 | explain (costs off) VALUES($1) +(12 rows) + +-- Explain - all-level tracking with multi statement. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2; + QUERY PLAN +------------ + Result +(1 row) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4); + QUERY PLAN +------------ + Result +(1 row) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table); + QUERY PLAN +----------------------------- + Seq Scan on stats_track_tab +(1 row) + + QUERY PLAN +------------------------ + Seq Scan on test_table +(1 row) + +explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2)); + QUERY PLAN +------------ + Result +(1 row) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1; + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + +explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1; + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + +explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES (1), (2); + QUERY PLAN +--------------------------- + Insert on stats_track_tab + -> Result +(2 rows) + + QUERY PLAN +--------------------------------- + Insert on stats_track_tab + -> Values Scan on "*VALUES*" +(2 rows) + +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; + QUERY PLAN +------------------------------------------------------- + Merge on stats_track_tab + -> Hash Right Join + Hash Cond: (stats_track_tab.x = id.id) + -> Seq Scan on stats_track_tab + -> Hash + -> Function Scan on generate_series id +(6 rows) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; + QUERY PLAN +---------------------------- + Unique + -> Sort + Sort Key: (1), (2) + -> Append + -> Result + -> Result +(6 rows) + + QUERY PLAN +--------------------------------- + Unique + -> Sort + Sort Key: (1), (2), (3) + -> Append + -> Result + -> Result +(6 rows) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+---------------------------------------------------------------------------------------------------------------------------------- + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 1 | explain (costs off) (SELECT $1, $2, $3) + t | 1 | explain (costs off) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6 + t | 1 | explain (costs off) (SELECT $1, $2, $3, $4) + 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 + 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)) + 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) + 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 + t | 1 | explain (costs off) SELECT $1, $2, $3, $4, $5 + t | 1 | explain (costs off) TABLE stats_track_tab + 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 + t | 1 | explain (costs off) VALUES($1) + f | 1 | explain (costs off) (SELECT $1, $2, $3); 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); + 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; + 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); + 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; + 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; + 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); + 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; + 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)); +(37 rows) + +-- Explain - top-level tracking with multi statement. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2; + QUERY PLAN +------------ + Result +(1 row) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4); + QUERY PLAN +------------ + Result +(1 row) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table); + QUERY PLAN +----------------------------- + Seq Scan on stats_track_tab +(1 row) + + QUERY PLAN +------------------------ + Seq Scan on test_table +(1 row) + +explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2)); + QUERY PLAN +------------ + Result +(1 row) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1; + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + +explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1; + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + +explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES ((1), (2)); + QUERY PLAN +--------------------------- + Insert on stats_track_tab + -> Result +(2 rows) + +ERROR: INSERT has more expressions than target columns +LINE 1: ...n (costs off) INSERT INTO stats_track_tab VALUES ((1), (2)); + ^ +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; + QUERY PLAN +------------------------------------------------------- + Merge on stats_track_tab + -> Hash Right Join + Hash Cond: (stats_track_tab.x = id.id) + -> Seq Scan on stats_track_tab + -> Hash + -> Function Scan on generate_series id +(6 rows) + + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; + QUERY PLAN +---------------------------- + Unique + -> Sort + Sort Key: (1), (2) + -> Append + -> Result + -> Result +(6 rows) + + QUERY PLAN +--------------------------------- + Unique + -> Sort + Sort Key: (1), (2), (3) + -> Append + -> Result + -> Result +(6 rows) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+------------------------------------------------------------------------------------------------------------ + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 1 | explain (costs off) (SELECT $1, $2, $3) + t | 1 | explain (costs off) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6 + t | 1 | explain (costs off) (SELECT $1, $2, $3, $4) + 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 + 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 + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) + 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 + t | 1 | explain (costs off) SELECT $1, $2, $3, $4, $5 + t | 1 | explain (costs off) TABLE stats_track_tab + 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 + t | 1 | explain (costs off) VALUES($1) +(18 rows) + +-- Explain with CTE - all-level tracking +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) WITH a AS (select 4) SELECT 1; + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (WITH a AS (select 4) (SELECT 1, 2)); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1; + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + +explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab; + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + +explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1)); + QUERY PLAN +--------------------------- + Insert on stats_track_tab + -> Result +(2 rows) + +explain (costs off) WITH a AS (select 4) 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); + QUERY PLAN +------------------------------------------------------- + Merge on stats_track_tab + -> Hash Right Join + Hash Cond: (stats_track_tab.x = id.id) + -> Seq Scan on stats_track_tab + -> Hash + -> Function Scan on generate_series id +(6 rows) + +explain (costs off) WITH a AS (select 4) SELECT 1 UNION SELECT 2; + QUERY PLAN +-------------------------- + Unique + -> Sort + Sort Key: (1) + -> Append + -> Result + -> Result +(6 rows) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+---------------------------------------------------------------------------------------------------------------------------------- + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 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 + 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 + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) + t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 + t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 UNION SELECT $3 + 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) (SELECT $2, $3)); + f | 1 | explain (costs off) WITH a AS (select $1) DELETE FROM stats_track_tab; + f | 1 | explain (costs off) WITH a AS (select $1) INSERT INTO stats_track_tab VALUES (($2)); + 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); + f | 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; + f | 1 | explain (costs off) WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3; +(15 rows) + +-- Explain with CTE - top-level tracking +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) WITH a AS (select 4) SELECT 1; + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) (WITH a AS (select 4) (SELECT 1, 2)); + QUERY PLAN +------------ + Result +(1 row) + +explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1; + QUERY PLAN +----------------------------------- + Update on stats_track_tab + -> Seq Scan on stats_track_tab + Filter: (x = 1) +(3 rows) + +explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab; + QUERY PLAN +----------------------------------- + Delete on stats_track_tab + -> Seq Scan on stats_track_tab +(2 rows) + +explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1)); + QUERY PLAN +--------------------------- + Insert on stats_track_tab + -> Result +(2 rows) + +explain (costs off) WITH a AS (select 4) 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); + QUERY PLAN +------------------------------------------------------- + Merge on stats_track_tab + -> Hash Right Join + Hash Cond: (stats_track_tab.x = id.id) + -> Seq Scan on stats_track_tab + -> Hash + -> Function Scan on generate_series id +(6 rows) + +explain (costs off) WITH a AS (select 4) SELECT 1 UNION SELECT 2; + QUERY PLAN +-------------------------- + Unique + -> Sort + Sort Key: (1) + -> Append + -> Result + -> Result +(6 rows) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+---------------------------------------------------------------------------------------------------------------------------------- + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 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 + 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 + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) + t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 + t | 1 | explain (costs off) WITH a AS (select $1) SELECT $2 UNION SELECT $3 + t | 1 | explain (costs off) WITH a AS (select $1) UPDATE stats_track_tab SET x=$2 WHERE x=$3 +(8 rows) + +-- Explain analyze, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100; + QUERY PLAN +-------------------------------- + Result (actual rows=1 loops=1) +(1 row) + +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; + QUERY PLAN +----------------------------------------------------- + Seq Scan on stats_track_tab (actual rows=0 loops=1) +(1 row) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+--------------------------------------------------------------------------------------------------------------- + t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab + t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1 + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1; +(4 rows) + +-- Explain analyze, top tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100; + QUERY PLAN +-------------------------------- + Result (actual rows=1 loops=1) +(1 row) + +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; + QUERY PLAN +----------------------------------------------------- + Seq Scan on stats_track_tab (actual rows=0 loops=1) +(1 row) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+--------------------------------------------------------------------------------------------------------------- + t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab + t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT $1 + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(3 rows) + +-- Create Materialized View, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +CREATE MATERIALIZED VIEW pgss_materialized_view AS SELECT * FROM generate_series(1, 5) as id; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+------------------------------------------------------------------------------------------------ + t | 1 | CREATE MATERIALIZED VIEW pgss_materialized_view AS SELECT * FROM generate_series($1, $2) as id + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- Create Materialized View, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS SELECT * FROM generate_series(1, 5) as id; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+-------------------------------------------------------------------------------------------------- + t | 1 | CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS SELECT * FROM generate_series($1, $2) as id + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- Refresh Materialized View, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +REFRESH MATERIALIZED VIEW pgss_materialized_view; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+---------------------------------------------------- + t | 1 | REFRESH MATERIALIZED VIEW pgss_materialized_view + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- Refresh Materialized View, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +REFRESH MATERIALIZED VIEW pgss_materialized_view; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+---------------------------------------------------- + t | 1 | REFRESH MATERIALIZED VIEW pgss_materialized_view + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- Create Table As, all-level tracking. +SET pg_stat_statements.track = 'all'; +PREPARE test_prepare_pgss AS select generate_series(1, 10); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT 1; +CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+----------------------------------------------------------------- + t | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1 + t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + f | 1 | PREPARE test_prepare_pgss AS select generate_series($1, $2) +(4 rows) + +-- Create Table As, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT 1; +CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+----------------------------------------------------------------- + t | 1 | CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT $1 + t | 1 | CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(3 rows) + +-- Explain with Create Table As - all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1; + QUERY PLAN +------------ + Result +(1 row) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+--------------------------------------------------------------------------- + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 1 | explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1 +(2 rows) + +-- Explain with Create Table As - top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1; + QUERY PLAN +------------ + Result +(1 row) + +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+--------------------------------------------------------------------------- + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + t | 1 | explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1 +(2 rows) + +-- Declare cursor, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; +FETCH FORWARD 1 FROM foocur; + x +--- +(0 rows) + +CLOSE foocur; +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + 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 + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(6 rows) + +-- Declare cursor, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; +FETCH FORWARD 1 FROM foocur; + x +--- +(0 rows) + +CLOSE foocur; +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + 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 + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(6 rows) + +-- COPY - all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +COPY (SELECT 1) to stdout; +1 +COPY (SELECT 1 UNION SELECT 2) to stdout; +1 +2 +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; +1 +COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout; +1 +COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout; +2 +2 +COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout; +2 +2 +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+-------------------------------------------------------------------------- + t | 1 | COPY (DELETE FROM stats_track_tab WHERE x=2 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 + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout + t | 1 | COPY (SELECT 1 UNION SELECT 2) to stdout + t | 1 | COPY (SELECT 1) to stdout + t | 1 | COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + f | 1 | COPY (DELETE FROM stats_track_tab WHERE x=$1 RETURNING x) to stdout + f | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x) to stdout + 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 + f | 1 | COPY (SELECT $1 UNION SELECT $2) to stdout + f | 1 | COPY (SELECT $1) to stdout + f | 1 | COPY (UPDATE stats_track_tab SET x=$1 WHERE x=$2 RETURNING x) to stdout +(13 rows) + +-- COPY - top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +COPY (SELECT 1) to stdout; +1 +COPY (SELECT 1 UNION SELECT 2) to stdout; +1 +2 +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; +1 +COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout; +1 +COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout; +2 +2 +COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout; +2 +2 +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + toplevel | calls | query +----------+-------+------------------------------------------------------------------------- + t | 1 | COPY (DELETE FROM stats_track_tab WHERE x=2 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 + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) to stdout + t | 1 | COPY (SELECT 1 UNION SELECT 2) to stdout + t | 1 | COPY (SELECT 1) to stdout + t | 1 | COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout + t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(7 rows) + -- DO block - top-level tracking without utility. SET pg_stat_statements.track = 'top'; SET pg_stat_statements.track_utility = FALSE; diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql index 65a17147a5a..87aee416d1f 100644 --- a/contrib/pg_stat_statements/sql/level_tracking.sql +++ b/contrib/pg_stat_statements/sql/level_tracking.sql @@ -32,7 +32,6 @@ BEGIN END; $$; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", toplevel; - -- Procedure with multiple utility statements. CREATE OR REPLACE PROCEDURE proc_with_utility_stmt() LANGUAGE SQL @@ -55,6 +54,234 @@ CALL proc_with_utility_stmt(); SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", toplevel; +-- Explain - all-level tracking. +CREATE TABLE test_table (x int); +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) SELECT 1; +explain (costs off) (SELECT 1, 2); +explain (costs off) TABLE stats_track_tab; +explain (costs off) (TABLE test_table); +explain (costs off) VALUES(1); +explain (costs off) (VALUES(1, 2)); +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1; +explain (costs off) DELETE FROM stats_track_tab; +explain (costs off) INSERT INTO stats_track_tab VALUES ((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 UNION SELECT 2; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain - top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) SELECT 1; +explain (costs off) (SELECT 1, 2); +explain (costs off) TABLE stats_track_tab; +explain (costs off) (TABLE test_table); +explain (costs off) VALUES(1); +explain (costs off) (VALUES(1, 2)); +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1; +explain (costs off) DELETE FROM stats_track_tab; +explain (costs off) INSERT INTO stats_track_tab VALUES ((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 UNION SELECT 2; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain - all-level tracking with multi statement. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2; +explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4); +explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table); +explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2)); +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1; +explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1; +explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES (1), (2); +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; +explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain - top-level tracking with multi statement. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) SELECT 1\; explain (costs off) SELECT 1, 2; +explain (costs off) (SELECT 1, 2, 3)\; explain (costs off) (SELECT 1, 2, 3, 4); +explain (costs off) TABLE stats_track_tab\; explain (costs off) (TABLE test_table); +explain (costs off) VALUES(1)\; explain (costs off) (VALUES(1, 2)); +explain (costs off) UPDATE stats_track_tab SET x=1 WHERE x=1\; explain (costs off) UPDATE stats_track_tab SET x=1; +explain (costs off) DELETE FROM stats_track_tab\; explain (costs off) DELETE FROM stats_track_tab WHERE x=1; +explain (costs off) INSERT INTO stats_track_tab VALUES ((1))\; explain (costs off) INSERT INTO stats_track_tab VALUES ((1), (2)); +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; +explain (costs off) SELECT 1, 2 UNION SELECT 3, 4\; explain (costs off) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain with CTE - all-level tracking +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) WITH a AS (select 4) SELECT 1; +explain (costs off) (WITH a AS (select 4) (SELECT 1, 2)); +explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1; +explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab; +explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1)); +explain (costs off) WITH a AS (select 4) 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) WITH a AS (select 4) SELECT 1 UNION SELECT 2; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain with CTE - top-level tracking +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) WITH a AS (select 4) SELECT 1; +explain (costs off) (WITH a AS (select 4) (SELECT 1, 2)); +explain (costs off) WITH a AS (select 4) UPDATE stats_track_tab SET x=1 WHERE x=1; +explain (costs off) WITH a AS (select 4) DELETE FROM stats_track_tab; +explain (costs off) WITH a AS (select 4) INSERT INTO stats_track_tab VALUES ((1)); +explain (costs off) WITH a AS (select 4) 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) WITH a AS (select 4) SELECT 1 UNION SELECT 2; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain analyze, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain analyze, top tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT 100; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Create Materialized View, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +CREATE MATERIALIZED VIEW pgss_materialized_view AS SELECT * FROM generate_series(1, 5) as id; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Create Materialized View, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +CREATE MATERIALIZED VIEW pgss_materialized_view_2 AS SELECT * FROM generate_series(1, 5) as id; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Refresh Materialized View, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +REFRESH MATERIALIZED VIEW pgss_materialized_view; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Refresh Materialized View, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +REFRESH MATERIALIZED VIEW pgss_materialized_view; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Create Table As, all-level tracking. +SET pg_stat_statements.track = 'all'; +PREPARE test_prepare_pgss AS select generate_series(1, 10); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT 1; +CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Create Table As, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +CREATE TEMPORARY TABLE pgss_ctas_3 AS SELECT 1; +CREATE TEMPORARY TABLE pgss_ctas_4 AS EXECUTE test_prepare_pgss; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain with Create Table As - all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Explain with Create Table As - top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +explain (costs off) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Declare cursor, all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; +FETCH FORWARD 1 FROM foocur; +CLOSE foocur; +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- Declare cursor, top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; +FETCH FORWARD 1 FROM foocur; +CLOSE foocur; +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- COPY - all-level tracking. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +COPY (SELECT 1) to stdout; +COPY (SELECT 1 UNION SELECT 2) to stdout; +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; +COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout; +COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout; +COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + +-- COPY - top-level tracking. +SET pg_stat_statements.track = 'top'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +COPY (SELECT 1) to stdout; +COPY (SELECT 1 UNION SELECT 2) to stdout; +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; +COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) to stdout; +COPY (UPDATE stats_track_tab SET x=2 WHERE x=1 RETURNING x) to stdout; +COPY (DELETE FROM stats_track_tab WHERE x=2 RETURNING x) to stdout; +SELECT toplevel, calls, query FROM pg_stat_statements + ORDER BY toplevel desc, query COLLATE "C"; + -- DO block - top-level tracking without utility. SET pg_stat_statements.track = 'top'; SET pg_stat_statements.track_utility = FALSE; -- 2.39.5 (Apple Git-154)