10000 [PGPRO-5386] Added new functionality to clean_aqo_data(). · postgrespro/aqo@8c54621 · GitHub
[go: up one dir, main page]
Skip to content

Commit 8c54621

Browse files
Damir BelyalovAndrey Lepikhov
Damir Belyalov
authored and
Andrey Lepikhov
committed
[PGPRO-5386] Added new functionality to clean_aqo_data().
Now clean_aqo_data() can delete useless rows not only in table aqo_data but also in aqo_queries, aqo_query_texts, aqo_query_stat.
1 parent f6d9b06 commit 8c54621

File tree

3 files changed

+339
-18
lines changed

3 files changed

+339
-18
lines changed

‎aqo--1.2--1.3.sql

Lines changed: 37 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -3,24 +3,53 @@ ALTER TABLE public.aqo_data ADD COLUMN oids OID [] DEFAULT NULL;
33
CREATE OR REPLACE FUNCTION public.clean_aqo_data() RETURNS void AS $$
44
DECLARE
55
aqo_data_row aqo_data%ROWTYPE;
6+
aqo_queries_row aqo_queries%ROWTYPE;
7+
aqo_query_texts_row aqo_query_texts%ROWTYPE;
8+
aqo_query_stat_row aqo_query_stat%ROWTYPE;
69
oid_var oid;
10+
fspace_hash_var numeric;
711
delete_row boolean DEFAULT false;
812
BEGIN
913
RAISE NOTICE 'Cleaning aqo_data records';
1014

11-
FOR aqo_data_row IN
15+
FOR aqo_data_row IN
1216
SELECT * FROM aqo_data
13-
LOOP
17+
LOOP
1418
delete_row = false;
15-
FOREACH oid_var IN ARRAY aqo_data_row.oids
19+
SELECT aqo_data_row.fspace_hash INTO fspace_hash_var FROM aqo_data;
20+
IF (aqo_data_row.oids IS NOT NULL) THEN
21+
FOREACH oid_var IN ARRAY aqo_data_row.oids
22+
LOOP
23+
IF NOT EXISTS (SELECT relname FROM pg_class WHERE oid = oid_var) THEN
24+
delete_row = true;
25+
END IF;
26+
END LOOP;
27+
END IF;
28+
29+
FOR aqo_queries_row IN
30+
SELECT * FROM aqo_queries
1631
LOOP
17-
IF NOT EXISTS (SELECT relname FROM pg_class WHERE oid = oid_var) THEN
18-
delete_row = true;
32+
IF (delete_row = true AND
33+
fspace_hash_var <> 0 AND
34+
fspace_hash_var = aqo_queries_row.fspace_hash AND
35+
aqo_queries_row.fspace_hash = aqo_queries_row.query_hash) THEN
36+
37+
DELETE FROM aqo_data WHERE aqo_data = aqo_data_row;
38+
DELETE FROM aqo_queries WHERE aqo_queries = aqo_queries_row;
39+
FOR aqo_query_texts_row IN
40+
SELECT * FROM aqo_query_texts
41+
LOOP
42+
DELETE FROM aqo_query_texts WHERE aqo_query_texts_row.query_hash = fspace_hash_var AND
43+
aqo_query_texts = aqo_query_texts_row;
44+
END LOOP;
45+
FOR aqo_query_stat_row IN
46+
SELECT * FROM aqo_query_stat
47+
LOOP
48+
DELETE FROM aqo_query_stat WHERE aqo_query_stat_row.query_hash = fspace_hash_var AND
49+
aqo_query_stat = aqo_query_stat_row;
50+
END LOOP;
1951
END IF;
2052
END LOOP;
21-
IF delete_row = true THEN
22-
DELETE FROM aqo_data WHERE aqo_data = aqo_data_row;
23-
END IF;
2453
END LOOP;
2554
END;
2655
$$ LANGUAGE plpgsql;

‎expected/clean_aqo_data.out

Lines changed: 200 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,9 @@
11
CREATE EXTENSION aqo;
22
SET aqo.mode = 'learn';
3+
DROP TABLE IF EXISTS a;
4+
NOTICE: table "a" does not exist, skipping
5+
DROP TABLE IF EXISTS b;
6+
NOTICE: table "b" does not exist, skipping
37
CREATE TABLE a();
48
SELECT * FROM a;
59
--
@@ -13,13 +17,42 @@ NOTICE: Cleaning aqo_data records
1317

