Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

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

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit93a2291

Browse files
Alena Rybakinadanolivo
Alena Rybakina
authored andcommitted
Add smart statement timeout for learning aqo in special quesries within through manual retraining.
AQO evaluates whether enough to execute the query through comparison integral error value with its fixed value (0.1),also if integral error didn't change compared to previous iterations, smart statemet timeout value will be increased.Besides, smart statemet timeout value won't be increased, if there is reached limit value, namely statement timeout.The initial smart_statement_timeout value is aqo statement timeout value or 0.Smart statement timeout value and number of its using are saved in aqo_queries.
1 parentf9aedf3 commit93a2291

12 files changed

+298
-13
lines changed

‎aqo--1.5--1.6.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,9 +3,12 @@
33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use"ALTER EXTENSION aqo UPDATE TO '1.6'" to load this file. \quit
55

6+
DROPVIEW aqo_queries;
7+
68
DROPFUNCTION aqo_enable_query;
79
DROPFUNCTION aqo_disable_query;
810
DROPFUNCTION aqo_cleanup;
11+
DROPFUNCTION aqo_queries;
912

1013
CREATEFUNCTIONaqo_enable_class(queryidbigint)
1114
RETURNS void
@@ -77,3 +80,21 @@ CREATE FUNCTION aqo_data_update(
7780
RETURNS bool
7881
AS'MODULE_PATHNAME','aqo_data_update'
7982
LANGUAGE C VOLATILE;
83+
84+
/*
85+
* VIEWs to discover AQO data.
86+
*/
87+
CREATEFUNCTIONaqo_queries (
88+
OUT queryidbigint,
89+
OUT fsbigint,
90+
OUT learn_aqoboolean,
91+
OUT use_aqoboolean,
92+
OUT auto_tuningboolean,
93+
OUT smart_timeoutbigint,
94+
OUT count_increase_timeoutbigint
95+
)
96+
RETURNS SETOF record
97+
AS'MODULE_PATHNAME','aqo_queries'
98+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
99+
100+
CREATEVIEWaqo_queriesASSELECT*FROM aqo_queries();

‎aqo.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@ void _PG_init(void);
3939
intaqo_mode=AQO_MODE_CONTROLLED;
4040
boolforce_collect_stat;
4141
boolaqo_predict_with_few_neighbors;
42+
intaqo_statement_timeout;
4243

4344
/*
4445
* Show special info in EXPLAIN mode.
@@ -52,6 +53,7 @@ boolaqo_predict_with_few_neighbors;
5253
*/
5354
boolaqo_show_hash;
5455
boolaqo_show_details;
56+
boolchange_flex_timeout;
5557

5658
/* GUC variables */
5759
staticconststructconfig_enum_entryformat_options[]= {
@@ -403,6 +405,17 @@ _PG_init(void)
403405
NULL,
404406
NULL
405407
);
408+
DefineCustomIntVariable("aqo.statement_timeout",
409+
"Time limit on learning.",
410+
NULL,
411+
&aqo_statement_timeout,
412+
0,
413+
0,INT_MAX,
414+
PGC_USERSET,
415+
0,
416+
NULL,
417+
NULL,
418+
NULL);
406419

407420
DefineCustomBoolVariable("aqo.cleanup_bgworker",
408421
"Enable bgworker which responsible for doing cleanup after drop",

‎aqo.h

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,8 +199,15 @@ typedef struct QueryContextData
199199

200200
instr_timestart_execution_time;
201201
doubleplanning_time;
202+
int64smart_timeout;
203+
int64count_increase_timeout;
202204
}QueryContextData;
203205

206+
/*
207+
* Indicator for using smart statement timeout for query
208+
*/
209+
externboolchange_flex_timeout;
210+
204211
structStatEntry;
205212

206213
externdoublepredicted_ppi_rows;
@@ -250,6 +257,7 @@ extern ExplainOnePlan_hook_type prev_ExplainOnePlan_hook;
250257
externExplainOneNode_hook_typeprev_ExplainOneNode_hook;
251258

252259
externvoidppi_hook(ParamPathInfo*ppi);
260+
externintaqo_statement_timeout;
253261

254262
/* Hash functions */
255263
voidget_eclasses(List*clauselist,int*nargs,int**args_hash,
@@ -298,5 +306,8 @@ extern void selectivity_cache_clear(void);
298306

299307
externboolIsQueryDisabled(void);
300308

309+
externboolupdate_query_timeout(uint64queryid,int64smart_timeout);
310+
externdoubleget_mean(double*elems,intnelems);
311+
301312
externList*cur_classes;
302313
#endif

‎auto_tuning.c

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -27,17 +27,15 @@
2727
*/
2828
doubleauto_tuning_convergence_error=0.01;
2929

30-
staticdoubleget_mean(double*elems,intnelems);
3130
staticdoubleget_estimation(double*elems,intnelems);
3231
staticboolis_stable(double*elems,intnelems);
3332
staticboolconverged_cq(double*elems,intnelems);
3433
staticboolis_in_infinite_loop_cq(double*elems,intnelems);
3534

36-
3735
/*
3836
* Returns mean value of the array of doubles.
3937
*/
40-
staticdouble
38+
double
4139
get_mean(double*elems,intnelems)
4240
{
4341
doublesum=0;

‎expected/smart_statement_timeout.out

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
DROP TABLE IF EXISTS a,b CASCADE;
2+
NOTICE: table "a" does not exist, skipping
3+
NOTICE: table "b" does not exist, skipping
4+
CREATE TABLE a (x1 int, x2 int, x3 int);
5+
INSERT INTO a (x1, x2, x3) SELECT mod(ival,4), mod(ival,10), mod(ival,10) FROM generate_series(1,100) As ival;
6+
CREATE TABLE b (y1 int, y2 int, y3 int);
7+
INSERT INTO b (y1, y2, y3) SELECT mod(ival + 1,4), mod(ival + 1,10), mod(ival + 1,10) FROM generate_series(1,100) As ival;
8+
CREATE EXTENSION IF NOT EXISTS aqo;
9+
SET aqo.join_threshold = 0;
10+
SET aqo.mode = 'learn';
11+
SET aqo.show_details = 'off';
12+
SET aqo.learn_statement_timeout = 'on';
13+
SET statement_timeout = 1500; -- [1.5s]
14+
SET aqo.statement_timeout = 500; -- [0.5s]
15+
SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;
16+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 0
17+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 1
18+
count | count
19+
-------+-------
20+
62500 | 62500
21+
(1 row)
22+
23+
select smart_timeout, count_increase_timeout from aqo_queries, aqo_query_texts
24+
where query_text = 'SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
25+
and aqo_query_texts.queryid = aqo_queries.queryid limit 1;
26+
smart_timeout | count_increase_timeout
27+
---------------+------------------------
28+
1 | 1
29+
(1 row)
30+
31+
SET aqo.learn_statement_timeout = 'off';
32+
SET aqo.statement_timeout = 1000; -- [1s]
33+
INSERT INTO a (x1, x2, x3) SELECT mod(ival,20), mod(ival,10), mod(ival,10) FROM generate_series(1,1000) As ival;
34+
SET aqo.learn_statement_timeout = 'on';
35+
SET aqo.statement_timeout = 500; -- [0.5s]
36+
SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;
37+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 1
38+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 6
39+
count | count
40+
--------+--------
41+
563300 | 562500
42+
(1 row)
43+
44+
select smart_timeout, count_increase_timeout from aqo_queries, aqo_query_texts
45+
where query_text = 'SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
46+
and aqo_query_texts.queryid = aqo_queries.queryid limit 1;
47+
smart_timeout | count_increase_timeout
48+
---------------+------------------------
49+
6 | 2
50+
(1 row)
51+
52+
SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;
53+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 6
54+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 63
55+
count | count
56+
--------+--------
57+
563300 | 562500
58+
(1 row)
59+
60+
select smart_timeout, count_increase_timeout from aqo_queries, aqo_query_texts
61+
where query_text = 'SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
62+
and aqo_query_texts.queryid = aqo_queries.queryid limit 1;
63+
smart_timeout | count_increase_timeout
64+
---------------+------------------------
65+
63 | 3
66+
(1 row)
67+
68+
SET statement_timeout = 100; -- [0.1s]
69+
SET aqo.statement_timeout = 150;
70+
SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;
71+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 63
72+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 1728
73+
count | count
74+
--------+--------
75+
563300 | 562500
76+
(1 row)
77+
78+
select smart_timeout, count_increase_timeout from aqo_queries, aqo_query_texts
79+
where query_text = 'SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
80+
and aqo_query_texts.queryid = aqo_queries.queryid limit 1;
81+
smart_timeout | count_increase_timeout
82+
---------------+------------------------
83+
1728 | 4
84+
(1 row)
85+
86+
SELECT 1 FROM aqo_reset();
87+
?column?
88+
----------
89+
1
90+
(1 row)
91+
92+
DROP TABLE a;
93+
DROP TABLE b;
94+
DROP EXTENSION aqo;

‎expected/update_functions.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -211,8 +211,8 @@ ORDER BY res;
211211
(TABLE aqo_queries_dump EXCEPT TABLE aqo_queries)
212212
UNION ALL
213213
(TABLE aqo_queries EXCEPT TABLE aqo_queries_dump);
214-
queryid | fs | learn_aqo | use_aqo | auto_tuning
215-
---------+----+-----------+---------+-------------
214+
queryid | fs | learn_aqo | use_aqo | auto_tuning| smart_timeout | count_increase_timeout
215+
---------+----+-----------+---------+-------------+---------------+------------------------
216216
(0 rows)
217217

218218
-- Update aqo_queries with dump data.
@@ -234,8 +234,8 @@ ORDER BY res;
234234
(TABLE aqo_queries_dump EXCEPT TABLE aqo_queries)
235235
UNION ALL
236236
(TABLE aqo_queries EXCEPT TABLE aqo_queries_dump);
237-
queryid | fs | learn_aqo | use_aqo | auto_tuning
238-
---------+----+-----------+---------+-------------
237+
queryid | fs | learn_aqo | use_aqo | auto_tuning| smart_timeout | count_increase_timeout
238+
---------+----+-----------+---------+-------------+---------------+------------------------
239239
(0 rows)
240240

241241
--

‎postprocessing.c

Lines changed: 47 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,8 @@ typedef struct
4444

4545
staticdoublecardinality_sum_errors;
4646
staticintcardinality_num_objects;
47+
staticint64max_timeout_value;
48+
staticint64growth_rate=3;
4749

4850
/*
4951
* Store an AQO-related query data into the Query Environment structure.
@@ -625,15 +627,46 @@ aqo_timeout_handler(void)
625627
ctx.learn=query_context.learn_aqo;
626628
ctx.isTimedOut= true;
627629

628-
elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.");
630+
if (aqo_statement_timeout==0)
631+
elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.");
632+
else
633+
elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is %ld",max_timeout_value);
634+
629635
learnOnPlanState(timeoutCtl.queryDesc->planstate, (void*)&ctx);
630636
MemoryContextSwitchTo(oldctx);
631637
}
632638

639+
/*
640+
* Function for updating smart statement timeout
641+
*/
642+
staticint64
643+
increase_smart_timeout()
644+
{
645+
int64smart_timeout_fin_time= (query_context.smart_timeout+1)*pow(growth_rate,query_context.count_increase_timeout);
646+
647+
if (query_context.smart_timeout==max_timeout_value&& !update_query_timeout(query_context.query_hash,smart_timeout_fin_time))
648+
elog(NOTICE,"[AQO] Timeout is not updated!");
649+
650+
returnsmart_timeout_fin_time;
651+
}
652+
633653
staticbool
634654
set_timeout_if_need(QueryDesc*queryDesc)
635655
{
636-
TimestampTzfin_time;
656+
int64fintime= (int64)get_timeout_finish_time(STATEMENT_TIMEOUT)-1;
657+
658+
if (aqo_learn_statement_timeout&&aqo_statement_timeout>0)
659+
{
660+
max_timeout_value=Min(query_context.smart_timeout, (int64)aqo_statement_timeout);
661+
if (max_timeout_value>fintime)
662+
{
663+
max_timeout_value=fintime;
664+
}
665+
}
666+
else
667+
{
668+
max_timeout_value=fintime;
669+
}
637670

638671
if (IsParallelWorker())
639672
/*
@@ -663,8 +696,7 @@ set_timeout_if_need(QueryDesc *queryDesc)
663696
else
664697
Assert(!get_timeout_active(timeoutCtl.id));
665698

666-
fin_time=get_timeout_finish_time(STATEMENT_TIMEOUT);
667-
enable_timeout_at(timeoutCtl.id,fin_time-1);
699+
enable_timeout_at(timeoutCtl.id, (TimestampTz)max_timeout_value);
668700

669701
/* Save pointer to queryDesc to use at learning after a timeout interruption. */
670702
timeoutCtl.queryDesc=queryDesc;
@@ -720,6 +752,7 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
720752
instr_timeendtime;
721753
EphemeralNamedRelationenr=get_ENR(queryDesc->queryEnv,PlanStateInfo);
722754
MemoryContextoldctx=MemoryContextSwitchTo(AQOLearnMemCtx);
755+
doubleerror=.0;
723756

724757
cardinality_sum_errors=0.;
725758
cardinality_num_objects=0;
@@ -788,6 +821,16 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
788821
/* Store all learn data into the AQO service relations. */
789822
if (!query_context.adding_query&&query_context.auto_tuning)
790823
automatical_query_tuning(query_context.query_hash,stat);
824+
825+
error=stat->est_error_aqo[stat->cur_stat_slot_aqo-1]-cardinality_sum_errors/(1+cardinality_num_objects);
826+
827+
if (aqo_learn_statement_timeout&&aqo_statement_timeout>0&&error >=0.1)
828+
{
829+
int64fintime=increase_smart_timeout();
830+
elog(NOTICE,"[AQO] Time limit for execution of the statement was increased. Current timeout is %ld",fintime);
831+
}
832+
833+
pfree(stat);
791834
}
792835
}
793836

‎preprocessing.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -249,6 +249,8 @@ aqo_planner(Query *parse,
249249
elog(ERROR,"unrecognized mode in AQO: %d",aqo_mode);
250250
break;
251251
}
252+
query_context.count_increase_timeout=0;
253+
query_context.smart_timeout=0;
252254
}
253255
else/* Query class exists in a ML knowledge base. */
254256
{

‎regress_schedule

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,9 +14,10 @@ test: parallel_workers
1414
test: plancache
1515
test: update_functions
1616
test: statement_timeout
17+
test: smart_statement_timeout
1718
test: temp_tables
1819
test: top_queries
1920
test: relocatable
2021
test: look_a_like
2122
test: feature_subspace
22-
test: cleanup_bgworker
23+
test: cleanup_bgworker

‎sql/smart_statement_timeout.sql

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
DROPTABLE IF EXISTS a,b CASCADE;
2+
CREATETABLEa (x1int, x2int, x3int);
3+
INSERT INTO a (x1, x2, x3)SELECT mod(ival,4), mod(ival,10), mod(ival,10)FROM generate_series(1,100)As ival;
4+
5+
CREATETABLEb (y1int, y2int, y3int);
6+
INSERT INTO b (y1, y2, y3)SELECT mod(ival+1,4), mod(ival+1,10), mod(ival+1,10)FROM generate_series(1,100)As ival;
7+
8+
CREATE EXTENSION IF NOT EXISTS aqo;
9+
SETaqo.join_threshold=0;
10+
SETaqo.mode='learn';
11+
SETaqo.show_details='off';
12+
SETaqo.learn_statement_timeout='on';
13+
SET statement_timeout=1500;-- [1.5s]
14+
SETaqo.statement_timeout=500;-- [0.5s]
15+
16+
SELECTcount(a.x1),count(B.y1)FROM A aLEFT JOIN BONa.x1=B.y1LEFT JOIN A a1ONa1.x1=B.y1;
17+
select smart_timeout, count_increase_timeoutfrom aqo_queries, aqo_query_texts
18+
where query_text='SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
19+
andaqo_query_texts.queryid=aqo_queries.queryidlimit1;
20+
21+
SETaqo.learn_statement_timeout='off';
22+
SETaqo.statement_timeout=1000;-- [1s]
23+
INSERT INTO a (x1, x2, x3)SELECT mod(ival,20), mod(ival,10), mod(ival,10)FROM generate_series(1,1000)As ival;
24+
SETaqo.learn_statement_timeout='on';
25+
SETaqo.statement_timeout=500;-- [0.5s]
26+
SELECTcount(a.x1),count(B.y1)FROM A aLEFT JOIN BONa.x1=B.y1LEFT JOIN A a1ONa1.x1=B.y1;
27+
select smart_timeout, count_increase_timeoutfrom aqo_queries, aqo_query_texts
28+
where query_text='SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
29+
andaqo_query_texts.queryid=aqo_queries.queryidlimit1;
30+
SELECTcount(a.x1),count(B.y1)FROM A aLEFT JOIN BONa.x1=B.y1LEFT JOIN A a1ONa1.x1=B.y1;
31+
select smart_timeout, count_increase_timeoutfrom aqo_queries, aqo_query_texts
32+
where query_text='SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
33+
andaqo_query_texts.queryid=aqo_queries.queryidlimit1;
34+
35+
SET statement_timeout=100;-- [0.1s]
36+
SETaqo.statement_timeout=150;
37+
SELECTcount(a.x1),count(B.y1)FROM A aLEFT JOIN BONa.x1=B.y1LEFT JOIN A a1ONa1.x1=B.y1;
38+
select smart_timeout, count_increase_timeoutfrom aqo_queries, aqo_query_texts
39+
where query_text='SELECT count(a.x1),count(B.y1) FROM A a LEFT JOIN B ON a.x1 = B.y1 LEFT JOIN A a1 ON a1.x1 = B.y1;'
40+
andaqo_query_texts.queryid=aqo_queries.queryidlimit1;
41+
42+
SELECT1FROM aqo_reset();
43+
DROPTABLE a;
44+
DROPTABLE b;
45+
DROP EXTENSION aqo;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp