voidaqo_ExecutorStart(QueryDesc*queryDesc,inteflags);

voidaqo_ExecutorRun(QueryDesc*queryDesc,ScanDirectiondirection,

uint64count,boolexecute_once);

voidincrease_flex_timeout(uint64query_hash,int64flex_timeout_fin_time);

voidincrease_flex_timeout(int64flex_timeout_fin_time);

voidaqo_ExecutorEnd(QueryDesc*queryDesc);

SET statement_timeout = 5000; -- [0.8s]

DROP TABLE IF EXISTS a,b CASCADE;

NOTICE: table "a" does not exist, skipping

NOTICE: table "b" does not exist, skipping

CREATE TABLE a (x int);

INSERT INTO a (x) SELECT mod(ival,10) FROM generate_series(1,1000) As ival;

CREATE TABLE b (y int);

INSERT INTO b (y) SELECT mod(ival + 1,10) FROM generate_series(1,1000) As ival;

CREATE EXTENSION IF NOT EXISTS aqo;

SET aqo.join_threshold = 0;

SET aqo.mode = 'learn';

SET aqo.show_details = 'off';

SET aqo.learn_statement_timeout = 'on';

SET aqo.statement_timeout = 4; -- [0.8s]

SELECT count(y), pg_sleep(3) FROM a,b where x > 2 * (select min(x) from A,B where x = y);

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 0.000000

count | pg_sleep

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

900000 |

(1 row)

select * from aqo_queries where query_hash <> 0;

query_hash | learn_aqo | use_aqo | fspace_hash | auto_tuning | flex_timeout | count_increase_timeout

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

42929091640608990 | t | t | 42929091640608990 | f | 5 | 1

(1 row)

SELECT count(y), pg_sleep(3) FROM a,b where x > 3 * (select min(x) from A,B where x = y);

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 0.866297

count | pg_sleep

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

900000 |

(1 row)

select * from aqo_queries where query_hash <> 0;

query_hash | learn_aqo | use_aqo | fspace_hash | auto_tuning | flex_timeout | count_increase_timeout

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

42929091640608990 | t | t | 42929091640608990 | f | 5 | 1

(1 row)

SELECT count(y), pg_sleep(3) FROM a,b where x > 3 * (select min(x) from A,B where x = y);

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 0.435709

count | pg_sleep

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

900000 |

(1 row)

select * from aqo_queries where query_hash <> 0;

query_hash | learn_aqo | use_aqo | fspace_hash | auto_tuning | flex_timeout | count_increase_timeout

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

42929091640608990 | t | t | 42929091640608990 | f | 5 | 1

(1 row)

SELECT count(y), pg_sleep(3) FROM a,b where x > (select min(x) from A,B where x = y);

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 0.000000

count | pg_sleep

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

900000 |

(1 row)

select * from aqo_queries where query_hash <> 0;

query_hash | learn_aqo | use_aqo | fspace_hash | auto_tuning | flex_timeout | count_increase_timeout

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

42929091640608990 | t | t | 42929091640608990 | f | 5 | 1

6582036157989373325 | t | t | 6582036157989373325 | f | 20 | 2

(2 rows)

SELECT count(y), pg_sleep(3) FROM a,b where x > (select min(x) from A,B where x = y);

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 0.866297

count | pg_sleep

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

900000 |

(1 row)

select * from aqo_queries where query_hash <> 0;

query_hash | learn_aqo | use_aqo | fspace_hash | auto_tuning | flex_timeout | count_increase_timeout

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

42929091640608990 | t | t | 42929091640608990 | f | 5 | 1

6582036157989373325 | t | t | 6582036157989373325 | f | 20 | 2

(2 rows)

SELECT count(y), pg_sleep(3) FROM a,b where x > (select min(x) from A,B where x = y);

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 0.435709

count | pg_sleep

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

900000 |

(1 row)

select * from aqo_queries where query_hash <> 0;

query_hash | learn_aqo | use_aqo | fspace_hash | auto_tuning | flex_timeout | count_increase_timeout

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

42929091640608990 | t | t | 42929091640608990 | f | 5 | 1

6582036157989373325 | t | t | 6582036157989373325 | f | 20 | 2

(2 rows)

DROP EXTENSION aqo;

SET aqo.learn_statement_timeout = 'on';

SET statement_timeout = 800; -- [0.8s]

