10000 Rework the cleanup routine in AQO interface. · postgrespro/aqo@678cfaf · GitHub
[go: up one dir, main page]
Skip to content

Commit 678cfaf

Browse files
danolivodanolivo
committed
Rework the cleanup routine in AQO interface.
Allow user to know how many records were removed during this procedure.
1 parent 14e8550 commit 678cfaf

12 files changed

+109
-52
lines changed

‎aqo--1.4--1.5.sql

Lines changed: 43 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ $$ LANGUAGE plpgsql;
7878

7979
DROP FUNCTION public.top_time_queries;
8080
DROP FUNCTION public.aqo_drop;
81+
DROP FUNCTION public.clean_aqo_data;
8182

8283
--
8384
-- Show execution time of queries, for which AQO has statistics.
@@ -166,4 +167,45 @@ END;
166167
$$ LANGUAGE plpgsql;
167168

168169
COMMENT ON FUNCTION public.aqo_drop_class(bigint) IS
169-
'Remove info about an query class from AQO ML knowledge base.';
170+
'Remove info about an query class from AQO ML knowledge base.';
171+
172+
--
173+
-- Remove unneeded rows from the AQO ML storage.
174+
-- For common feature space, remove rows from aqo_data only.
175+
-- For custom feature space - remove all rows related to the space from all AQO
176+
-- tables even if only one oid for one feature subspace of the space is illegal.
177+
-- Returns number of deleted rows from aqo_queries and aqo_data tables.
178+
--
179+
CREATE OR REPLACE FUNCTION public.aqo_cleanup(OUT nfs integer, OUT nfss integer)
180+
AS $$
181+
DECLARE
182+
fs bigint;
183+
fss integer;
184+
BEGIN
185+
-- Save current number of rows
186+
SELECT count(*) FROM aqo_queries INTO nfs;
187+
SELECT count(*) FROM aqo_data INTO nfss;
188+
189+
FOR fs,fss IN SELECT q1.fs,q1.fss FROM (
190+
SELECT fspace_hash fs, fsspace_hash fss, unnest(oids) AS reloid
191+
FROM aqo_data) AS q1
192+
WHERE q1.reloid NOT IN (SELECT oid FROM pg_class)
193+
GROUP BY (q1.fs,q1.fss)
194+
LOOP
195+
IF (fs = 0) THEN
196+
DELETE FROM aqo_data WHERE fsspace_hash = fss;
197+
continue;
198+
END IF;
199+
200+
-- Remove ALL feature space if one of oids isn't exists
201+
DELETE FROM aqo_queries WHERE fspace_hash = fs;
202+
END LOOP;
203+
204+
-- Calculate difference with previous state of knowledge base
205+
nfs := nfs - (SELECT count(*) FROM aqo_queries);
206+
nfss := nfss - (SELECT count(*) FROM aqo_data);
207+
END;
208+
$$ LANGUAGE plpgsql;
209+
210+
COMMENT ON FUNCTION public.aqo_cleanup() IS
211+
'Remove unneeded rows from the AQO ML storage';

‎expected/aqo_learn.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -235,10 +235,10 @@ SELECT count(*) FROM tmp1;
235235
(1 row)
236236

237237
-- Remove data on some unneeded instances of tmp1 table.
238-
SELECT public.clean_aqo_data();
239-
clean_aqo_data
240-
----------------
241-
238+
SELECT public.aqo_cleanup();
239+
aqo_cleanup
240+
-------------
241+
(9,18)
242242
(1 row)
243243

244244
-- Result of the query below should be empty

‎expected/clean_aqo_data.out

Lines changed: 20 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -10,10 +10,10 @@ SELECT * FROM a;
1010
(0 rows)
1111

1212
SELECT 'a'::regclass::oid AS a_oid \gset
13-
SELECT clean_aqo_data();
14-
clean_aqo_data
15-
----------------
16-
13+
SELECT aqo_cleanup();
14+
aqo_cleanup
15+
-------------
16+
(0,0)
1717
(1 row)
1818

