DROPTABLEpublic.aqo_data CASCADE;

CREATETABLEpublic.aqo_data (

fspace_hashbigintNOT NULLREFERENCESpublic.aqo_queriesON DELETE CASCADE,

fsspace_hashintNOT NULL,

nfeaturesintNOT NULL,

featuresdouble precision[][],

targetsdouble precision[],

oidsoid [] DEFAULTNULL,

reliabilitydouble precision []

fspace_hashbigintNOT NULLREFERENCESpublic.aqo_queriesON DELETE CASCADE,

fsspace_hashintNOT NULL,

nfeaturesintNOT NULL,

featuresdouble precision[][],

targetsdouble precision[],

oidsoid [] DEFAULTNULL,

reliabilitydouble precision []

);

CREATEUNIQUE INDEXaqo_fss_access_idxONpublic.aqo_data (fspace_hash, fsspace_hash);

DROPFUNCTIONpublic.top_time_queries;

DROPFUNCTIONpublic.aqo_drop;

DROPFUNCTIONpublic.clean_aqo_data;

DROPFUNCTIONpublic.show_cardinality_errors;

DROPFUNCTION array_mse;

DROPFUNCTION array_avg;

DROPFUNCTIONpublic.aqo_ne_queries;-- Not needed anymore due to changing in the logic

DROPFUNCTIONpublic.aqo_clear_hist;-- Should be renamed and reworked

CREATE OR REPLACEFUNCTIONpublic.show_execution_time(controlledboolean)

CREATE OR REPLACEFUNCTIONpublic.aqo_execution_time(controlledboolean)

RETURNS TABLE(numbigint, idbigint, fshashbigint, exec_time float, nexecsbigint)

AS $$

aq.query_hashAS queryid,

aq.fspace_hashAS fs_hash,

array_avg(execution_time_without_aqo)AS exectime,

(SELECTAVG(t)FROM unnest(execution_time_without_aqo) t)AS exectime,

executions_without_aqoAS execs

FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs

ONaq.query_hash=aqs.query_hash

END;

$$ LANGUAGE plpgsql;

'Get execution time of queries. If controlled = true (AQO could advise cardinality estimations), show time of last execution attempt. Another case (AQO not used), return an average value of execution time across all known executions.';

CREATE OR REPLACEFUNCTIONpublic.aqo_drop_class(idbigint)

CREATE OR REPLACEFUNCTIONpublic.aqo_drop_class(queryidbigint)

RETURNSintegerAS $$

DECLARE

fsbigint;

numinteger;

IF (id=0) THEN

raise EXCEPTION'[AQO] Cannot remove basic class %.',id;

IF (queryid=0) THEN

raise EXCEPTION'[AQO] Cannot remove basic class %.',queryid;

END IF;

SELECT fspace_hashFROMpublic.aqo_queriesWHERE (query_hash=id) INTO fs;

SELECT fspace_hashFROMpublic.aqo_queriesWHERE (query_hash=queryid) INTO fs;

IF (fs ISNULL) THEN

raise WARNING'[AQO] Nothing to remove for the class %.',id;

raise WARNING'[AQO] Nothing to remove for the class %.',queryid;

RETURN0;

END IF;

IF (fs<>id) THEN

raise WARNING'[AQO] Removing query class has non-generic feature space value: id = %, fs = %.',id, fs;

IF (fs<>queryid) THEN

raise WARNING'[AQO] Removing query class has non-generic feature space value: id = %, fs = %.',queryid, fs;

END IF;

SELECTcount(*)FROMpublic.aqo_dataWHERE fspace_hash= fs INTO num;

DELETEFROMpublic.aqo_queriesWHERE query_hash=id;

DELETEFROMpublic.aqo_queriesWHERE query_hash=queryid;

RETURN num;

END;

$$ LANGUAGE plpgsql;

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

AS $$

DECLARE

fsbigint;

fssinteger;

fsbigint;

fssinteger;

SELECTcount(*)FROM aqo_queries INTO nfs;

'Remove unneeded rows from the AQO ML storage';

CREATE OR REPLACEFUNCTIONpublic.aqo_cardinality_error(controlledboolean)

RETURNS TABLE(numbigint, idbigint, fshashbigint, error float, nexecsbigint)

AS $$

IF (controlled) THEN

RETURN QUERY

row_number() OVER (ORDER BY (cerror, query_id, fs_hash)DESC)AS nn,

query_id, fs_hash, cerror, execs

FROM (

aq.query_hashAS query_id,

aq.fspace_hashAS fs_hash,

cardinality_error_with_aqo[array_length(cardinality_error_with_aqo,1)]AS cerror,

executions_with_aqoAS execs

FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs

ONaq.query_hash=aqs.query_hash

WHERE TRUE= ANY (SELECT unnest(cardinality_error_with_aqo)IS NOT NULL)

)AS q1

ORDER BY nnASC;

ELSE

RETURN QUERY

row_number() OVER (ORDER BY (cerror, query_id, fs_hash)DESC)AS nn,

query_id, fs_hash, cerror, execs

FROM (

aq.query_hashAS query_id,

aq.fspace_hashAS fs_hash,

(SELECTAVG(t)FROM unnest(cardinality_error_without_aqo) t)AS cerror,

executions_without_aqoAS execs

FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs

ONaq.query_hash=aqs.query_hash

WHERE TRUE= ANY (SELECT unnest(cardinality_error_without_aqo)IS NOT NULL)

)AS q1

ORDER BY (nn)ASC;

END IF;

END;

$$ LANGUAGE plpgsql;

'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';