1418
(1 row)
1519

16-
-- lines with a_oid should remain
20+
/*
21+
* lines with a_oid in aqo_data,
22+
* lines with fspace_hash corresponding to a_oid in aqo_queries,
23+
* lines with query_hash corresponding to a_oid's fspace_hash in aqo_query_texts,
24+
* lines with query_hash corresponding to a_oid's fspace_hash in aqo_query_stat
25+
* should remain
26+
*/
1727
SELECT count(*) FROM aqo_data WHERE :a_oid=ANY(oids);
1828
count
1929
-------
2030
1
2131
(1 row)
2232

33+
SELECT count(*) FROM aqo_queries WHERE
34+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids));
35+
count
36+
-------
37+
1
38+
(1 row)
39+
40+
SELECT count(*) FROM aqo_query_texts WHERE
41+
aqo_query_texts.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
42+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)));
43+
count
44+
-------
45+
1
46+
(1 row)
47+
48+
SELECT count(*) FROM aqo_query_stat WHERE
49+
aqo_query_stat.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
50+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)));
51+
count
52+
-------
53+
1
54+
(1 row)
55+
2356
DROP TABLE a;
2457
SELECT clean_aqo_data();
2558
NOTICE: Cleaning aqo_data records
@@ -28,13 +61,45 @@ NOTICE: Cleaning aqo_data records
2861

2962
(1 row)
3063

31-
-- lines with a_oid should be deleted
64+
/*
65+
* lines with a_oid in aqo_data,
66+
* lines with a_oid's fspace_hash EQUAL TO query_hash in aqo_queries,
67+
* lines with query_hash corresponding to a_oid's fspace_hash in aqo_query_texts,
68+
* lines with query_hash corresponding to a_oid's fspace_hash in aqo_query_stat,
69+
* should be deleted
70+
*/
3271
SELECT count(*) FROM aqo_data WHERE :a_oid=ANY(oids);
3372
count
3473
-------
3574
0
3675
(1 row)
3776

77+
SELECT count(*) FROM aqo_queries WHERE
78+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)) AND
79+
aqo_queries.fspace_hash = aqo_queries.query_hash;
80+
count
81+
-------
82+
0
83+
(1 row)
84+
85+
SELECT count(*) FROM aqo_query_texts WHERE
86+
aqo_query_texts.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
87+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)) AND
88+
aqo_queries.fspace_hash = aqo_queries.query_hash);
89+
count
90+
-------
91+
0
92+
(1 row)
93+
94+
SELECT count(*) FROM aqo_query_stat WHERE
95+
aqo_query_stat.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
96+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)) AND
97+
aqo_queries.fspace_hash = aqo_queries.query_hash);
98+
count
99+
-------
100+
0
101+
(1 row)
102+
38103
CREATE TABLE a();
39104
CREATE TABLE b();
40105
SELECT * FROM a;
@@ -58,12 +123,58 @@ SELECT count(*) FROM aqo_data WHERE :a_oid=ANY(oids);
58123
3
59124
(1 row)
60125

126+
SELECT count(*) FROM aqo_queries WHERE
127+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids));
128+
count
129+
-------
130+
2
131+
(1 row)
132+
133+
SELECT count(*) FROM aqo_query_texts WHERE
134+
aqo_query_texts.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
135+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)));
136+
count
137+
-------
138+
2
139+
(1 row)
140+
141+
SELECT count(*) FROM aqo_query_stat WHERE
142+
aqo_query_stat.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
143+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)));
144+
count
145+
-------
146+
2
147+
(1 row)
148+
61149
SELECT count(*) FROM aqo_data WHERE :b_oid=ANY(oids);
62150
count
63151
-------
64152
3
65153
(1 row)
66154