1919
/*
@@ -53,10 +53,10 @@ SELECT count(*) FROM aqo_query_stat WHERE
5353
(1 row)
5454

5555
DROP TABLE a;
56-
SELECT clean_aqo_data();
57-
clean_aqo_data
58-
----------------
59-
56+
SELECT aqo_cleanup();
57+
aqo_cleanup
58+
-------------
59+
(1,1)
6060
(1 row)
6161

6262
/*
@@ -107,10 +107,10 @@ SELECT 'a'::regclass::oid AS a_oid \gset
107107
-- add manually line with different fspace_hash and query_hash to aqo_queries
108108
INSERT INTO aqo_queries VALUES (:a_oid + 1, 't', 't', :a_oid, 'f');
109109
DROP TABLE a;
110-
SELECT clean_aqo_data();
111-
clean_aqo_data
112-
----------------
113-
110+
SELECT aqo_cleanup();
111+
aqo_cleanup
112+
-------------
113+
(1,1)
114114
(1 row)
115115

116116
-- this line should remain
@@ -196,10 +196,10 @@ SELECT count(*) FROM aqo_query_stat WHERE
196196
(1 row)
197197

198198
DROP TABLE a;
199-
SELECT clean_aqo_data();
200-
clean_aqo_data
201-
----------------
202-
199+
SELECT aqo_cleanup();
200+
aqo_cleanup
201+
-------------
202+
(2,4)
203203
(1 row)
204204

205205
/*
@@ -274,10 +274,10 @@ SELECT count(*) FROM aqo_query_stat WHERE
274274
(1 row)
275275

276276
DROP TABLE b;
277-
SELECT clean_aqo_data();
278-
clean_aqo_data
279-
----------------
280-
277+
SELECT aqo_cleanup();
278+
aqo_cleanup
279+
-------------
280+
(1,1)
281281
(1 row)
282282

283283
-- lines corresponding to b_oid in theese tables deleted

‎expected/gucs.out

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,12 @@ SELECT obj_description('public.aqo_drop_class'::regproc::oid);
8686
Remove info about an query class from AQO ML knowledge base.
8787
(1 row)
8888

89+
SELECT obj_description('public.aqo_cleanup'::regproc::oid);
90+
obj_description
91+
----------------------------------------------
92+
Remove unneeded rows from the AQO ML storage
93+
(1 row)
94+
8995
\df show_cardinality_errors
9096
List of functions
9197
Schema | Name | Result data type | Argument data types | Type
@@ -107,4 +113,11 @@ SELECT obj_description('public.aqo_drop_class'::regproc::oid);
107113
public | aqo_drop_class | integer | id bigint | func
108114
(1 row)
109115

116+
\df aqo_cleanup
117+
List of functions
118+
Schema | Name | Result data type | Argument data types | Type
119+
--------+-------------+------------------+-----------------------------------+------
120+
public | aqo_cleanup | record | OUT nfs integer, OUT nfss integer | func
121+
(1 row)
122+
110123
DROP EXTENSION aqo;

‎expected/temp_tables.out

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -46,10 +46,10 @@ SELECT count(*) FROM aqo_data;
4646
(1 row)
4747

4848
DROP TABLE tt;
49-
SELECT clean_aqo_data();
50-
clean_aqo_data
51-
----------------
52-
49+
SELECT aqo_cleanup();
50+
aqo_cleanup
51+
-------------
52+
(0,0)
5353
(1 row)
5454

5555
SELECT count(*) FROM aqo_data; -- Should be the same as above
@@ -59,10 +59,10 @@ SELECT count(*) FROM aqo_data; -- Should be the same as above
5959
(1 row)
6060

6161
DROP TABLE pt;
62-
SELECT clean_aqo_data();
63-
clean_aqo_data
64-
----------------
65-
62+
SELECT aqo_cleanup();
63+
aqo_cleanup
64+
-------------
65+
(3,10)
6666
(1 row)
6767

6868
SELECT count(*) FROM aqo_data; -- Should be 0
@@ -133,10 +133,10 @@ SELECT * FROM check_estimated_rows('
133133

134134
SET aqo.mode = 'forced'; -- Now we use all fss records for each query
135135
DROP TABLE pt;
136-
SELECT clean_aqo_data();
137-
clean_aqo_data
138-
----------------
139-
136+
SELECT aqo_cleanup();
137+
aqo_cleanup
138+
-------------
139+
(2,6)
140140
(1 row)
141141

142142
CREATE TABLE pt AS SELECT x AS x, (x % 10) AS y FROM generate_series(1,100) AS x;

‎expected/unsupported.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -579,10 +579,10 @@ ORDER BY (md5(query_text),error) DESC;
579579
(12 rows)
580580

581581
DROP TABLE t,t1 CASCADE;
582-
SELECT public.clean_aqo_data();
583-
clean_aqo_data
584-
----------------
585-
582+
SELECT public.aqo_cleanup();
583+
aqo_cleanup
584+
-------------
585+
(12,42)
586586
(1 row)
587587

588588
-- Look for any remaining queries in the ML storage.

‎sql/aqo_learn.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -123,7 +123,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;
123123
SELECT count(*) FROM tmp1;
124124

125125
-- Remove data on some unneeded instances of tmp1 table.
126-
SELECT public.clean_aqo_data();
126+
SELECT public.aqo_cleanup();
127127

128128
-- Result of the query below should be empty
129129
SELECT * FROM aqo_query_texts aqt1, aqo_query_texts aqt2

‎sql/clean_aqo_data.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@ DROP TABLE IF EXISTS b;
66
CREATE TABLE a();
77
SELECT * FROM a;
88
SELECT 'a'::regclass::oid AS a_oid \gset
9-
SELECT clean_aqo_data();
9+
SELECT aqo_cleanup();
1010

1111
/*
1212
* lines with a_oid in aqo_data,
@@ -26,7 +26,7 @@ SELECT count(*) FROM aqo_query_stat WHERE
2626
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :a_oid=ANY(oids)));
2727

2828
DROP TABLE a;
29-
SELECT clean_aqo_data();
29+
SELECT aqo_cleanup();
3030

3131
/*
3232
* lines with a_oid in aqo_data,
@@ -54,7 +54,7 @@ SELECT 'a'::regclass::oid AS a_oid \gset
5454
-- add manually line with different fspace_hash and query_hash to aqo_queries
5555
INSERT INTO aqo_queries VALUES (:a_oid + 1, 't', 't', :a_oid, 'f');
5656
DROP TABLE a;
57-
SELECT clean_aqo_data();
57+
SELECT aqo_cleanup();
5858
-- this line should remain
5959
SELECT count(*) FROM aqo_queries WHERE (fspace_hash = :a_oid AND query_hash = :a_oid + 1);
6060

@@ -88,7 +88,7 @@ SELECT count(*) FROM aqo_query_stat WHERE
8888
aqo_queries.fspace_hash = ANY(SELECT aqo_data.fspace_hash FROM aqo_data WHERE :b_oid=ANY(oids)));
8989

9090
DROP TABLE a;
91-
SELECT clean_aqo_data();
91+
SELECT aqo_cleanup();
9292

9393
/*
9494
* lines corresponding to a_oid and both a_oid's fspace_hash deleted in aqo_data,
@@ -124,7 +124,7 @@ SELECT count(*) FROM aqo_query_stat WHERE
124124
aqo_queries.fspace_hash = aqo_queries.query_hash);
125125

126126
DROP TABLE b;
127-
SELECT clean_aqo_data();
127+
SELECT aqo_cleanup();
128128

129129
-- lines corresponding to b_oid in theese tables deleted
130130
SELECT count(*) FROM aqo_data WHERE :b_oid=ANY(oids);

‎sql/gucs.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,9 +26,11 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
2626
SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
2727
SELECT obj_description('public.show_execution_time'::regproc::oid);
2828
SELECT obj_description('public.aqo_drop_class'::regproc::oid);
29+
SELECT obj_description('public.aqo_cleanup'::regproc::oid);
2930

3031
\df show_cardinality_errors
3132
\df show_execution_time
3233
\df aqo_drop_class
34+
\df aqo_cleanup
3335

3436
DROP EXTENSION aqo;

‎sql/temp_tables.sql

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -16,10 +16,10 @@ SELECT count(*) FROM pt AS pt1, tt AS tt1, tt AS tt2, pt AS pt2;
1616
SELECT count(*) FROM aqo_data;
1717

1818
DROP TABLE tt;
19-
SELECT clean_aqo_data();
19+
SELECT aqo_cleanup();
2020
SELECT count(*) FROM aqo_data; -- Should be the same as above
2121
DROP TABLE pt;
22-
SELECT clean_aqo_data();
22+
SELECT aqo_cleanup();
2323
SELECT count(*) FROM aqo_data; -- Should be 0
2424
SELECT query_text FROM aqo_queries aq LEFT JOIN aqo_query_texts aqt
2525
ON aq.query_hash = aqt.query_hash
@@ -66,7 +66,7 @@ SELECT * FROM check_estimated_rows('
6666

6767
SET aqo.mode = 'forced'; -- Now we use all fss records for each query
6868
DROP TABLE pt;
69-
SELECT clean_aqo_data();
69+
SELECT aqo_cleanup();
7070
CREATE TABLE pt AS SELECT x AS x, (x % 10) AS y FROM generate_series(1,100) AS x;
7171
CREATE TEMP TABLE ttd1 AS
7272
SELECT -(x*3) AS x, (x % 9) AS y1 FROM generate_series(1,100) AS x;

‎sql/unsupported.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -163,7 +163,7 @@ ORDER BY (md5(query_text),error) DESC;
163163

164164
DROP TABLE t,t1 CASCADE;
165165

166-
SELECT public.clean_aqo_data();
166+
SELECT public.aqo_cleanup();
167167

168168
-- Look for any remaining queries in the ML storage.
169169
SELECT to_char(error, '9.99EEEE')::text AS error, query_text

‎t/001_pgbench.pl

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -272,7 +272,7 @@
272272
pgbench_history CASCADE;");
273273

274274
# Clean unneeded AQO knowledge
275-
$node->safe_psql('postgres', "SELECT clean_aqo_data()");
275+
$node->safe_psql('postgres', "SELECT public.aqo_cleanup()");
276276

277277
# Calculate total number of rows in AQO-related tables.
278278
my $new_fs_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_queries;");

0 commit comments

Comments
 (0)
0