SELECT *, pg_sleep(1) FROM t;

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 0, mean integral error is 0.000000

ERROR: canceling statement due to statement timeout

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;'); -- haven't any partial data

check_estimated_rows

-- Don't learn because running node has smaller cardinality than an optimizer prediction

SET statement_timeout = 3500;

SELECT *, pg_sleep(1) FROM t;

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 0, mean integral error is 0.000000

ERROR: canceling statement due to statement timeout

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

check_estimated_rows

-- We have a real learning data.

SET statement_timeout = 10000;

SELECT *, pg_sleep(1) FROM t;

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 0, mean integral error is 0.000000

x | pg_sleep

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

1 |

-- Force to make an underestimated prediction

DELETE FROM t WHERE x > 2;

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 0.000000

ANALYZE t;

INSERT INTO t (x) (SELECT * FROM generate_series(3,5) AS x);

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is -2.697209

TRUNCATE aqo_data;

SET statement_timeout = 800;

SELECT *, pg_sleep(1) FROM t; -- Not learned

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 2.629528

ERROR: canceling statement due to statement timeout

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

check_estimated_rows

SET statement_timeout = 3500;

SELECT *, pg_sleep(1) FROM t; -- Learn!

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 2.995732

ERROR: canceling statement due to statement timeout

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

check_estimated_rows

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

(1 row)

SET statement_timeout = 5500;

SELECT *, pg_sleep(1) FROM t; -- Get reliable data

NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is 4, mean integral error is 2.995732

x | pg_sleep

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

1 |

SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1; | 1

(4 rows)

DROP EXTENSION AQO;

predicted=clamp_row_est(predicted);

{

aqo_obj_statctx= {NIL,NIL,NIL, false, false};

QueryStat*stat=NULL;

doubleerror=0;

int64fintime=0;

if (!timeoutCtl.queryDesc|| !ExtractFromQueryEnv(timeoutCtl.queryDesc))

return;

ctx.learn=query_context.learn_aqo;

ctx.isTimedOut= true;

elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is %d",(int)get_timeout_finish_time(timeoutCtl.id));

learnOnPlanState(timeoutCtl.queryDesc->planstate, (void*)&ctx);

stat=get_aqo_stat(query_context.query_hash);

error=stat->cardinality_error_with_aqo[stat->cardinality_error_with_aqo_size-1]-cardinality_sum_errors/(1+cardinality_num_objects);

fintime= (int64)get_timeout_finish_time(timeoutCtl.id);

elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Current timeout is %ld, mean integral error is %f",fintime,error);

if (stat&&aqo_learn_statement_timeout)

{

if (error <=0.1)

{

increase_flex_timeout(fintime);

}

}

}

increase_flex_timeout(uint64query_hash,int64flex_timeout_fin_time)

increase_flex_timeout(int64flex_timeout_fin_time)

{

LOCKTAGtag;

flex_timeout_fin_time= (flex_timeout_fin_time+1)*get_increment();

init_lock_tag(&tag,query_context.query_hash,0);

LockAcquire(&tag,ExclusiveLock, false, false);

if (!update_query_timeout(query_hash,flex_timeout_fin_time,query_context.count_increase_timeout+1))

elog(NOTICE,"timeoutis not updated");

if (!update_query_timeout(query_context.query_hash,flex_timeout_fin_time,query_context.count_increase_timeout+1))

elog(NOTICE,"[AQO] Timeoutis not updated!");

LockRelease(&tag,ExclusiveLock, false);

}

list_free(ctx.selectivities);

}

stat=get_aqo_stat(query_context.query_hash);

if (cardinality_num_objects>0)

cardinality_error=cardinality_sum_errors /cardinality_num_objects;

cardinality_error=-1;

if (stat&&aqo_learn_statement_timeout&&

stat->cardinality_error_with_aqo[stat->cardinality_error_with_aqo_size-1]-cardinality_sum_errors/cardinality_num_objects <=0.1)

{

increase_flex_timeout(query_context.query_hash, (int64)get_timeout_finish_time(timeoutCtl.id));

}

if (query_context.collect_stat)

stat=get_aqo_stat(query_context.query_hash);

