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

Commitfcea55c

Browse files
author
Alena Rybakina
committed
Add smart statement timeout for learning AQO.
AQO evaluates to have an enough time for training by the average integral error.If the integral error hasn't changed for comparing with error from the previous iteration,we increase the training time exponentially.Start value of smart statement timeout as equal as aqo_statement timeout or 0.If the user needs an upper limit on query execution time, he can set a vanilla statement timeoutsetting aqo_statement_timeout as 0. Smart statement timeout in this option is unenabled.The aqo.statement_timeout value is stored in aqo_queries. Initially it equals as 0 (default value).The user has the ability to specify the GUC aqo.statement_timeout andterminates the request by analogy with statement timeout.
1 parent34ee3be commitfcea55c

File tree

12 files changed

+303
-11
lines changed

12 files changed

+303
-11
lines changed

‎Makefile‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,8 @@ REGRESS =aqo_disabled \
2828
temp_tables\
2929
top_queries\
3030
relocatable\
31-
look_a_like
31+
look_a_like\
32+
smart_statement_timeout
3233

3334
fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw
3435
stat_srcdir =$(top_srcdir)/contrib/pg_stat_statements

‎aqo--1.3--1.4.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,8 @@
44
\echo Use"ALTER EXTENSION aqo UPDATE TO '1.4'" to load this file. \quit
55

66
ALTERTABLEpublic.aqo_data ADD COLUMN reliabilitydouble precision [];
7+
ALTERTABLEpublic.aqo_queries ADD COLUMN smart_timeoutbigint;
8+
ALTERTABLEpublic.aqo_queries ADD COLUMN count_increase_timeoutbigint;
79

810
DROPFUNCTIONpublic.top_error_queries(int);
911

‎aqo--1.4--1.5.sql‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,9 @@ CREATE FUNCTION aqo_queries (
2525
OUT fsbigint,
2626
OUT learn_aqoboolean,
2727
OUT use_aqoboolean,
28-
OUT auto_tuningboolean
28+
OUT auto_tuningboolean,
29+
OUT smart_timeoutbigint,
30+
OUT count_increase_timeoutbigint
2931
)
3032
RETURNS SETOF record
3133
AS'MODULE_PATHNAME','aqo_queries'
@@ -357,4 +359,4 @@ CREATE FUNCTION aqo_queries_update(queryid bigint, fs bigint, learn_aqo bool,
357359
use_aqo bool, auto_tuning bool)
358360
RETURNS bool
359361
AS'MODULE_PATHNAME','aqo_queries_update'
360-
LANGUAGE C VOLATILE;
362+
LANGUAGE C VOLATILE;

‎aqo.c‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@ void _PG_init(void);
3535
/* Strategy of determining feature space for new queries. */
3636
intaqo_mode;
3737
boolforce_collect_stat;
38+
intaqo_statement_timeout;
3839

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

5254
/* GUC variables */
5355
staticconststructconfig_enum_entryformat_options[]= {
@@ -237,6 +239,18 @@ _PG_init(void)
237239
NULL
238240
);
239241

242+
DefineCustomIntVariable("aqo.statement_timeout",
243+
"Time limit on learning.",
244+
NULL,
245+
&aqo_statement_timeout,
246+
0,
247+
0,INT_MAX,
248+
PGC_USERSET,
249+
0,
250+
NULL,
251+
NULL,
252+
NULL);
253+
240254
prev_shmem_startup_hook=shmem_startup_hook;
241255
shmem_startup_hook=aqo_init_shmem;
242256
prev_planner_hook=planner_hook;

‎aqo.h‎

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

198198
instr_timestart_execution_time;
199199
doubleplanning_time;
200+
int64smart_timeout;
201+
int64count_increase_timeout;
200202
}QueryContextData;
201203

204+
/*
205+
* Indicator for using smart statement timeout for query
206+
*/
207+
externboolchange_flex_timeout;
208+
202209
structStatEntry;
203210

204211
externdoublepredicted_ppi_rows;
@@ -244,6 +251,7 @@ extern ExplainOnePlan_hook_type prev_ExplainOnePlan_hook;
244251
externExplainOneNode_hook_typeprev_ExplainOneNode_hook;
245252

246253
externvoidppi_hook(ParamPathInfo*ppi);
254+
externintaqo_statement_timeout;
247255

