From bee23ea4ddc46198c95a4e73a83f453c09e04bf8 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Wed, 2 Jul 2025 08:39:25 +0900 Subject: [PATCH] Show sizes of FETCH queries as constants in pg_stat_statements Prior to this patch, every FETCH call would generate a unique queryId with a different size specified. Depending on the workloads, this could lead to a significant bloat in pg_stat_statements, as repeatedly calling a specific cursor would result in a new queryId each time. For example, FETCH 1 c1; and FETCH 2 c1; would produce different queryIds. This patch improves the situation by normalizing the fetch size, so as semantically similar statements generate the same queryId. As a result, statements like the below, which differ syntactically but have the same effect, will now share a single queryId: FETCH FROM c1 FETCH NEXT c1 FETCH 1 c1 In order to do a normalization based on the keyword used in FETCH, FetchStmt is tweaked with a new FetchDirectionKeywords. This matters for "howMany", which could be set to a negative value depending on the direction, and we want to normalize the queries with enough information about the direction keywords provided, including RELATIVE, ABSOLUTE or all the ALL variants. Author: Sami Imseih Discussion: https://postgr.es/m/CAA5RZ0tA6LbHCg2qSS+KuM850BZC_+ZgHV7Ug6BXw22TNyF+MA@mail.gmail.com --- .../pg_stat_statements/expected/cursors.out | 141 +++++++++++++++++- .../expected/level_tracking.out | 4 +- .../pg_stat_statements/expected/utility.out | 2 +- contrib/pg_stat_statements/sql/cursors.sql | 43 ++++++ src/backend/parser/gram.y | 50 +++++-- src/include/nodes/parsenodes.h | 35 ++++- src/tools/pgindent/typedefs.list | 1 + 7 files changed, 259 insertions(+), 17 deletions(-) diff --git a/contrib/pg_stat_statements/expected/cursors.out b/contrib/pg_stat_statements/expected/cursors.out index 0fc4b2c098d..6afb48ace92 100644 --- a/contrib/pg_stat_statements/expected/cursors.out +++ b/contrib/pg_stat_statements/expected/cursors.out @@ -57,8 +57,8 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 0 | COMMIT 1 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1 - 1 | 1 | FETCH 1 IN cursor_stats_1 - 1 | 1 | FETCH 1 IN cursor_stats_2 + 1 | 1 | FETCH $1 IN cursor_stats_1 + 1 | 1 | FETCH $1 IN cursor_stats_2 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (9 rows) @@ -68,3 +68,140 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t; t (1 row) +-- Normalization of FETCH statements +BEGIN; +DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series(1, 10); +-- implicit directions +FETCH pgss_cursor; +-- +(1 row) + +FETCH 1 pgss_cursor; +-- +(1 row) + +FETCH 2 pgss_cursor; +-- +(2 rows) + +FETCH -1 pgss_cursor; +-- +(1 row) + +-- explicit NEXT +FETCH NEXT pgss_cursor; +-- +(1 row) + +-- explicit PRIOR +FETCH PRIOR pgss_cursor; +-- +(1 row) + +-- explicit FIRST +FETCH FIRST pgss_cursor; +-- +(1 row) + +-- explicit LAST +FETCH LAST pgss_cursor; +-- +(1 row) + +-- explicit ABSOLUTE +FETCH ABSOLUTE 1 pgss_cursor; +-- +(1 row) + +FETCH ABSOLUTE 2 pgss_cursor; +-- +(1 row) + +FETCH ABSOLUTE -1 pgss_cursor; +-- +(1 row) + +-- explicit RELATIVE +FETCH RELATIVE 1 pgss_cursor; +-- +(0 rows) + +FETCH RELATIVE 2 pgss_cursor; +-- +(0 rows) + +FETCH RELATIVE -1 pgss_cursor; +-- +(1 row) + +-- explicit FORWARD +FETCH ALL pgss_cursor; +-- +(0 rows) + +-- explicit FORWARD ALL +FETCH FORWARD ALL pgss_cursor; +-- +(0 rows) + +-- explicit FETCH FORWARD +FETCH FORWARD pgss_cursor; +-- +(0 rows) + +FETCH FORWARD 1 pgss_cursor; +-- +(0 rows) + +FETCH FORWARD 2 pgss_cursor; +-- +(0 rows) + +FETCH FORWARD -1 pgss_cursor; +-- +(1 row) + +-- explicit FETCH BACKWARD +FETCH BACKWARD pgss_cursor; +-- +(1 row) + +FETCH BACKWARD 1 pgss_cursor; +-- +(1 row) + +FETCH BACKWARD 2 pgss_cursor; +-- +(2 rows) + +FETCH BACKWARD -1 pgss_cursor; +-- +(1 row) + +-- explicit BACKWARD ALL +FETCH BACKWARD ALL pgss_cursor; +-- +(6 rows) + +COMMIT; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | query +-------+-------------------------------------------------------------------- + 1 | BEGIN + 1 | COMMIT + 1 | DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series($1, $2) + 3 | FETCH ABSOLUTE $1 pgss_cursor + 1 | FETCH ALL pgss_cursor + 1 | FETCH BACKWARD ALL pgss_cursor + 4 | FETCH BACKWARD pgss_cursor + 1 | FETCH FIRST pgss_cursor + 1 | FETCH FORWARD ALL pgss_cursor + 4 | FETCH FORWARD pgss_cursor + 1 | FETCH LAST pgss_cursor + 1 | FETCH NEXT pgss_cursor + 1 | FETCH PRIOR pgss_cursor + 3 | FETCH RELATIVE $1 pgss_cursor + 4 | FETCH pgss_cursor + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(16 rows) + diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index 8213fcd2e61..8e8388dd5cb 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -1147,7 +1147,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements t | 1 | COMMIT t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; - t | 1 | FETCH FORWARD 1 FROM foocur + t | 1 | FETCH FORWARD $1 FROM foocur t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (7 rows) @@ -1176,7 +1176,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements t | 1 | CLOSE foocur t | 1 | COMMIT t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab - t | 1 | FETCH FORWARD 1 FROM foocur + t | 1 | FETCH FORWARD $1 FROM foocur t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (6 rows) diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out index 060d4416dd7..e4d6564ea5b 100644 --- a/contrib/pg_stat_statements/expected/utility.out +++ b/contrib/pg_stat_statements/expected/utility.out @@ -702,7 +702,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 13 | CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas 1 | 10 | CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_series($2, $3) a 1 | 0 | DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv - 1 | 5 | FETCH FORWARD 5 pgss_cursor + 1 | 5 | FETCH FORWARD $1 pgss_cursor 1 | 7 | FETCH FORWARD ALL pgss_cursor 1 | 1 | FETCH NEXT pgss_cursor 1 | 13 | REFRESH MATERIALIZED VIEW pgss_matv diff --git a/contrib/pg_stat_statements/sql/cursors.sql b/contrib/pg_stat_statements/sql/cursors.sql index 61738ac470e..78bb4228433 100644 --- a/contrib/pg_stat_statements/sql/cursors.sql +++ b/contrib/pg_stat_statements/sql/cursors.sql @@ -28,3 +28,46 @@ COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT pg_stat_statements_reset() IS NOT NULL AS t; + +-- Normalization of FETCH statements +BEGIN; +DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series(1, 10); +-- implicit directions +FETCH pgss_cursor; +FETCH 1 pgss_cursor; +FETCH 2 pgss_cursor; +FETCH -1 pgss_cursor; +-- explicit NEXT +FETCH NEXT pgss_cursor; +-- explicit PRIOR +FETCH PRIOR pgss_cursor; +-- explicit FIRST +FETCH FIRST pgss_cursor; +-- explicit LAST +FETCH LAST pgss_cursor; +-- explicit ABSOLUTE +FETCH ABSOLUTE 1 pgss_cursor; +FETCH ABSOLUTE 2 pgss_cursor; +FETCH ABSOLUTE -1 pgss_cursor; +-- explicit RELATIVE +FETCH RELATIVE 1 pgss_cursor; +FETCH RELATIVE 2 pgss_cursor; +FETCH RELATIVE -1 pgss_cursor; +-- explicit FORWARD +FETCH ALL pgss_cursor; +-- explicit FORWARD ALL +FETCH FORWARD ALL pgss_cursor; +-- explicit FETCH FORWARD +FETCH FORWARD pgss_cursor; +FETCH FORWARD 1 pgss_cursor; +FETCH FORWARD 2 pgss_cursor; +FETCH FORWARD -1 pgss_cursor; +-- explicit FETCH BACKWARD +FETCH BACKWARD pgss_cursor; +FETCH BACKWARD 1 pgss_cursor; +FETCH BACKWARD 2 pgss_cursor; +FETCH BACKWARD -1 pgss_cursor; +-- explicit BACKWARD ALL +FETCH BACKWARD ALL pgss_cursor; +COMMIT; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 50f53159d58..1c11b235aa6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -7477,6 +7477,8 @@ fetch_args: cursor_name n->portalname = $1; n->direction = FETCH_FORWARD; n->howMany = 1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_NONE; $$ = (Node *) n; } | from_in cursor_name @@ -7486,6 +7488,19 @@ fetch_args: cursor_name n->portalname = $2; n->direction = FETCH_FORWARD; n->howMany = 1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_NONE; + $$ = (Node *) n; + } + | SignedIconst opt_from_in cursor_name + { + FetchStmt *n = makeNode(FetchStmt); + + n->portalname = $3; + n->direction = FETCH_FORWARD; + n->howMany = $1; + n->location = @1; + n->direction_keyword = FETCH_KEYWORD_NONE; $$ = (Node *) n; } | NEXT opt_from_in cursor_name @@ -7495,6 +7510,8 @@ fetch_args: cursor_name n->portalname = $3; n->direction = FETCH_FORWARD; n->howMany = 1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_NEXT; $$ = (Node *) n; } | PRIOR opt_from_in cursor_name @@ -7504,6 +7521,8 @@ fetch_args: cursor_name n->portalname = $3; n->direction = FETCH_BACKWARD; n->howMany = 1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_PRIOR; $$ = (Node *) n; } | FIRST_P opt_from_in cursor_name @@ -7513,6 +7532,8 @@ fetch_args: cursor_name n->portalname = $3; n->direction = FETCH_ABSOLUTE; n->howMany = 1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_FIRST; $$ = (Node *) n; } | LAST_P opt_from_in cursor_name @@ -7522,6 +7543,8 @@ fetch_args: cursor_name n->portalname = $3; n->direction = FETCH_ABSOLUTE; n->howMany = -1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_LAST; $$ = (Node *) n; } | ABSOLUTE_P SignedIconst opt_from_in cursor_name @@ -7531,6 +7554,8 @@ fetch_args: cursor_name n->portalname = $4; n->direction = FETCH_ABSOLUTE; n->howMany = $2; + n->location = @2; + n->direction_keyword = FETCH_KEYWORD_ABSOLUTE; $$ = (Node *) n; } | RELATIVE_P SignedIconst opt_from_in cursor_name @@ -7540,15 +7565,8 @@ fetch_args: cursor_name n->portalname = $4; n->direction = FETCH_RELATIVE; n->howMany = $2; - $$ = (Node *) n; - } - | SignedIconst opt_from_in cursor_name - { - FetchStmt *n = makeNode(FetchStmt); - - n->portalname = $3; - n->direction = FETCH_FORWARD; - n->howMany = $1; + n->location = @2; + n->direction_keyword = FETCH_KEYWORD_RELATIVE; $$ = (Node *) n; } | ALL opt_from_in cursor_name @@ -7558,6 +7576,8 @@ fetch_args: cursor_name n->portalname = $3; n->direction = FETCH_FORWARD; n->howMany = FETCH_ALL; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_ALL; $$ = (Node *) n; } | FORWARD opt_from_in cursor_name @@ -7567,6 +7587,8 @@ fetch_args: cursor_name n->portalname = $3; n->direction = FETCH_FORWARD; n->howMany = 1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_FORWARD; $$ = (Node *) n; } | FORWARD SignedIconst opt_from_in cursor_name @@ -7576,6 +7598,8 @@ fetch_args: cursor_name n->portalname = $4; n->direction = FETCH_FORWARD; n->howMany = $2; + n->location = @2; + n->direction_keyword = FETCH_KEYWORD_FORWARD; $$ = (Node *) n; } | FORWARD ALL opt_from_in cursor_name @@ -7585,6 +7609,8 @@ fetch_args: cursor_name n->portalname = $4; n->direction = FETCH_FORWARD; n->howMany = FETCH_ALL; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_FORWARD_ALL; $$ = (Node *) n; } | BACKWARD opt_from_in cursor_name @@ -7594,6 +7620,8 @@ fetch_args: cursor_name n->portalname = $3; n->direction = FETCH_BACKWARD; n->howMany = 1; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_BACKWARD; $$ = (Node *) n; } | BACKWARD SignedIconst opt_from_in cursor_name @@ -7603,6 +7631,8 @@ fetch_args: cursor_name n->portalname = $4; n->direction = FETCH_BACKWARD; n->howMany = $2; + n->location = @2; + n->direction_keyword = FETCH_KEYWORD_BACKWARD; $$ = (Node *) n; } | BACKWARD ALL opt_from_in cursor_name @@ -7612,6 +7642,8 @@ fetch_args: cursor_name n->portalname = $4; n->direction = FETCH_BACKWARD; n->howMany = FETCH_ALL; + n->location = -1; + n->direction_keyword = FETCH_KEYWORD_BACKWARD_ALL; $$ = (Node *) n; } ; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index ba12678d1cb..daa285ca62f 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3422,15 +3422,44 @@ typedef enum FetchDirection FETCH_RELATIVE, } FetchDirection; +typedef enum FetchDirectionKeywords +{ + FETCH_KEYWORD_NONE = 0, + FETCH_KEYWORD_NEXT, + FETCH_KEYWORD_PRIOR, + FETCH_KEYWORD_FIRST, + FETCH_KEYWORD_LAST, + FETCH_KEYWORD_ABSOLUTE, + FETCH_KEYWORD_RELATIVE, + FETCH_KEYWORD_ALL, + FETCH_KEYWORD_FORWARD, + FETCH_KEYWORD_FORWARD_ALL, + FETCH_KEYWORD_BACKWARD, + FETCH_KEYWORD_BACKWARD_ALL, +} FetchDirectionKeywords; + #define FETCH_ALL LONG_MAX typedef struct FetchStmt { NodeTag type; FetchDirection direction; /* see above */ - long howMany; /* number of rows, or position argument */ - char *portalname; /* name of portal (cursor) */ - bool ismove; /* true if MOVE */ + /* number of rows, or position argument */ + long howMany pg_node_attr(query_jumble_ignore); + /* name of portal (cursor) */ + char *portalname; + /* true if MOVE */ + bool ismove; + + /* + * Set when a direction_keyword (e.g., FETCH FORWARD) is used, to + * distinguish it from a numeric variant (e.g., FETCH 1) for the purpose + * of query jumbling. + */ + FetchDirectionKeywords direction_keyword; + + /* token location, or -1 if unknown */ + ParseLoc location pg_node_attr(query_jumble_location); } FetchStmt; /* ---------------------- diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 98159f4bd6f..220e5a4f6b3 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -805,6 +805,7 @@ FastPathStrongRelationLockData FdwInfo FdwRoutine FetchDirection +FetchDirectionKeywords FetchStmt FieldSelect FieldStore -- 2.39.5