NotificationsYou must be signed in to change notification settings
Fork54
Star463

Commit678cfaf

committed

Rework the cleanup routine in AQO interface.

Allow user to know how many records were removed during this procedure.

1 parent14e8550 commit678cfafCopy full SHA for 678cfaf

File tree

12 files changed

+109

-52

lines changed

12 files changed

+109

-52

lines changed

`‎aqo--1.4--1.5.sql`

Lines changed: 43 additions & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -78,6 +78,7 @@ $$ LANGUAGE plpgsql;`
`78`	`78`
`79`	`79`	`DROPFUNCTIONpublic.top_time_queries;`
`80`	`80`	`DROPFUNCTIONpublic.aqo_drop;`
	`81`	`+DROPFUNCTIONpublic.clean_aqo_data;`
`81`	`82`
`82`	`83`	`--`
`83`	`84`	`-- Show execution time of queries, for which AQO has statistics.`
`@@ -166,4 +167,45 @@ END;`
`166`	`167`	`$$ LANGUAGE plpgsql;`
`167`	`168`
`168`	`169`	`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 REPLACEFUNCTIONpublic.aqo_cleanup(OUT nfsinteger, OUT nfssinteger)`
	`180`	`+AS $$`
	`181`	`+DECLARE`
	`182`	`+ fsbigint;`
	`183`	`+ fssinteger;`
	`184`	`+BEGIN`
	`185`	`+-- Save current number of rows`
	`186`	`+SELECTcount(*)FROM aqo_queries INTO nfs;`
	`187`	`+SELECTcount(*)FROM aqo_data INTO nfss;`
	`188`	`+`
	`189`	`+ FOR fs,fssINSELECTq1.fs,q1.fssFROM (`
	`190`	`+SELECT fspace_hash fs, fsspace_hash fss, unnest(oids)AS reloid`
	`191`	`+FROM aqo_data)AS q1`
	`192`	`+WHEREq1.reloid NOTIN (SELECToidFROM pg_class)`
	`193`	`+GROUP BY (q1.fs,q1.fss)`
	`194`	`+ LOOP`
	`195`	`+ IF (fs=0) THEN`
	`196`	`+DELETEFROM aqo_dataWHERE fsspace_hash= fss;`
	`197`	`+ continue;`
	`198`	`+ END IF;`
	`199`	`+`
	`200`	`+-- Remove ALL feature space if one of oids isn't exists`
	`201`	`+DELETEFROM aqo_queriesWHERE fspace_hash= fs;`
	`202`	`+ END LOOP;`
	`203`	`+`
	`204`	`+-- Calculate difference with previous state of knowledge base`
	`205`	`+ nfs := nfs- (SELECTcount(*)FROM aqo_queries);`
	`206`	`+ nfss := nfss- (SELECTcount(*)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 number	Diff line number	Diff line change
`@@ -235,10 +235,10 @@ SELECT count(*) FROM tmp1;`
`235`	`235`	`(1 row)`
`236`	`236`
`237`	`237`	`-- 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)`
`242`	`242`	`(1 row)`
`243`	`243`
`244`	`244`	`-- Result of the query below should be empty`

`‎expected/clean_aqo_data.out`

Lines changed: 20 additions & 20 deletions

Original file line number	Diff line number	Diff line change
`@@ -10,10 +10,10 @@ SELECT * FROM a;`
`10`	`10`	`(0 rows)`
`11`	`11`
`12`	`12`	`SELECT 'a'::regclass::oid AS a_oid \gset`
`13`		`-SELECTclean_aqo_data();`
`14`		`-clean_aqo_data`
`15`		`-----------------`
`16`		`-`
	`13`	`+SELECTaqo_cleanup();`
	`14`	`+aqo_cleanup`
	`15`	`+-------------`
	`16`	`+(0,0)`
`17`	`17`	`(1 row)`
`18`	`18`
`19`	`19`	`/*`
`@@ -53,10 +53,10 @@ SELECT count(*) FROM aqo_query_stat WHERE`
`53`	`53`	`(1 row)`
`54`	`54`
`55`	`55`	`DROP TABLE a;`
`56`		`-SELECTclean_aqo_data();`
`57`		`-clean_aqo_data`
`58`		`-----------------`
`59`		`-`
	`56`	`+SELECTaqo_cleanup();`
	`57`	`+aqo_cleanup`
	`58`	`+-------------`
	`59`	`+(1,1)`
`60`	`60`	`(1 row)`
`61`	`61`
`62`	`62`	`/*`
`@@ -107,10 +107,10 @@ SELECT 'a'::regclass::oid AS a_oid \gset`
`107`	`107`	`-- add manually line with different fspace_hash and query_hash to aqo_queries`
`108`	`108`	`INSERT INTO aqo_queries VALUES (:a_oid + 1, 't', 't', :a_oid, 'f');`
`109`	`109`	`DROP TABLE a;`
`110`		`-SELECTclean_aqo_data();`
`111`		`-clean_aqo_data`
`112`		`-----------------`
`113`		`-`
	`110`	`+SELECTaqo_cleanup();`
	`111`	`+aqo_cleanup`
	`112`	`+-------------`
	`113`	`+(1,1)`
`114`	`114`	`(1 row)`
`115`	`115`
`116`	`116`	`-- this line should remain`
`@@ -196,10 +196,10 @@ SELECT count(*) FROM aqo_query_stat WHERE`
`196`	`196`	`(1 row)`
`197`	`197`
`198`	`198`	`DROP TABLE a;`
`199`		`-SELECTclean_aqo_data();`
`200`		`-clean_aqo_data`
`201`		`-----------------`
`202`		`-`
	`199`	`+SELECTaqo_cleanup();`
	`200`	`+aqo_cleanup`
	`201`	`+-------------`
	`202`	`+(2,4)`
`203`	`203`	`(1 row)`
`204`	`204`
`205`	`205`	`/*`
`@@ -274,10 +274,10 @@ SELECT count(*) FROM aqo_query_stat WHERE`
`274`	`274`	`(1 row)`
`275`	`275`
`276`	`276`	`DROP TABLE b;`
`277`		`-SELECTclean_aqo_data();`
`278`		`-clean_aqo_data`
`279`		`-----------------`
`280`		`-`
	`277`	`+SELECTaqo_cleanup();`
	`278`	`+aqo_cleanup`
	`279`	`+-------------`
	`280`	`+(1,1)`
`281`	`281`	`(1 row)`
`282`	`282`
`283`	`283`	`-- lines corresponding to b_oid in theese tables deleted`

`‎expected/gucs.out`

Lines changed: 13 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -86,6 +86,12 @@ SELECT obj_description('public.aqo_drop_class'::regproc::oid);`
`86`	`86`	`Remove info about an query class from AQO ML knowledge base.`
`87`	`87`	`(1 row)`
`88`	`88`
	`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`	`+`
`89`	`95`	`\df show_cardinality_errors`
`90`	`96`	`List of functions`
`91`	`97`	`Schema \| Name \| Result data type \| Argument data types \| Type`
`@@ -107,4 +113,11 @@ SELECT obj_description('public.aqo_drop_class'::regproc::oid);`
`107`	`113`	`public \| aqo_drop_class \| integer \| id bigint \| func`
`108`	`114`	`(1 row)`
`109`	`115`
	`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`	`+`
`110`	`123`	`DROP EXTENSION aqo;`

`‎expected/temp_tables.out`

Lines changed: 12 additions & 12 deletions

Original file line number	Diff line number	Diff line change
`@@ -46,10 +46,10 @@ SELECT count(*) FROM aqo_data;`
`46`	`46`	`(1 row)`
`47`	`47`
`48`	`48`	`DROP TABLE tt;`
`49`		`-SELECTclean_aqo_data();`
`50`		`-clean_aqo_data`
`51`		`-----------------`
`52`		`-`
	`49`	`+SELECTaqo_cleanup();`
	`50`	`+aqo_cleanup`
	`51`	`+-------------`
	`52`	`+(0,0)`
`53`	`53`	`(1 row)`
`54`	`54`
`55`	`55`	`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`
`59`	`59`	`(1 row)`
`60`	`60`
`61`	`61`	`DROP TABLE pt;`
`62`		`-SELECTclean_aqo_data();`
`63`		`-clean_aqo_data`
`64`		`-----------------`
`65`		`-`
	`62`	`+SELECTaqo_cleanup();`
	`63`	`+aqo_cleanup`
	`64`	`+-------------`
	`65`	`+(3,10)`
`66`	`66`	`(1 row)`
`67`	`67`
`68`	`68`	`SELECT count(*) FROM aqo_data; -- Should be 0`
`@@ -133,10 +133,10 @@ SELECT * FROM check_estimated_rows('`
`133`	`133`
`134`	`134`	`SET aqo.mode = 'forced'; -- Now we use all fss records for each query`
`135`	`135`	`DROP TABLE pt;`
`136`		`-SELECTclean_aqo_data();`
`137`		`-clean_aqo_data`
`138`		`-----------------`
`139`		`-`
	`136`	`+SELECTaqo_cleanup();`
	`137`	`+aqo_cleanup`
	`138`	`+-------------`
	`139`	`+(2,6)`
`140`	`140`	`(1 row)`
`141`	`141`
`142`	`142`	`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 number	Diff line number	Diff line change
`@@ -579,10 +579,10 @@ ORDER BY (md5(query_text),error) DESC;`
`579`	`579`	`(12 rows)`
`580`	`580`
`581`	`581`	`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)`
`586`	`586`	`(1 row)`
`587`	`587`
`588`	`588`	`-- Look for any remaining queries in the ML storage.`

`‎sql/aqo_learn.sql`

Lines changed: 1 addition & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -123,7 +123,7 @@ WHERE t1.a = t2.b AND t2.a = t3.b AND t3.a = t4.b;`
`123`	`123`	`SELECTcount(*)FROM tmp1;`
`124`	`124`
`125`	`125`	`-- Remove data on some unneeded instances of tmp1 table.`
`126`		`-SELECTpublic.clean_aqo_data();`
	`126`	`+SELECTpublic.aqo_cleanup();`
`127`	`127`
`128`	`128`	`-- Result of the query below should be empty`
`129`	`129`	`SELECT*FROM aqo_query_texts aqt1, aqo_query_texts aqt2`

`‎sql/clean_aqo_data.sql`

Lines changed: 5 additions & 5 deletions

Original file line number	Diff line number	Diff line change
`@@ -6,7 +6,7 @@ DROP TABLE IF EXISTS b;`
`6`	`6`	`CREATETABLEa();`
`7`	`7`	`SELECT*FROM a;`
`8`	`8`	`SELECT'a'::regclass::oidAS a_oid \gset`
`9`		`-SELECTclean_aqo_data();`
	`9`	`+SELECTaqo_cleanup();`
`10`	`10`
`11`	`11`	`/*`
`12`	`12`	`* lines with a_oid in aqo_data,`
`@@ -26,7 +26,7 @@ SELECT count(*) FROM aqo_query_stat WHERE`
`26`	`26`	`aqo_queries.fspace_hash= ANY(SELECTaqo_data.fspace_hashFROM aqo_dataWHERE :a_oid=ANY(oids)));`
`27`	`27`
`28`	`28`	`DROPTABLE a;`
`29`		`-SELECTclean_aqo_data();`
	`29`	`+SELECTaqo_cleanup();`
`30`	`30`
`31`	`31`	`/*`
`32`	`32`	`* lines with a_oid in aqo_data,`
`@@ -54,7 +54,7 @@ SELECT 'a'::regclass::oid AS a_oid \gset`
`54`	`54`	`-- add manually line with different fspace_hash and query_hash to aqo_queries`
`55`	`55`	`INSERT INTO aqo_queriesVALUES (:a_oid+1,'t','t', :a_oid,'f');`
`56`	`56`	`DROPTABLE a;`
`57`		`-SELECTclean_aqo_data();`
	`57`	`+SELECTaqo_cleanup();`
`58`	`58`	`-- this line should remain`
`59`	`59`	`SELECTcount(*)FROM aqo_queriesWHERE (fspace_hash= :a_oidAND query_hash= :a_oid+1);`
`60`	`60`
`@@ -88,7 +88,7 @@ SELECT count(*) FROM aqo_query_stat WHERE`
`88`	`88`	`aqo_queries.fspace_hash= ANY(SELECTaqo_data.fspace_hashFROM aqo_dataWHERE :b_oid=ANY(oids)));`
`89`	`89`
`90`	`90`	`DROPTABLE a;`
`91`		`-SELECTclean_aqo_data();`
	`91`	`+SELECTaqo_cleanup();`
`92`	`92`
`93`	`93`	`/*`
`94`	`94`	`* 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`
`124`	`124`	`aqo_queries.fspace_hash=aqo_queries.query_hash);`
`125`	`125`
`126`	`126`	`DROPTABLE b;`
`127`		`-SELECTclean_aqo_data();`
	`127`	`+SELECTaqo_cleanup();`
`128`	`128`
`129`	`129`	`-- lines corresponding to b_oid in theese tables deleted`
`130`	`130`	`SELECTcount(*)FROM aqo_dataWHERE :b_oid=ANY(oids);`

`‎sql/gucs.sql`

Lines changed: 2 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -26,9 +26,11 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)`
`26`	`26`	`SELECT obj_description('public.show_cardinality_errors'::regproc::oid);`
`27`	`27`	`SELECT obj_description('public.show_execution_time'::regproc::oid);`
`28`	`28`	`SELECT obj_description('public.aqo_drop_class'::regproc::oid);`
	`29`	`+SELECT obj_description('public.aqo_cleanup'::regproc::oid);`
`29`	`30`
`30`	`31`	`\df show_cardinality_errors`
`31`	`32`	`\df show_execution_time`
`32`	`33`	`\df aqo_drop_class`
	`34`	`+\df aqo_cleanup`
`33`	`35`
`34`	`36`	`DROP EXTENSION aqo;`

`‎sql/temp_tables.sql`

Lines changed: 3 additions & 3 deletions

Original file line number	Diff line number	Diff line change
`@@ -16,10 +16,10 @@ SELECT count(*) FROM pt AS pt1, tt AS tt1, tt AS tt2, pt AS pt2;`
`16`	`16`	`SELECTcount(*)FROM aqo_data;`
`17`	`17`
`18`	`18`	`DROPTABLE tt;`
`19`		`-SELECTclean_aqo_data();`
	`19`	`+SELECTaqo_cleanup();`
`20`	`20`	`SELECTcount(*)FROM aqo_data;-- Should be the same as above`
`21`	`21`	`DROPTABLE pt;`
`22`		`-SELECTclean_aqo_data();`
	`22`	`+SELECTaqo_cleanup();`
`23`	`23`	`SELECTcount(*)FROM aqo_data;-- Should be 0`
`24`	`24`	`SELECT query_textFROM aqo_queries aqLEFT JOIN aqo_query_texts aqt`
`25`	`25`	`ONaq.query_hash=aqt.query_hash`
`@@ -66,7 +66,7 @@ SELECT * FROM check_estimated_rows('`
`66`	`66`
`67`	`67`	`SETaqo.mode='forced';-- Now we use all fss records for each query`
`68`	`68`	`DROPTABLE pt;`
`69`		`-SELECTclean_aqo_data();`
	`69`	`+SELECTaqo_cleanup();`
`70`	`70`	`CREATETABLEptASSELECT xAS x, (x %10)AS yFROM generate_series(1,100)AS x;`
`71`	`71`	`CREATE TEMP TABLE ttd1AS`
`72`	`72`	`SELECT-(x*3)AS x, (x %9)AS y1FROM generate_series(1,100)AS x;`

`‎sql/unsupported.sql`

Lines changed: 1 addition & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -163,7 +163,7 @@ ORDER BY (md5(query_text),error) DESC;`
`163`	`163`
`164`	`164`	`DROPTABLE t,t1 CASCADE;`
`165`	`165`
`166`		`-SELECTpublic.clean_aqo_data();`
	`166`	`+SELECTpublic.aqo_cleanup();`
`167`	`167`
`168`	`168`	`-- Look for any remaining queries in the ML storage.`
`169`	`169`	`SELECT to_char(error,'9.99EEEE')::textAS error, query_text`

`‎t/001_pgbench.pl`

Lines changed: 1 addition & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -272,7 +272,7 @@`
`272`	`272`	`pgbench_history CASCADE;");`
`273`	`273`
`274`	`274`	`# Clean unneeded AQO knowledge`
`275`		`-$node->safe_psql('postgres',"SELECTclean_aqo_data()");`
	`275`	`+$node->safe_psql('postgres',"SELECTpublic.aqo_cleanup()");`
`276`	`276`
`277`	`277`	`# Calculate total number of rows in AQO-related tables.`
`278`	`278`	`my$new_fs_count =$node->safe_psql('postgres',"SELECT count(*) FROM aqo_queries;");`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commit678cfaf

File tree

12 files changed

12 files changed

`‎aqo--1.4--1.5.sql`

`‎expected/aqo_learn.out`

`‎expected/clean_aqo_data.out`

`‎expected/gucs.out`

`‎expected/temp_tables.out`

`‎expected/unsupported.out`

`‎sql/aqo_learn.sql`

`‎sql/clean_aqo_data.sql`

`‎sql/gucs.sql`

`‎sql/temp_tables.sql`

`‎sql/unsupported.sql`

`‎t/001_pgbench.pl`

0 commit comments