248256
/* Hash functions */
249257
voidget_eclasses(List*clauselist,int*nargs,int**args_hash,
@@ -296,5 +304,8 @@ extern void selectivity_cache_clear(void);
296304
externOidget_aqo_schema(void);
297305
externboolIsQueryDisabled(void);
298306

307+
externboolupdate_query_timeout(uint64queryid,int64smart_timeout);
308+
externdoubleget_mean(double*elems,intnelems);
309+
299310
externList*cur_classes;
300311
#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;
Lines changed: 122 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,122 @@
1+
DROP TABLE IF EXISTS a,b CASCADE;
2+
CREATE TABLE a (x1 int, x2 int, x3 int);
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+
CREATE TABLE b (y1 int, y2 int, y3 int);
5+
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;
6+
CREATE EXTENSION IF NOT EXISTS aqo;
7+
SET aqo.join_threshold = 0;
8+
SET aqo.mode = 'learn';
9+
SET aqo.show_details = 'off';
10+
SET aqo.learn_statement_timeout = 'on';
11+
SET statement_timeout = 2500; -- [2.5s]
12+
SET aqo.statement_timeout = 5000;
13+
SELECT pg_sleep(2),count(x1),count(y1) FROM A,B WHERE x1 = 5 AND x2 = 5 AND A.x1 = B.y1;
14+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 0
15+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 1
16+
pg_sleep | count | count
17+
----------+-------+-------
18+
| 0 | 0
19+
(1 row)
20+
21+
select queryid, smart_timeout, count_increase_timeout from aqo_queries where queryid <> 0 and count_increase_timeout > 0 limit 1;
22+
queryid | smart_timeout | count_increase_timeout
23+
---------------------+---------------+------------------------
24+
1781875614098811285 | 1 | 1
25+
(1 row)
26+
27+
INSERT INTO a (x1, x2, x3) SELECT mod(ival,20), mod(ival,10), mod(ival,10) FROM generate_series(1,1000) As ival;
28+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 0
29+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 1
30+
SELECT pg_sleep(2),count(x1),count(y1) FROM A,B WHERE x1 = 5 AND x2 = 5 AND A.x1 = B.y1;
31+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 1
32+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 6
33+
pg_sleep | count | count
34+
----------+-------+-------
35+
| 0 | 0
36+
(1 row)
37+
38+
select queryid, smart_timeout, count_increase_timeout from aqo_queries where queryid <> 0 and count_increase_timeout > 1 limit 1;
39+
queryid | smart_timeout | count_increase_timeout
40+
---------------------+---------------+------------------------
41+
1781875614098811285 | 6 | 2
42+
(1 row)
43+
44+
SELECT pg_sleep(2),count(x1),count(y1) FROM A,B WHERE x1 = 5 AND x2 = 5 AND A.x1 = B.y1;
45+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 6
46+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 63
47+
pg_sleep | count | count
48+
----------+-------+-------
49+
| 0 | 0
50+
(1 row)
51+
52+
select queryid, smart_timeout, count_increase_timeout from aqo_queries where queryid <> 0 and count_increase_timeout > 2 limit 1;
53+
queryid | smart_timeout | count_increase_timeout
54+
---------------------+---------------+------------------------
55+
1781875614098811285 | 63 | 3
56+
(1 row)
57+
58+
SET aqo.statement_timeout = 1500; -- [1.5s]
59+
SELECT pg_sleep(1),count(x1),count(y1) FROM A,B WHERE x1 > 5 AND x2 > 5 AND x3 < 10 AND A.x1 = B.y1;
60+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 0
61+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 1
62+
pg_sleep | count | count
63+
----------+-------+-------
64+
| 0 | 0
65+
(1 row)
66+
67+
INSERT INTO b (y1, y2, y3) SELECT mod(ival,20), mod(ival,10), mod(ival,10) FROM generate_series(1,1000) As ival;
68+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 0
69+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 1
70+
SELECT pg_sleep(1),count(x1),count(y1) FROM A,B WHERE x1 > 5 AND x2 > 5 AND x3 < 10 AND A.x1 = B.y1;
71+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 1
72+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 6
73+
pg_sleep | count | count
74+
----------+-------+-------
75+
| 20000 | 20000
76+
(1 row)
77+
78+
select queryid, smart_timeout, count_increase_timeout from aqo_queries where queryid <> 0 and count_increase_timeout > 1 limit 2;
79+
queryid | smart_timeout | count_increase_timeout
80+
---------------------+---------------+------------------------
81+
3518970893927378223 | 6 | 2
82+
1781875614098811285 | 63 | 3
83+
(2 rows)
84+
85+
SELECT pg_sleep(1),count(x1),count(y1) FROM A,B WHERE x1 > 5 AND x2 > 5 AND x3 < 10 AND A.x1 = B.y1;
86+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 6
87+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 63
88+
pg_sleep | count | count
89+
----------+-------+-------
90+
| 20000 | 20000
91+
(1 row)
92+
93+
select queryid, smart_timeout, count_increase_timeout from aqo_queries where queryid <> 0 and count_increase_timeout > 2 limit 2;
94+
queryid | smart_timeout | count_increase_timeout
95+
---------------------+---------------+------------------------
96+
3518970893927378223 | 63 | 3
97+
1781875614098811285 | 63 | 3
98+
(2 rows)
99+
100+
SET aqo.statement_timeout = 15;
101+
SELECT pg_sleep(1),count(x1),count(y1) FROM A,B WHERE x1 > 5 AND x2 > 5 AND x3 < 10 AND A.x1 = B.y1;
102+
NOTICE: [AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data. Timeout is 15
103+
NOTICE: [AQO] Time limit for execution of the statement was increased. Current timeout is 1728
104+
pg_sleep | count | count
105+
----------+-------+-------
106+
| 20000 | 20000
107+
(1 row)
108+
109+
select queryid, smart_timeout, count_increase_timeout from aqo_queries where queryid <> 0 and count_increase_timeout > 2 limit 2;
110+
queryid | smart_timeout | count_increase_timeout
111+
---------------------+---------------+------------------------
112+
3518970893927378223 | 63 | 3
113+
1781875614098811285 | 63 | 3
114+
(2 rows)
115+
116+
SELECT 1 FROM aqo_reset();
117+
?column?
118+
----------
119+
1
120+
(1 row)
121+
122+
DROP EXTENSION aqo;

‎postprocessing.c‎

Lines changed: 54 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,8 @@ typedef struct
4343

4444
staticdoublecardinality_sum_errors;
4545
staticintcardinality_num_objects;
46+
staticint64max_timeout_value;
47+
staticint64growth_rate=2;
4648

4749
/*
4850
* Store an AQO-related query data into the Query Environment structure.
@@ -626,14 +628,54 @@ aqo_timeout_handler(void)
626628
ctx.learn=query_context.learn_aqo;
627629
ctx.isTimedOut= true;
628630

629-
elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.");
631+
if (aqo_statement_timeout==0)
632+
elog(NOTICE,"[AQO] Time limit for execution of the statement was expired. AQO tried to learn on partial data.");
633+
else
634+
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);
635+
630636
learnOnPlanState(timeoutCtl.queryDesc->planstate, (void*)&ctx);
631637
}
632638

639+
/*
640+
* Function to get the value of a variable with exponential growth
641+
*/
642+
staticint64
643+
get_increment()
644+
{
645+
returnpow(1+growth_rate,query_context.count_increase_timeout);
646+
}
647+
648+
/*
649+
* Function for updating smart statement timeout
650+
*/
651+
staticint64
652+
increase_smart_timeout()
653+
{
654+
int64smart_timeout_fin_time= (query_context.smart_timeout+1)*get_increment(query_context.count_increase_timeout);
655+
656+
if (query_context.smart_timeout==max_timeout_value&& !update_query_timeout(query_context.query_hash,smart_timeout_fin_time))
657+
elog(NOTICE,"[AQO] Timeout is not updated!");
658+
659+
returnsmart_timeout_fin_time;
660+
}
661+
633662
staticbool
634663
set_timeout_if_need(QueryDesc*queryDesc)
635664
{
636-
TimestampTzfin_time;
665+
int64fintime= (int64)get_timeout_finish_time(STATEMENT_TIMEOUT)-1;
666+
667+
if (aqo_learn_statement_timeout&&aqo_statement_timeout>0)
668+
{
669+
max_timeout_value=Min(query_context.smart_timeout, (int64)aqo_statement_timeout);
670+
if (max_timeout_value>fintime)
671+
{
672+
max_timeout_value=fintime;
673+
}
674+
}
675+
else
676+
{
677+
max_timeout_value=fintime;
678+
}
637679

638680
if (!get_timeout_active(STATEMENT_TIMEOUT)|| !aqo_learn_statement_timeout)
639681
return false;
@@ -656,8 +698,7 @@ set_timeout_if_need(QueryDesc *queryDesc)
656698
else
657699
Assert(!get_timeout_active(timeoutCtl.id));
658700

659-
fin_time=get_timeout_finish_time(STATEMENT_TIMEOUT);
660-
enable_timeout_at(timeoutCtl.id,fin_time-1);
701+
enable_timeout_at(timeoutCtl.id, (TimestampTz)max_timeout_value);
661702

662703
/* Save pointer to queryDesc to use at learning after a timeout interruption. */
663704
timeoutCtl.queryDesc=queryDesc;
@@ -712,6 +753,7 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
712753
StatEntry*stat;
713754
instr_timeendtime;
714755
EphemeralNamedRelationenr=get_ENR(queryDesc->queryEnv,PlanStateInfo);
756+
doubleerror=.0;
715757

716758
cardinality_sum_errors=0.;
717759
cardinality_num_objects=0;
@@ -779,6 +821,14 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
779821
if (!query_context.adding_query&&query_context.auto_tuning)
780822
automatical_query_tuning(query_context.query_hash,stat);
781823

824+
error=stat->est_error_aqo[stat->cur_stat_slot_aqo-1]-cardinality_sum_errors/(1+cardinality_num_objects);
825+
826+
if (aqo_learn_statement_timeout&&aqo_statement_timeout>0&&error >=0.1)
827+
{
828+
int64fintime=increase_smart_timeout();
829+
elog(NOTICE,"[AQO] Time limit for execution of the statement was increased. Current timeout is %ld",fintime);
830+
}
831+
782832
pfree(stat);
783833
}
784834
}

‎preprocessing.c‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -237,6 +237,8 @@ aqo_planner(Query *parse,
237237
elog(ERROR,"unrecognized mode in AQO: %d",aqo_mode);
238238
break;
239239
}
240+
query_context.count_increase_timeout=0;
241+
query_context.smart_timeout=0;
240242
}
241243
else/* Query class exists in a ML knowledge base. */
242244
{

‎sql/smart_statement_timeout.sql‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
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=2500;-- [2.5s]
14+
SETaqo.statement_timeout=5000;
15+
16+
SELECT pg_sleep(2),count(x1),count(y1)FROM A,BWHERE x1=5AND x2=5ANDA.x1=B.y1;
17+
select queryid, smart_timeout, count_increase_timeoutfrom aqo_querieswhere queryid<>0and count_increase_timeout>0limit1;
18+
INSERT INTO a (x1, x2, x3)SELECT mod(ival,20), mod(ival,10), mod(ival,10)FROM generate_series(1,1000)As ival;
19+
SELECT pg_sleep(2),count(x1),count(y1)FROM A,BWHERE x1=5AND x2=5ANDA.x1=B.y1;
20+
select queryid, smart_timeout, count_increase_timeoutfrom aqo_querieswhere queryid<>0and count_increase_timeout>1limit1;
21+
SELECT pg_sleep(2),count(x1),count(y1)FROM A,BWHERE x1=5AND x2=5ANDA.x1=B.y1;
22+
select queryid, smart_timeout, count_increase_timeoutfrom aqo_querieswhere queryid<>0and count_increase_timeout>2limit1;
23+
24+
SETaqo.statement_timeout=1500;-- [1.5s]
25+
SELECT pg_sleep(1),count(x1),count(y1)FROM A,BWHERE x1>5AND x2>5AND x3<10ANDA.x1=B.y1;
26+
INSERT INTO b (y1, y2, y3)SELECT mod(ival,20), mod(ival,10), mod(ival,10)FROM generate_series(1,1000)As ival;
27+
SELECT pg_sleep(1),count(x1),count(y1)FROM A,BWHERE x1>5AND x2>5AND x3<10ANDA.x1=B.y1;
28+
select queryid, smart_timeout, count_increase_timeoutfrom aqo_querieswhere queryid<>0and count_increase_timeout>1limit2;
29+
SELECT pg_sleep(1),count(x1),count(y1)FROM A,BWHERE x1>5AND x2>5AND x3<10ANDA.x1=B.y1;
30+
select queryid, smart_timeout, count_increase_timeoutfrom aqo_querieswhere queryid<>0and count_increase_timeout>2limit2;
31+
32+
SETaqo.statement_timeout=15;
33+
SELECT pg_sleep(1),count(x1),count(y1)FROM A,BWHERE x1>5AND x2>5AND x3<10ANDA.x1=B.y1;
34+
select queryid, smart_timeout, count_increase_timeoutfrom aqo_querieswhere queryid<>0and count_increase_timeout>2limit2;
35+
36+
SELECT1FROM aqo_reset();
37+
DROP EXTENSION aqo;

0 commit comments

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