DROPFUNCTIONpublic.top_time_queries;

DROPFUNCTIONpublic.aqo_drop;

DROPFUNCTIONpublic.clean_aqo_data;

$$ LANGUAGE plpgsql;

'Remove info about an query class from AQO ML knowledge base.';

'Remove info about an query class from AQO ML knowledge base.';

CREATE OR REPLACEFUNCTIONpublic.aqo_cleanup(OUT nfsinteger, OUT nfssinteger)

AS $$

DECLARE

fsbigint;

fssinteger;

SELECTcount(*)FROM aqo_queries INTO nfs;

SELECTcount(*)FROM aqo_data INTO nfss;

FOR fs,fssINSELECTq1.fs,q1.fssFROM (

SELECT fspace_hash fs, fsspace_hash fss, unnest(oids)AS reloid

FROM aqo_data)AS q1

WHEREq1.reloid NOTIN (SELECToidFROM pg_class)

GROUP BY (q1.fs,q1.fss)

LOOP

IF (fs=0) THEN

DELETEFROM aqo_dataWHERE fsspace_hash= fss;

continue;

END IF;

DELETEFROM aqo_queriesWHERE fspace_hash= fs;

END LOOP;

nfs := nfs- (SELECTcount(*)FROM aqo_queries);

nfss := nfss- (SELECTcount(*)FROM aqo_data);

END;

$$ LANGUAGE plpgsql;

'Remove unneeded rows from the AQO ML storage';

(1 row)

-- Remove data on some unneeded instances of tmp1 table.

SELECT public.clean_aqo_data();

----------------

SELECT public.aqo_cleanup();

-------------

(1 row)

-- Result of the query below should be empty

(0 rows)

SELECT 'a'::regclass::oid AS a_oid \gset

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

/*

(1 row)

DROP TABLE a;

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

/*

-- add manually line with different fspace_hash and query_hash to aqo_queries

INSERT INTO aqo_queries VALUES (:a_oid + 1, 't', 't', :a_oid, 'f');

DROP TABLE a;

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

-- this line should remain

(1 row)

DROP TABLE a;

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

/*

(1 row)

DROP TABLE b;

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

-- lines corresponding to b_oid in theese tables deleted

Remove info about an query class from AQO ML knowledge base.

(1 row)

SELECT obj_description('public.aqo_cleanup'::regproc::oid);

obj_description

----------------------------------------------

Remove unneeded rows from the AQO ML storage

(1 row)

\df show_cardinality_errors

List of functions

Schema | Name | Result data type | Argument data types | Type

public | aqo_drop_class | integer | id bigint | func

(1 row)

\df aqo_cleanup

List of functions

Schema | Name | Result data type | Argument data types | Type

--------+-------------+------------------+-----------------------------------+------

public | aqo_cleanup | record | OUT nfs integer, OUT nfss integer | func

(1 row)

DROP EXTENSION aqo;

(1 row)

DROP TABLE tt;

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

SELECT count(*) FROM aqo_data; -- Should be the same as above

(1 row)

DROP TABLE pt;

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

SELECT count(*) FROM aqo_data; -- Should be 0

SET aqo.mode = 'forced'; -- Now we use all fss records for each query

DROP TABLE pt;

SELECTclean_aqo_data();

----------------

SELECTaqo_cleanup();

-------------

(1 row)

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

(12 rows)

DROP TABLE t,t1 CASCADE;

SELECT public.clean_aqo_data();

----------------

SELECT public.aqo_cleanup();

-------------

(1 row)

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

SELECTcount(*)FROM tmp1;

SELECTpublic.clean_aqo_data();

SELECTpublic.aqo_cleanup();

SELECT*FROM aqo_query_texts aqt1, aqo_query_texts aqt2

CREATETABLEa();

SELECT*FROM a;

SELECT'a'::regclass::oidAS a_oid \gset

SELECTclean_aqo_data();

SELECTaqo_cleanup();

aqo_queries.fspace_hash= ANY(SELECTaqo_data.fspace_hashFROM aqo_dataWHERE :a_oid=ANY(oids)));

DROPTABLE a;

SELECTclean_aqo_data();

SELECTaqo_cleanup();

INSERT INTO aqo_queriesVALUES (:a_oid+1,'t','t', :a_oid,'f');

DROPTABLE a;

SELECTclean_aqo_data();

SELECTaqo_cleanup();

SELECTcount(*)FROM aqo_queriesWHERE (fspace_hash= :a_oidAND query_hash= :a_oid+1);

aqo_queries.fspace_hash= ANY(SELECTaqo_data.fspace_hashFROM aqo_dataWHERE :b_oid=ANY(oids)));

DROPTABLE a;

SELECTclean_aqo_data();

SELECTaqo_cleanup();

aqo_queries.fspace_hash=aqo_queries.query_hash);

DROPTABLE b;

SELECTclean_aqo_data();

SELECTaqo_cleanup();

SELECTcount(*)FROM aqo_dataWHERE :b_oid=ANY(oids);

SELECT obj_description('public.show_cardinality_errors'::regproc::oid);

SELECT obj_description('public.show_execution_time'::regproc::oid);

SELECT obj_description('public.aqo_drop_class'::regproc::oid);

SELECT obj_description('public.aqo_cleanup'::regproc::oid);

\df show_cardinality_errors

\df show_execution_time

\df aqo_drop_class

\df aqo_cleanup

DROP EXTENSION aqo;

SELECTcount(*)FROM aqo_data;

DROPTABLE tt;

SELECTclean_aqo_data();

SELECTaqo_cleanup();

SELECTcount(*)FROM aqo_data;-- Should be the same as above

DROPTABLE pt;

SELECTclean_aqo_data();

SELECTaqo_cleanup();

SELECTcount(*)FROM aqo_data;-- Should be 0

SELECT query_textFROM aqo_queries aqLEFT JOIN aqo_query_texts aqt

ONaq.query_hash=aqt.query_hash

SETaqo.mode='forced';-- Now we use all fss records for each query

DROPTABLE pt;

SELECTclean_aqo_data();

SELECTaqo_cleanup();

CREATETABLEptASSELECT xAS x, (x %10)AS yFROM generate_series(1,100)AS x;

CREATE TEMP TABLE ttd1AS

SELECT-(x*3)AS x, (x %9)AS y1FROM generate_series(1,100)AS x;