155+
SELECT count(*) FROM aqo_queries WHERE
156+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids));
157+
count
158+
-------
159+
2
160+
(1 row)
161+
162+
SELECT count(*) FROM aqo_query_texts WHERE
163+
aqo_query_texts.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
164+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)));
165+
count
166+
-------
167+
2
168+
(1 row)
169+
170+
SELECT count(*) FROM aqo_query_stat WHERE
171+
aqo_query_stat.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
172+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)));
173+
count
174+
-------
175+
2
176+
(1 row)
177+
67178
DROP TABLE a;
68179
SELECT clean_aqo_data();
69180
NOTICE: Cleaning aqo_data records
@@ -72,17 +183,75 @@ NOTICE: Cleaning aqo_data records
72183

73184
(1 row)
74185

75-
-- lines with a_oid deleted, including line with both a_oid and b_oid
186+
/*
187+
* lines corresponding to a_oid and both a_oid's fspace_hash deleted in aqo_data,
188+
* lines with fspace_hash corresponding to a_oid deleted in aqo_queries,
189+
* lines with query_hash corresponding to a_oid's fspace_hash deleted in aqo_query_texts,
190+
* lines with query_hash corresponding to a_oid's fspace_hash deleted in aqo_query_stat,
191+
*/
76192
SELECT count(*) FROM aqo_data WHERE :a_oid=ANY(oids);
77193
count
78194
-------
79195
0
80196
(1 row)
81197

198+
SELECT count(*) FROM aqo_queries WHERE
199+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)) AND
200+
aqo_queries.fspace_hash = aqo_queries.query_hash;
201+
count
202+
-------
203+
0
204+
(1 row)
205+
206+
SELECT count(*) FROM aqo_query_texts WHERE
207+
aqo_query_texts.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
208+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)) AND
209+
aqo_queries.fspace_hash = aqo_queries.query_hash);
210+
count
211+
-------
212+
0
213+
(1 row)
214+
215+
SELECT count(*) FROM aqo_query_stat WHERE
216+
aqo_query_stat.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
217+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)) AND
218+
aqo_queries.fspace_hash = aqo_queries.query_hash);
219+
count
220+
-------
221+
0
222+
(1 row)
223+
224+
-- lines corresponding to b_oid in all theese tables should remain
82225
SELECT count(*) FROM aqo_data WHERE :b_oid=ANY(oids);
83226
count
84227
-------
85-
2
228+
1
229+
(1 row)
230+
231+
SELECT count(*) FROM aqo_queries WHERE
232+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)) AND
233+
aqo_queries.fspace_hash = aqo_queries.query_hash;
234+
count
235+
-------
236+
1
237+
(1 row)
238+
239+
SELECT count(*) FROM aqo_query_texts WHERE
240+
aqo_query_texts.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
241+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)) AND
242+
aqo_queries.fspace_hash = aqo_queries.query_hash);
243+
count
244+
-------
245+
1
246+
(1 row)
247+
248+
SELECT count(*) FROM aqo_query_stat WHERE
249+
aqo_query_stat.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
250+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)) AND
251+
aqo_queries.fspace_hash = aqo_queries.query_hash);
252+
count
253+
-------
254+
1
86255
(1 row)
87256

88257
DROP TABLE b;
@@ -93,10 +262,36 @@ NOTICE: Cleaning aqo_data records
93262

94263
(1 row)
95264

96-
-- lines with b_oid deleted
265+
-- lines corresponding to b_oid in theese tables deleted
97266
SELECT count(*) FROM aqo_data WHERE :b_oid=ANY(oids);
98267
count
99268
-------
100269
0
101270
(1 row)
102271

272+
SELECT count(*) FROM aqo_queries WHERE
273+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)) AND
274+
aqo_queries.fspace_hash = aqo_queries.query_hash;
275+
count
276+
-------
277+
0
278+
(1 row)
279+
280+
SELECT count(*) FROM aqo_query_texts WHERE
281+
aqo_query_texts.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
282+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)) AND
283+
aqo_queries.fspace_hash = aqo_queries.query_hash);
284+
count
285+
-------
286+
0
287+
(1 row)
288+
289+
SELECT count(*) FROM aqo_query_stat WHERE
290+
aqo_query_stat.query_hash = ANY(SELECT aqo_queries.query_hash FROM aqo_queries WHERE
291+
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)) AND
292+
aqo_queries.fspace_hash = aqo_queries.query_hash);
293+
count
294+
-------
295+
0
296+
(1 row)
297+

0 commit comments

Comments
 (0)
0