🌐 AI搜索 & 代理 主页
Skip to content

Commit bee23ea

Browse files
michaelpqmichaelpq
committed
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 <samimseih@gmail.com> Discussion: https://postgr.es/m/CAA5RZ0tA6LbHCg2qSS+KuM850BZC_+ZgHV7Ug6BXw22TNyF+MA@mail.gmail.com
1 parent 1845958 commit bee23ea

File tree

7 files changed

+259
-17
lines changed

7 files changed

+259
-17
lines changed

‎contrib/pg_stat_statements/expected/cursors.out‎

Lines changed: 139 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -57,8 +57,8 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
5757
1 | 0 | COMMIT
5858
1 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1
5959
1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1
60-
1 | 1 | FETCH 1 IN cursor_stats_1
61-
1 | 1 | FETCH 1 IN cursor_stats_2
60+
1 | 1 | FETCH $1 IN cursor_stats_1
61+
1 | 1 | FETCH $1 IN cursor_stats_2
6262
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
6363
(9 rows)
6464

@@ -68,3 +68,140 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t;
6868
t
6969
(1 row)
7070

71+
-- Normalization of FETCH statements
72+
BEGIN;
73+
DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series(1, 10);
74+
-- implicit directions
75+
FETCH pgss_cursor;
76+
--
77+
(1 row)
78+
79+
FETCH 1 pgss_cursor;
80+
--
81+
(1 row)
82+
83+
FETCH 2 pgss_cursor;
84+
--
85+
(2 rows)
86+
87+
FETCH -1 pgss_cursor;
88+
--
89+
(1 row)
90+
91+
-- explicit NEXT
92+
FETCH NEXT pgss_cursor;
93+
--
94+
(1 row)
95+
96+
-- explicit PRIOR
97+
FETCH PRIOR pgss_cursor;
98+
--
99+
(1 row)
100+
101+
-- explicit FIRST
102+
FETCH FIRST pgss_cursor;
103+
--
104+
(1 row)
105+
106+
-- explicit LAST
107+
FETCH LAST pgss_cursor;
108+
--
109+
(1 row)
110+
111+
-- explicit ABSOLUTE
112+
FETCH ABSOLUTE 1 pgss_cursor;
113+
--
114+
(1 row)
115+
116+
FETCH ABSOLUTE 2 pgss_cursor;
117+
--
118+
(1 row)
119+
120+
FETCH ABSOLUTE -1 pgss_cursor;
121+
--
122+
(1 row)
123+
124+
-- explicit RELATIVE
125+
FETCH RELATIVE 1 pgss_cursor;
126+
--
127+
(0 rows)
128+
129+
FETCH RELATIVE 2 pgss_cursor;
130+
--
131+
(0 rows)
132+
133+
FETCH RELATIVE -1 pgss_cursor;
134+
--
135+
(1 row)
136+
137+
-- explicit FORWARD
138+
FETCH ALL pgss_cursor;
139+
--
140+
(0 rows)
141+
142+
-- explicit FORWARD ALL
143+
FETCH FORWARD ALL pgss_cursor;
144+
--
145+
(0 rows)
146+
147+
-- explicit FETCH FORWARD
148+
FETCH FORWARD pgss_cursor;
149+
--
150+
(0 rows)
151+
152+
FETCH FORWARD 1 pgss_cursor;
153+
--
154+
(0 rows)
155+
156+
FETCH FORWARD 2 pgss_cursor;
157+
--
158+
(0 rows)
159+
160+
FETCH FORWARD -1 pgss_cursor;
161+
--
162+
(1 row)
163+
164+
-- explicit FETCH BACKWARD
165+
FETCH BACKWARD pgss_cursor;
166+
--
167+
(1 row)
168+
169+
FETCH BACKWARD 1 pgss_cursor;
170+
--
171+
(1 row)
172+
173+
FETCH BACKWARD 2 pgss_cursor;
174+
--
175+
(2 rows)
176+
177+
FETCH BACKWARD -1 pgss_cursor;
178+
--
179+
(1 row)
180+
181+
-- explicit BACKWARD ALL
182+
FETCH BACKWARD ALL pgss_cursor;
183+
--
184+
(6 rows)
185+
186+
COMMIT;
187+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
188+
calls | query
189+
-------+--------------------------------------------------------------------
190+
1 | BEGIN
191+
1 | COMMIT
192+
1 | DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series($1, $2)
193+
3 | FETCH ABSOLUTE $1 pgss_cursor
194+
1 | FETCH ALL pgss_cursor
195+
1 | FETCH BACKWARD ALL pgss_cursor
196+
4 | FETCH BACKWARD pgss_cursor
197+
1 | FETCH FIRST pgss_cursor
198+
1 | FETCH FORWARD ALL pgss_cursor
199+
4 | FETCH FORWARD pgss_cursor
200+
1 | FETCH LAST pgss_cursor
201+
1 | FETCH NEXT pgss_cursor
202+
1 | FETCH PRIOR pgss_cursor
203+
3 | FETCH RELATIVE $1 pgss_cursor
204+
4 | FETCH pgss_cursor
205+
1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
206+
(16 rows)
207+

