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

Commitc8b629c

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 parentf1826ab commitc8b629c

13 files changed

+299
-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
@@ -35,6 +35,7 @@ void _PG_init(void);
3535
intaqo_mode=AQO_MODE_CONTROLLED;
3636
boolforce_collect_stat;
3737
boolaqo_predict_with_few_neighbors;
38+
intaqo_statement_timeout;
3839

3940
/*
4041
* Show special info in EXPLAIN mode.
@@ -48,6 +49,7 @@ boolaqo_predict_with_few_neighbors;
4849
*/
4950
boolaqo_show_hash;
5051
boolaqo_show_details;
52+
boolchange_flex_timeout;
5153

5254
/* GUC variables */
5355
staticconststructconfig_enum_entryformat_options[]= {
@@ -287,6 +289,17 @@ _PG_init(void)
287289
NULL,
288290
NULL
289291
);
292+
DefineCustomIntVariable("aqo.statement_timeout",
293+
"Time limit on learning.",
294+
NULL,
295+
&aqo_statement_timeout,
296+
0,
297+
0,INT_MAX,
298+
PGC_USERSET,
299+
0,
300+
NULL,
301+
NULL,
302+
NULL);
290303

291304
DefineCustomIntVariable("aqo.min_neighbors_for_predicting",
292305
"Set how many neighbors the cardinality prediction will be calculated",

‎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
@@ -26,17 +26,15 @@
2626
*/
2727
doubleauto_tuning_convergence_error=0.01;
2828

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

35-
3634
/*
3735
* Returns mean value of the array of doubles.
3836
*/
39-
staticdouble
37+
double
4038
get_mean(double*elems,intnelems)
4139
{
4240
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/statement_timeout.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -91,7 +91,7 @@ ERROR: canceling statement due to statement timeout
9191
SELECT check_estimated_rows('SELECT *, pg_sleep(1) FROM t;');
9292
check_estimated_rows
9393
----------------------
94-
2
94+
4
9595
(1 row)
9696

9797
SET statement_timeout = 800;

‎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
@@ -243,6 +243,8 @@ aqo_planner(Query *parse,
243243
elog(ERROR,"unrecognized mode in AQO: %d",aqo_mode);
244244
break;
245245
}
246+
query_context.count_increase_timeout=0;
247+
query_context.smart_timeout=0;
246248
}
247249
else/* Query class exists in a ML knowledge base. */
248250
{

‎regress_schedule

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,9 +15,11 @@ test: plancache
1515
test: update_functions
1616
# Performance-dependent test. Can be ignored if executes in containers or on slow machines
1717
ignore: statement_timeout
18+
ignore: smart_statement_timeout
1819
test: statement_timeout
1920
test: temp_tables
2021
test: top_queries
2122
test: relocatable
2223
test: look_a_like
2324
test: feature_subspace
25+
test: smart_statement_timeout

0 commit comments

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