v6-0001-Add-tests-covering-pgss-nested-queries.patch
application/octet-stream
Filename: v6-0001-Add-tests-covering-pgss-nested-queries.patch
Type: application/octet-stream
Part: 2
Patch
Format: format-patch
Series: patch v6-0001
Subject: Add tests covering pgss nested queries
| File | + | − |
|---|---|---|
| contrib/pg_stat_statements/expected/level_tracking.out | 895 | 0 |
| contrib/pg_stat_statements/sql/level_tracking.sql | 187 | 1 |
From e232cc34249af05c9670544fe919843d3b9f6789 Mon Sep 17 00:00:00 2001 From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com> 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 | 895 ++++++++++++++++++ .../pg_stat_statements/sql/level_tracking.sql | 188 +++- 2 files changed, 1082 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..21a6c3ba7bd 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -112,6 +112,901 @@ 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. +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) 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) 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) UPDATE stats_track_tab SET x=$1 WHERE x=$2 + f | 1 | explain (costs off) (SELECT $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) UPDATE stats_track_tab SET x=$1 WHERE x=$2; +(15 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) 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) 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) UPDATE stats_track_tab SET x=$1 WHERE x=$2 +(8 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) 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) 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) 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) (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) 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; +(29 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) 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) 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) UPDATE stats_track_tab SET x=$1 + t | 1 | explain (costs off) UPDATE stats_track_tab SET x=$1 WHERE x=$2 +(14 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) + -- 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..b72964aa305 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,193 @@ CALL proc_with_utility_stmt(); SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", toplevel; +-- Explain - all-level tracking. +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) 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) 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) 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) 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"; + -- 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)