‎contrib/pg_stat_statements/expected/level_tracking.out‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1147,7 +1147,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
11471147
t | 1 | COMMIT
11481148
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab
11491149
f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab;
1150-
t | 1 | FETCH FORWARD 1 FROM foocur
1150+
t | 1 | FETCH FORWARD $1 FROM foocur
11511151
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
11521152
(7 rows)
11531153

@@ -1176,7 +1176,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
11761176
t | 1 | CLOSE foocur
11771177
t | 1 | COMMIT
11781178
t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab
1179-
t | 1 | FETCH FORWARD 1 FROM foocur
1179+
t | 1 | FETCH FORWARD $1 FROM foocur
11801180
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
11811181
(6 rows)
11821182

‎contrib/pg_stat_statements/expected/utility.out‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -702,7 +702,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
702702
1 | 13 | CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas
703703
1 | 10 | CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_series($2, $3) a
704704
1 | 0 | DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv
705-
1 | 5 | FETCH FORWARD 5 pgss_cursor
705+
1 | 5 | FETCH FORWARD $1 pgss_cursor
706706
1 | 7 | FETCH FORWARD ALL pgss_cursor
707707
1 | 1 | FETCH NEXT pgss_cursor
708708
1 | 13 | REFRESH MATERIALIZED VIEW pgss_matv

‎contrib/pg_stat_statements/sql/cursors.sql‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,3 +28,46 @@ COMMIT;
2828

2929
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
3030
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
31+
32+
-- Normalization of FETCH statements
33+
BEGIN;
34+
DECLARE pgss_cursor CURSOR FOR SELECT FROM generate_series(1, 10);
35+
-- implicit directions
36+
FETCH pgss_cursor;
37+
FETCH 1 pgss_cursor;
38+
FETCH 2 pgss_cursor;
39+
FETCH -1 pgss_cursor;
40+
-- explicit NEXT
41+
FETCH NEXT pgss_cursor;
42+
-- explicit PRIOR
43+
FETCH PRIOR pgss_cursor;
44+
-- explicit FIRST
45+
FETCH FIRST pgss_cursor;
46+
-- explicit LAST
47+
FETCH LAST pgss_cursor;
48+
-- explicit ABSOLUTE
49+
FETCH ABSOLUTE 1 pgss_cursor;
50+
FETCH ABSOLUTE 2 pgss_cursor;
51+
FETCH ABSOLUTE -1 pgss_cursor;
52+
-- explicit RELATIVE
53+
FETCH RELATIVE 1 pgss_cursor;
54+
FETCH RELATIVE 2 pgss_cursor;
55+
FETCH RELATIVE -1 pgss_cursor;
56+
-- explicit FORWARD
57+
FETCH ALL pgss_cursor;
58+
-- explicit FORWARD ALL
59+
FETCH FORWARD ALL pgss_cursor;
60+
-- explicit FETCH FORWARD
61+
FETCH FORWARD pgss_cursor;
62+
FETCH FORWARD 1 pgss_cursor;
63+
FETCH FORWARD 2 pgss_cursor;
64+
FETCH FORWARD -1 pgss_cursor;
65+
-- explicit FETCH BACKWARD
66+
FETCH BACKWARD pgss_cursor;
67+
FETCH BACKWARD 1 pgss_cursor;
68+
FETCH BACKWARD 2 pgss_cursor;
69+
FETCH BACKWARD -1 pgss_cursor;
70+
-- explicit BACKWARD ALL
71+
FETCH BACKWARD ALL pgss_cursor;
72+
COMMIT;
73+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";

