ALTERTABLEpublic.aqo_data ADD COLUMN reliabilitydouble precision [];

DROPFUNCTIONpublic.top_error_queries(int);

CREATE OR REPLACEFUNCTIONpublic.show_cardinality_errors()

RETURNS TABLE(numbigint, idbigint, error float)

CREATE OR REPLACEFUNCTIONpublic.show_cardinality_errors(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, qhash)DESC)AS nn,

qhash, cerror

FROM (

aq.query_hashAS qhash,

cardinality_error_with_aqo[array_length(cardinality_error_with_aqo,1)]AS cerror

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;

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,

array_avg(cardinality_error_without_aqo)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 lastquery execution. Return querieshaving the largest error.';

'Get cardinality error ofqueries thelasttime they were executed. Order queriesaccording to an error value.';

-- Check existence of the interface functions.

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

obj_description

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

Get cardinality error of lastquery execution. Return querieshaving the largest error.

obj_description

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

Get cardinality error ofqueries thelasttime they were executed. Order queriesaccording to an error value.

(1 row)

DROP EXTENSION aqo;

SET aqo.mode = 'disabled';

SET aqo.force_collect_stat = 'on';

--

-- num of generate_series(1,1000000) query should be the first

-- Dummy test. CREATE TABLE shouldn't find in the ML storage. But a simple

-- select must be in. Also here we test on gathering a stat on temp and plain

-- relations.

--

SELECT count(*) FROM generate_series(1,1000000);

count

---------

1000000

CREATE TEMP TABLE ttt AS SELECT count(*) AS cnt FROM generate_series(1,10);

CREATE TABLE ttp AS SELECT count(*) AS cnt FROM generate_series(1,10);

SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0;

cnt

-----

0

(1 row)

SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0;

cnt

-----

0

(1 row)

SELECT num FROM top_time_queries(10) AS tt WHERE

tt.fspace_hash = (SELECT fspace_hash FROM aqo_queries WHERE

aqo_queries.query_hash = (SELECT aqo_query_texts.query_hash FROM aqo_query_texts

WHERE query_text = 'SELECT count(*) FROM generate_series(1,1000000);'));

NOTICE: Top 10 execution time queries

SELECT num FROM top_time_queries(3);

NOTICE: Top 3 execution time queries

num

-----

1

(1 row)

2

(2 rows)

--

-- num of query uses table t2 should be bigger than num of query uses table t1 and be the first

31

(1 row)

SELECT num FROM top_error_queries(10) AS te WHERE

te.fspace_hash = (SELECT fspace_hash FROM aqo_queries WHERE

aqo_queries.query_hash = (SELECT aqo_query_texts.query_hash FROM aqo_query_texts

WHERE query_text = 'SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'));

NOTICE: Top 10 cardinality error queries

num

-----

1

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

WHERE te.fshash = (

SELECT fspace_hash FROM aqo_queries

WHERE aqo_queries.query_hash = (

SELECT aqo_query_texts.query_hash FROM aqo_query_texts

WHERE query_text = 'SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'

)

);

num | to_char

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

1 | 1.94e+00

(1 row)

-- Should return zero

SELECT count(*) FROM show_cardinality_errors(true);

count

-------

0

(1 row)

SELECT

num,

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

FROM public.show_cardinality_errors()

FROM public.show_cardinality_errors(true)

WHERE error > 0.;

num | error

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

1 |9.69e+02

2 |1.15e+02

1 |1.15e+02

2 |3.00e+01

3 | 3.00e+01

4 | 3.00e+01

5 | 3.00e+01

6 | 1.33e+00

(6 rows)

5 | 1.33e+00

(5 rows)

DROP TABLE t,t1 CASCADE;

SELECT public.clean_aqo_data();

-- TODO: figure out with remaining queries in the ML storage.

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

FROM public.show_cardinality_errors() cef, aqo_query_texts aqt

FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt

WHERE aqt.query_hash = cef.id;

num | error | query_text

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

1 | 9.69e+02 | SELECT str FROM expln(' +

| | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +

| | SELECT count(*) FROM +

| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1 +

| | JOIN +

| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2+

| | ON q1.x = q2.x+1; +

| | ') AS str WHERE str NOT LIKE '%Memory Usage%';

2 | 3.27e+02 | SELECT +

| | num, +

| | to_char(error, '9.99EEEE')::text AS error +

| | FROM public.show_cardinality_errors() +

| | WHERE error > 0.;

3 | 0.00e+00 | CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;

4 | 0.00e+00 | SELECT public.clean_aqo_data();

5 | 0.00e+00 | CREATE TABLE t1 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y +

| | FROM generate_series(1,1000) AS gs;

(5 rows)

num | error | query_text

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

(0 rows)

DROP EXTENSION aqo;

isQueryUsingSystemRelation(Query*query)

{

returnisQueryUsingSystemRelation_walker((Node*)query,NULL);

booltrivQuery= true;

boolresult;

result=isQueryUsingSystemRelation_walker((Node*)query,&trivQuery);

if (result||trivQuery)

return true;

return false;

}

Relationrel=table_open(rte->relid,AccessShareLock);

boolis_catalog=IsCatalogRelation(rel);

boolis_aqo_rel=IsAQORelation(rel);

bool*trivQuery= (bool*)context;

table_close(rel,AccessShareLock);

if (is_catalog||is_aqo_rel)

return true;

*trivQuery= false;

}

elseif (rte->rtekind==RTE_FUNCTION)

{

SETaqo.force_collect_stat='on';

SELECTcount(*)FROM generate_series(1,1000000);

SELECT numFROM top_time_queries(10)ASttWHERE

WHERE query_text='SELECTcount(*) FROMgenerate_series(1,1000000);'));