CREATE OR REPLACEFUNCTIONpublic.aqo_reset_query(queryidbigint)

RETURNSintegerAS $$

DECLARE

numinteger;

fsbigint;

IF (queryid=0) THEN

raise WARNING'[AQO] Reset common feature space.'

END IF;

SELECT fspace_hashFROMpublic.aqo_queriesWHERE query_hash= queryid INTO fs;

SELECTcount(*)FROMpublic.aqo_dataWHERE fspace_hash= fs INTO num;

DELETEFROMpublic.aqo_dataWHERE fspace_hash= fs;

RETURN num;

END;

$$ LANGUAGE plpgsql;

'Remove from AQO storage only learning data for given QueryId.';

off

(1 row)

SHOWaqo.show_details;

SHOW aqo.show_details;

aqo.show_details

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

off

off

(1 row)

SHOWaqo.mode;

SHOW aqo.mode;

aqo.mode

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

controlled

(6 rows)

-- Check existence of the interface functions.

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

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

obj_description

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

Get cardinality error of queries the last time they were executed. Order queries according to an error value.

(1 row)

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

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

obj_description

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

Get execution time of queries. If controlled = true (AQO could advise cardinality estimations), show time of last execution attempt. Another case (AQO not used), return an average value of execution time across all known executions.

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 | show_cardinality_errors | TABLE(num bigint, id bigint, fshash bigint, error double precision, nexecs bigint) | controlled boolean | func

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

obj_description

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

Remove from AQO storage only learning data for given QueryId.

(1 row)

\df aqo_cardinality_error

List of functions

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

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

public | aqo_cardinality_error | TABLE(num bigint, id bigint, fshash bigint, error double precision, nexecs bigint) | controlled boolean | func

(1 row)

\dfshow_execution_time

\dfaqo_execution_time

List of functions

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

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

public |show_execution_time | TABLE(num bigint, id bigint, fshash bigint, exec_time double precision, nexecs bigint) | controlled boolean | func

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

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

public |aqo_execution_time | TABLE(num bigint, id bigint, fshash bigint, exec_time double precision, nexecs bigint) | controlled boolean | func

(1 row)

\df aqo_drop_class

List of functions

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

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

public | aqo_drop_class | integer |id bigint | func

public | aqo_drop_class | integer |queryid bigint | func

(1 row)

\df aqo_cleanup

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

(1 row)

\df aqo_reset_query

List of functions

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

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

public | aqo_reset_query | integer | queryid bigint | func

(1 row)

DROP EXTENSION aqo;

0

(1 row)

SELECT num FROMshow_execution_time(true); -- Just for checking, return zero.

SELECT num FROMaqo_execution_time(true); -- Just for checking, return zero.

num

-----

(0 rows)

SELECT num FROMshow_execution_time(false);

SELECT num FROMaqo_execution_time(false);

num

-----

1

-- Without the AQO control queries with and without temp tables are logged.

SELECT query_text,nexecs

FROMshow_execution_time(false) ce, aqo_query_texts aqt

FROMaqo_execution_time(false) ce, aqo_query_texts aqt

WHERE ce.id = aqt.query_hash

ORDER BY (md5(query_text));

query_text | nexecs

31

(1 row)

SELECT num, to_char(error, '9.99EEEE') FROMshow_cardinality_errors(false) AS te

SELECT num, to_char(error, '9.99EEEE') FROMaqo_cardinality_error(false) AS te

WHERE te.fshash = (

SELECT fspace_hash FROM aqo_queries

WHERE aqo_queries.query_hash = (

(1 row)

-- Should return zero

SELECT count(*) FROMshow_cardinality_errors(true);

SELECT count(*) FROMaqo_cardinality_error(true);

count

-------

0

(1 row)

-- Fix list of logged queries

SELECT query_text,nexecs

FROMshow_cardinality_errors(false) ce, aqo_query_texts aqt

FROMaqo_cardinality_error(false) ce, aqo_query_texts aqt

WHERE ce.id = aqt.query_hash

ORDER BY (md5(query_text));

query_text | nexecs

JOINS: 0

(13 rows)

-- AQOneed to predict total fetched tuples in a table.

-- AQOneeds to predict total fetched tuples in a table.

--

-- At a non-leaf node we have prediction about input tuples - is a number of

-- predicted output rows in underlying node. But for Scan nodes we don't have

-- XXX: Do we stuck into an unstable behavior of an error value?

-- Live with this variant of the test for some time.

SELECT to_char(error, '9.99EEEE')::text AS error, query_text

FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt

FROM public.aqo_cardinality_error(true) cef, aqo_query_texts aqt

WHERE aqt.query_hash = cef.id

ORDER BY (md5(query_text),error) DESC;

error | query_text

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

SELECT to_char(error, '9.99EEEE')::text AS error, query_text

FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt

FROM public.aqo_cardinality_error(true) cef, aqo_query_texts aqt

WHERE aqt.query_hash = cef.id

ORDER BY (md5(query_text),error) DESC;

error | query_text

SHOWaqo.join_threshold;

SHOWaqo.learn_statement_timeout;

SHOWaqo.show_hash;

SHOWaqo.show_details;

SHOWaqo.show_details;

SHOWaqo.force_collect_stat;

SHOWaqo.mode;

SHOWaqo.mode;

SETaqo.mode='learn';

SETaqo.show_details= true;

SELECT xFROM t;

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

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

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

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

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

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

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

\dfshow_cardinality_errors

\dfshow_execution_time

\dfaqo_cardinality_error

\dfaqo_execution_time

\df aqo_drop_class

\df aqo_cleanup

\df aqo_reset_query

DROP EXTENSION aqo;