‎src/backend/parser/gram.y‎

Lines changed: 41 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -7477,6 +7477,8 @@ fetch_args: cursor_name
74777477
n->portalname = $1;
74787478
n->direction = FETCH_FORWARD;
74797479
n->howMany = 1;
7480+
n->location = -1;
7481+
n->direction_keyword = FETCH_KEYWORD_NONE;
74807482
$$ = (Node *) n;
74817483
}
74827484
| from_in cursor_name
@@ -7486,6 +7488,19 @@ fetch_args: cursor_name
74867488
n->portalname = $2;
74877489
n->direction = FETCH_FORWARD;
74887490
n->howMany = 1;
7491+
n->location = -1;
7492+
n->direction_keyword = FETCH_KEYWORD_NONE;
7493+
$$ = (Node *) n;
7494+
}
7495+
| SignedIconst opt_from_in cursor_name
7496+
{
7497+
FetchStmt *n = makeNode(FetchStmt);
7498+
7499+
n->portalname = $3;
7500+
n->direction = FETCH_FORWARD;
7501+
n->howMany = $1;
7502+
n->location = @1;
7503+
n->direction_keyword = FETCH_KEYWORD_NONE;
74897504
$$ = (Node *) n;
74907505
}
74917506
| NEXT opt_from_in cursor_name
@@ -7495,6 +7510,8 @@ fetch_args: cursor_name
74957510
n->portalname = $3;
74967511
n->direction = FETCH_FORWARD;
74977512
n->howMany = 1;
7513+
n->location = -1;
7514+
n->direction_keyword = FETCH_KEYWORD_NEXT;
74987515
$$ = (Node *) n;
74997516
}
75007517
| PRIOR opt_from_in cursor_name
@@ -7504,6 +7521,8 @@ fetch_args: cursor_name
75047521
n->portalname = $3;
75057522
n->direction = FETCH_BACKWARD;
75067523
n->howMany = 1;
7524+
n->location = -1;
7525+
n->direction_keyword = FETCH_KEYWORD_PRIOR;
75077526
$$ = (Node *) n;
75087527
}
75097528
| FIRST_P opt_from_in cursor_name
@@ -7513,6 +7532,8 @@ fetch_args: cursor_name
75137532
n->portalname = $3;
75147533
n->direction = FETCH_ABSOLUTE;
75157534
n->howMany = 1;
7535+
n->location = -1;
7536+
n->direction_keyword = FETCH_KEYWORD_FIRST;
75167537
$$ = (Node *) n;
75177538
}
75187539
| LAST_P opt_from_in cursor_name
@@ -7522,6 +7543,8 @@ fetch_args: cursor_name
75227543
n->portalname = $3;
75237544
n->direction = FETCH_ABSOLUTE;
75247545
n->howMany = -1;
7546+
n->location = -1;
7547+
n->direction_keyword = FETCH_KEYWORD_LAST;
75257548
$$ = (Node *) n;
75267549
}
75277550
| ABSOLUTE_P SignedIconst opt_from_in cursor_name
@@ -7531,6 +7554,8 @@ fetch_args: cursor_name
75317554
n->portalname = $4;
75327555
n->direction = FETCH_ABSOLUTE;
75337556
n->howMany = $2;
7557+
n->location = @2;
7558+
n->direction_keyword = FETCH_KEYWORD_ABSOLUTE;
75347559
$$ = (Node *) n;
75357560
}
75367561
| RELATIVE_P SignedIconst opt_from_in cursor_name
@@ -7540,15 +7565,8 @@ fetch_args: cursor_name
75407565
n->portalname = $4;
75417566
n->direction = FETCH_RELATIVE;
75427567
n->howMany = $2;
7543-
$$ = (Node *) n;
7544-
}
7545-
| SignedIconst opt_from_in cursor_name
7546-
{
7547-
FetchStmt *n = makeNode(FetchStmt);
7548-
7549-
n->portalname = $3;
7550-
n->direction = FETCH_FORWARD;
7551-
n->howMany = $1;
7568+
n->location = @2;
7569+
n->direction_keyword = FETCH_KEYWORD_RELATIVE;
75527570
$$ = (Node *) n;
75537571
}
75547572
| ALL opt_from_in cursor_name
@@ -7558,6 +7576,8 @@ fetch_args: cursor_name
75587576
n->portalname = $3;
75597577
n->direction = FETCH_FORWARD;
75607578
n->howMany = FETCH_ALL;
7579+
n->location = -1;
7580+
n->direction_keyword = FETCH_KEYWORD_ALL;
75617581
$$ = (Node *) n;
75627582
}
75637583
| FORWARD opt_from_in cursor_name
@@ -7567,6 +7587,8 @@ fetch_args: cursor_name
75677587
n->portalname = $3;
75687588
n->direction = FETCH_FORWARD;
75697589
n->howMany = 1;
7590+
n->location = -1;
7591+
n->direction_keyword = FETCH_KEYWORD_FORWARD;
75707592
$$ = (Node *) n;
75717593
}
75727594
| FORWARD SignedIconst opt_from_in cursor_name
@@ -7576,6 +7598,8 @@ fetch_args: cursor_name
75767598
n->portalname = $4;
75777599
n->direction = FETCH_FORWARD;
75787600
n->howMany = $2;
7601+
n->location = @2;
7602+
n->direction_keyword = FETCH_KEYWORD_FORWARD;
75797603
$$ = (Node *) n;
75807604
}
75817605
| FORWARD ALL opt_from_in cursor_name
@@ -7585,6 +7609,8 @@ fetch_args: cursor_name
75857609
n->portalname = $4;
75867610
n->direction = FETCH_FORWARD;
75877611
n->howMany = FETCH_ALL;
7612+
n->location = -1;
7613+
n->direction_keyword = FETCH_KEYWORD_FORWARD_ALL;
75887614
$$ = (Node *) n;
75897615
}
75907616
| BACKWARD opt_from_in cursor_name
@@ -7594,6 +7620,8 @@ fetch_args: cursor_name
75947620
n->portalname = $3;
75957621
n->direction = FETCH_BACKWARD;
75967622
n->howMany = 1;
7623+
n->location = -1;
7624+
n->direction_keyword = FETCH_KEYWORD_BACKWARD;
75977625
$$ = (Node *) n;
75987626
}
75997627
| BACKWARD SignedIconst opt_from_in cursor_name
@@ -7603,6 +7631,8 @@ fetch_args: cursor_name
76037631
n->portalname = $4;
76047632
n->direction = FETCH_BACKWARD;
76057633
n->howMany = $2;
7634+
n->location = @2;
7635+
n->direction_keyword = FETCH_KEYWORD_BACKWARD;
76067636
$$ = (Node *) n;
76077637
}
76087638
| BACKWARD ALL opt_from_in cursor_name
@@ -7612,6 +7642,8 @@ fetch_args: cursor_name
76127642
n->portalname = $4;
76137643
n->direction = FETCH_BACKWARD;
76147644
n->howMany = FETCH_ALL;
7645+
n->location = -1;
7646+
n->direction_keyword = FETCH_KEYWORD_BACKWARD_ALL;
76157647
$$ = (Node *) n;
76167648
}
76177649
;

0 commit comments

Comments
 (0)