CREATE TEMP TABLE tttASSELECTcount(*)AS cntFROM generate_series(1,10);

CREATETABLEttpASSELECTcount(*)AScntFROM generate_series(1,10);

SELECTnumFROMtop_time_queries(3);

SELECTcount(*)FROM (SELECT x, yFROM t1GROUP BY GROUPING SETS ((x,y), (x), (y), ()))AS q1;

SELECTcount(*)FROM (SELECT x, yFROM t2GROUP BY GROUPING SETS ((x,y), (x), (y), ()))AS q1;

SELECT numFROM top_error_queries(10)AS teWHERE

te.fspace_hash= (SELECT fspace_hashFROM aqo_queriesWHERE

aqo_queries.query_hash= (SELECTaqo_query_texts.query_hashFROM aqo_query_texts

WHERE query_text='SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'));

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

WHEREte.fshash= (

SELECT fspace_hashFROM aqo_queries

WHEREaqo_queries.query_hash= (

SELECTaqo_query_texts.query_hashFROM aqo_query_texts

WHERE query_text='SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'

)

);

SELECTcount(*)FROM show_cardinality_errors(true);

num,

to_char(error,'9.99EEEE')::textAS error

FROMpublic.show_cardinality_errors()

FROMpublic.show_cardinality_errors(true)

WHERE error>0.;

DROPTABLE t,t1 CASCADE;

SELECT num, to_char(error,'9.99EEEE')::textAS error, query_text

FROMpublic.show_cardinality_errors() cef, aqo_query_texts aqt

FROMpublic.show_cardinality_errors(true) cef, aqo_query_texts aqt

WHEREaqt.query_hash=cef.id;

DROP EXTENSION aqo;

'analytical queries in pgbench (disabled mode)');

$res =$node->safe_psql('postgres',

WHERE v.error > 0. AND t.query_text LIKE '%pgbench_accounts%'");

is($res, 3);

$res =$node->safe_psql('postgres',

WHERE v.error > 0. AND t.query_text LIKE '%pgbench_accounts%'");

note("\n TopN:\n$res\n");

$res =$node->safe_psql('postgres',

WHERE v.error > 0.");

note("\n Queries:\n$res\n");

$res =$node->safe_psql('postgres',

WHERE v.execution_time > 0.");

is($res,5);

is($res,3);