1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
--
-- DMLs on test table
--
SET pg_stat_statements.track_utility = FALSE;
CREATE TEMP TABLE pgss_dml_tab (a int, b char(20));
INSERT INTO pgss_dml_tab VALUES(generate_series(1, 10), 'aaa');
UPDATE pgss_dml_tab SET b = 'bbb' WHERE a > 7;
DELETE FROM pgss_dml_tab WHERE a > 9;
-- explicit transaction
BEGIN;
UPDATE pgss_dml_tab SET b = '111' WHERE a = 1 ;
COMMIT;
BEGIN \;
UPDATE pgss_dml_tab SET b = '222' WHERE a = 2 \;
COMMIT ;
UPDATE pgss_dml_tab SET b = '333' WHERE a = 3 \;
UPDATE pgss_dml_tab SET b = '444' WHERE a = 4 ;
BEGIN \;
UPDATE pgss_dml_tab SET b = '555' WHERE a = 5 \;
UPDATE pgss_dml_tab SET b = '666' WHERE a = 6 \;
COMMIT ;
-- many INSERT values
INSERT INTO pgss_dml_tab (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
-- SELECT with constants
SELECT * FROM pgss_dml_tab WHERE a > 5 ORDER BY a ;
a | b
---+----------------------
6 | 666
7 | aaa
8 | bbb
9 | bbb
(4 rows)
SELECT *
FROM pgss_dml_tab
WHERE a > 9
ORDER BY a ;
a | b
---+---
(0 rows)
-- these two need to be done on a different table
-- SELECT without constants
SELECT * FROM pgss_dml_tab ORDER BY a;
a | b
---+----------------------
1 | a
1 | 111
2 | b
2 | 222
3 | c
3 | 333
4 | 444
5 | 555
6 | 666
7 | aaa
8 | bbb
9 | bbb
(12 rows)
-- SELECT with IN clause
SELECT * FROM pgss_dml_tab WHERE a IN (1, 2, 3, 4, 5);
a | b
---+----------------------
1 | 111
2 | 222
3 | 333
4 | 444
5 | 555
1 | a
2 | b
3 | c
(8 rows)
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
calls | rows | query
-------+------+---------------------------------------------------------------------
1 | 1 | DELETE FROM pgss_dml_tab WHERE a > $1
1 | 3 | INSERT INTO pgss_dml_tab (a, b) VALUES ($1, $2), ($3, $4), ($5, $6)
1 | 10 | INSERT INTO pgss_dml_tab VALUES(generate_series($1, $2), $3)
1 | 12 | SELECT * FROM pgss_dml_tab ORDER BY a
2 | 4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a
1 | 8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1 /*, ... */)
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
1 | 0 | SET pg_stat_statements.track_utility = $1
6 | 6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2
1 | 3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2
(10 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
-- MERGE
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
WHEN NOT MATCHED THEN INSERT VALUES (0, NULL); -- same as above
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
WHEN MATCHED THEN DELETE;
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
WHEN MATCHED THEN DO NOTHING;
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
WHEN NOT MATCHED THEN DO NOTHING;
DROP TABLE pgss_dml_tab;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
calls | rows | query
-------+------+-----------------------------------------------------------------------------------------
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
| | WHEN MATCHED AND length(st.b) > $2 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
| | WHEN MATCHED THEN DELETE
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
| | WHEN MATCHED THEN DO NOTHING
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
| | WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
| | WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
| | WHEN NOT MATCHED THEN DO NOTHING
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) +
| | WHEN NOT MATCHED THEN INSERT (a) VALUES ($1)
2 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) +
| | WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2)
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) +
| | WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2)
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(10 rows)
-- check that [temp] table relation extensions are tracked as writes
CREATE TABLE pgss_extend_tab (a int, b text);
CREATE TEMP TABLE pgss_extend_temp_tab (a int, b text);
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
INSERT INTO pgss_extend_tab (a, b) SELECT generate_series(1, 1000), 'something';
INSERT INTO pgss_extend_temp_tab (a, b) SELECT generate_series(1, 1000), 'something';
WITH sizes AS (
SELECT
pg_relation_size('pgss_extend_tab') / current_setting('block_size')::int8 AS rel_size,
pg_relation_size('pgss_extend_temp_tab') / current_setting('block_size')::int8 AS temp_rel_size
)
SELECT
SUM(local_blks_written) >= (SELECT temp_rel_size FROM sizes) AS temp_written_ok,
SUM(local_blks_dirtied) >= (SELECT temp_rel_size FROM sizes) AS temp_dirtied_ok,
SUM(shared_blks_written) >= (SELECT rel_size FROM sizes) AS written_ok,
SUM(shared_blks_dirtied) >= (SELECT rel_size FROM sizes) AS dirtied_ok
FROM pg_stat_statements;
temp_written_ok | temp_dirtied_ok | written_ok | dirtied_ok
-----------------+-----------------+------------+------------
t | t | t | t
(1 row)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
|