{

INSTR_TIME_SET_CURRENT(endtime);

INSTR_TIME_SUBTRACT(endtime,query_context.start_execution_time);

execution_time=INSTR_TIME_GET_DOUBLE(endtime);

if (cardinality_num_objects>0)

cardinality_error=cardinality_sum_errors /cardinality_num_objects;

cardinality_error=-1;

init_lock_tag(&tag,query_context.query_hash,query_context.fspace_hash);

LockAcquire(&tag,ExclusiveLock, false, false);

createfunctioncheck_estimated_rows(text) returns table (estimatedint, actualint)

language plpgsqlas

$$

declare

lntext;

tmptext[];

first_row bool := true;

for lnin

execute format('explain analyze %s', $1)

loop

if first_row then

first_row := false;

tmp := regexp_match(ln,'rows=(\d*) .* rows=(\d*)');

return queryselect tmp[1]::int, tmp[2]::int;

end if;

end loop;

end;

$$;

SET statement_timeout=5000;-- [0.8s]

DROPTABLE IF EXISTS a,b CASCADE;

SETaqo.show_details='off';

SETaqo.learn_statement_timeout='on';

SETaqo.statement_timeout=4;-- [0.8s]

SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data

select flex_timeout, count_increase_timeoutfrom aqo_querieswhere query_hash<>0;

SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data

select flex_timeout, count_increase_timeoutfrom aqo_querieswhere query_hash<>0;

SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data

select flex_timeout, count_increase_timeoutfrom aqo_querieswhere query_hash<>0;

SELECT check_estimated_rows('SELECT x FROM A,B where x < 10 and y > 10 group by(x);');-- haven't any partial data

SELECTcount(y), pg_sleep(3)FROM a,bwhere x>2* (selectmin(x)from A,Bwhere x= y);

select*from aqo_querieswhere query_hash<>0;

SELECTcount(y), pg_sleep(3)FROM a,bwhere x>3* (selectmin(x)from A,Bwhere x= y);

select*from aqo_querieswhere query_hash<>0;

SELECTcount(y), pg_sleep(3)FROM a,bwhere x>3* (selectmin(x)from A,Bwhere x= y);

select*from aqo_querieswhere query_hash<>0;

SELECTcount(y), pg_sleep(3)FROM a,bwhere x> (selectmin(x)from A,Bwhere x= y);

select*from aqo_querieswhere query_hash<>0;

SELECTcount(y), pg_sleep(3)FROM a,bwhere x> (selectmin(x)from A,Bwhere x= y);

select*from aqo_querieswhere query_hash<>0;

SELECTcount(y), pg_sleep(3)FROM a,bwhere x> (selectmin(x)from A,Bwhere x= y);

select*from aqo_querieswhere query_hash<>0;

DROP EXTENSION aqo;

SET statement_timeout=800;-- [0.8s]

SELECT*, pg_sleep(1)FROM t;

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');-- haven't any partial data

SET statement_timeout=3500;

SELECT*, pg_sleep(1)FROM t;

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

SET statement_timeout=10000;

SELECT*, pg_sleep(1)FROM t;

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

DELETEFROM tWHERE x>2;

ANALYZE t;

SET statement_timeout=800;

SELECT*, pg_sleep(1)FROM t;-- Not learned

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

SET statement_timeout=3500;

SELECT*, pg_sleep(1)FROM t;-- Learn!

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

SET statement_timeout=5500;

SELECT*, pg_sleep(1)FROM t;-- Get reliable data

SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');

DROPTABLE t;

DROP EXTENSION aqo;

DROPFUNCTION check_estimated_rows;

FROM aqo_cardinality_error(false) ce, aqo_query_texts aqt

WHEREce.id=aqt.query_hash

ORDER BY (md5(query_text));

DROP EXTENSION AQO;

index_rescan(scan,&key,1,NULL,0);

slot=MakeSingleTupleTableSlot(hrel->rd_att,&TTSOpsBufferHeapTuple);

values[0]=Int64GetDatum(qhash);

values[5]=Int64GetDatum(flex_timeout);

values[6]=Int64GetDatum(count_increase_timeout);

if (!index_getnext_slot(scan,ForwardScanDirection,slot))

{

result= false;

tuple=ExecFetchSlotHeapTuple(slot, true,&shouldFree);

Assert(shouldFree!= true);

values[5]=Int64GetDatum(flex_timeout);

values[6]=Int64GetDatum(count_increase_timeout);

nw_tuple=heap_modify_tuple(tuple,hrel->rd_att,values,isnull,replace);

if (my_simple_heap_update(hrel,&(nw_tuple->t_self),nw_tuple,