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

Commita96e018

Browse files
committed
Cumulative commit to improve the AQO extension for PostgreSQL v.12.
1. Bugfixes: - duplicate index on aqo_data; - different hashes on query and EXPLAIN ANALYZE <query>2. Rename "FIXED" mode to "FROZEN". In the FROZEN mode don't write any datainto the knowledge base (don't increment xid at read-only queries) except ofthe force_collect_stat GUC will be enabled.3. Add service function aqo_status(hash) to check state of the query.4. Add GUC "force_collect_stat" to collect statistics on query execution timeand cardinality error in any mode, also when AQO is disabled but not dropped.5. Update license file6. Add update script to version 1.2
1 parent9ee5338 commita96e018

File tree

8 files changed

+147
-31
lines changed

8 files changed

+147
-31
lines changed

‎Makefile‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
# contrib/aqo/Makefile
22

33
EXTENSION = aqo
4-
EXTVERSION = 1.1
4+
EXTVERSION = 1.2
55
PGFILEDESC = "AQO - adaptive query optimization"
66
MODULES = aqo
77
OBJS = aqo.o auto_tuning.o cardinality_estimation.o cardinality_hooks.o\
@@ -17,8 +17,8 @@ REGRESS =aqo_disabled \
1717

1818
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
1919

20-
DATA = aqo--1.0.sql aqo--1.0--1.1.sql
21-
DATA_built = aqo--1.1.sql
20+
DATA = aqo--1.0.sql aqo--1.0--1.1.sql aqo--1.1--1.2.sql
21+
DATA_built = aqo--1.2.sql
2222

2323
MODULE_big = aqo
2424
ifdefUSE_PGXS

‎aqo--1.1--1.2.sql‎

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
CREATE OR REPLACEFUNCTIONaqo_migrate_to_1_2_get_pk(relid regclass) RETURNStextAS $$
2+
DECLARE
3+
restext;
4+
BEGIN
5+
SELECT conname
6+
FROM pg_constraint
7+
WHERE conrelid= relidAND contype='u'
8+
INTO res;
9+
10+
RETURN res;
11+
END
12+
$$ LANGUAGE plpgsql;
13+
14+
DO $$
15+
BEGIN
16+
EXECUTE format('ALTER TABLE public.aqo_data DROP CONSTRAINT %s',
17+
aqo_migrate_to_1_2_get_pk('aqo_data'::regclass),
18+
'aqo_queries_query_hash_idx');
19+
END
20+
$$;
21+
22+
23+
DROPFUNCTION aqo_migrate_to_1_2_get_pk(regclass);
24+
25+
--
26+
-- Service functions
27+
--
28+
CREATEFUNCTIONpublic.aqo_status(hashint)
29+
RETURNS TABLE (
30+
"learn"BOOL,
31+
"use aqo"BOOL,
32+
"auto tune"BOOL,
33+
"fspace hash"INT,
34+
"aqo error"TEXT,
35+
"base error"TEXT
36+
)
37+
AS $func$
38+
SELECTlearn_aqo,use_aqo,auto_tuning,fspace_hash,
39+
to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),
40+
to_char(cardinality_error_without_aqo[n2],'9.99EEEE')
41+
FROM aqo_queries aq, aqo_query_stat aqs,
42+
(SELECT array_length(n1,1)AS n1, array_length(n2,1)AS n2FROM
43+
(SELECT cardinality_error_with_aqoAS n1, cardinality_error_without_aqoAS n2
44+
FROM aqo_query_stat aqsWHERE
45+
aqs.query_hash= $1)AS al)AS q
46+
WHERE (aqs.query_hash=aq.query_hash)AND
47+
aqs.query_hash= $1;
48+
$func$ LANGUAGE SQL;
49+

‎aqo.c‎

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,15 +6,16 @@ void _PG_init(void);
66

77

88
/* Strategy of determining feature space for new queries. */
9-
intaqo_mode;
9+
intaqo_mode;
10+
boolforce_collect_stat;
1011

1112
/* GUC variables */
1213
staticconststructconfig_enum_entryformat_options[]= {
1314
{"intelligent",AQO_MODE_INTELLIGENT, false},
1415
{"forced",AQO_MODE_FORCED, false},
1516
{"controlled",AQO_MODE_CONTROLLED, false},
1617
{"learn",AQO_MODE_LEARN, false},
17-
{"fixed",AQO_MODE_FIXED, false},
18+
{"frozen",AQO_MODE_FROZEN, false},
1819
{"disabled",AQO_MODE_DISABLED, false},
1920
{NULL,0, false}
2021
};
@@ -93,6 +94,19 @@ _PG_init(void)
9394
NULL,
9495
NULL);
9596

97+
DefineCustomBoolVariable(
98+
"aqo.force_collect_stat",
99+
"Collect statistics at all AQO modes",
100+
NULL,
101+
&force_collect_stat,
102+
false,
103+
PGC_USERSET,
104+
0,
105+
NULL,
106+
NULL,
107+
NULL
108+
);
109+
96110
prev_planner_hook=planner_hook;
97111
planner_hook=aqo_planner;
98112
prev_post_parse_analyze_hook=post_parse_analyze_hook;

‎aqo.control‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# AQO extension
22
comment = 'machine learning for cardinality estimation in optimizer'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/aqo'
55
relocatable = false

‎aqo.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -166,11 +166,13 @@ typedef enum
166166
/* Creates new feature space for each query type without auto-tuning */
167167
AQO_MODE_LEARN,
168168
/* Use only current AQO estimations, without learning or tuning */
169-
AQO_MODE_FIXED,
169+
AQO_MODE_FROZEN,
170170
/* Aqo is disabled for all queries */
171171
AQO_MODE_DISABLED,
172172
}AQO_MODE;
173+
173174
externintaqo_mode;
175+
externboolforce_collect_stat;
174176

175177
/*
176178
* It is mostly needed for auto tuning of query. with auto tuning mode aqo

‎hash.c‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -334,7 +334,11 @@ get_relidslist_hash(List *relidslist)
334334
char*
335335
remove_consts(constchar*str)
336336
{
337-
returnreplace_patterns(str,"{CONST",is_brace);
337+
char*res;
338+
339+
res=replace_patterns(str,"{CONST",is_brace);
340+
res=replace_patterns(res,":stmt_len",is_brace);
341+
returnres;
338342
}
339343

340344
/*

‎postprocessing.c‎

Lines changed: 16 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@ typedef struct
1818
List*clauselist;
1919
List*selectivities;
2020
List*relidslist;
21+
boollearn;
2122
}aqo_obj_stat;
2223

2324
staticdoublecardinality_sum_errors;
@@ -90,9 +91,6 @@ learn_sample(List *clauselist, List *selectivities, List *relidslist,
9091
doubletarget;
9192
inti;
9293

93-
cardinality_sum_errors+=fabs(log(predicted_cardinality)-
94-
log(true_cardinality));
95-
cardinality_num_objects+=1;
9694
/*
9795
* Suppress the optimization for debug purposes.
9896
if (fabs(log(predicted_cardinality) - log(true_cardinality)) <
@@ -200,7 +198,7 @@ static bool
200198
learnOnPlanState(PlanState*p,void*context)
201199
{
202200
aqo_obj_stat*ctx= (aqo_obj_stat*)context;
203-
aqo_obj_statSubplanCtx= {NIL,NIL,NIL};
201+
aqo_obj_statSubplanCtx= {NIL,NIL,NIL,ctx->learn};
204202

205203
planstate_tree_walker(p,learnOnPlanState, (void*)&SubplanCtx);
206204

@@ -294,14 +292,18 @@ learnOnPlanState(PlanState *p, void *context)
294292
learn_rows=1.;
295293
}
296294

295+
cardinality_sum_errors+=fabs(log(predicted)-
296+
log(learn_rows));
297+
cardinality_num_objects+=1;
298+
297299
/*
298300
* A subtree was not visited. In this case we can not teach AQO
299301
* because ntuples value is equal to 0 and we will got
300302
* learn rows == 1.
301303
* It is false teaching, because at another place of a plan
302304
* scanning of the node may produce many tuples.
303305
*/
304-
if (p->instrument->nloops >=1)
306+
if (ctx->learn&&p->instrument->nloops >=1)
305307
learn_sample(SubplanCtx.clauselist,SubplanCtx.selectivities,
306308
p->plan->path_relids,learn_rows,predicted);
307309
}
@@ -366,15 +368,17 @@ aqo_ExecutorStart(QueryDesc *queryDesc, int eflags)
366368
{
367369
instr_timecurrent_time;
368370

369-
if (query_context.use_aqo||query_context.learn_aqo)
371+
if (!IsParallelWorker()&&
372+
(query_context.use_aqo||query_context.learn_aqo||force_collect_stat))
370373
{
371374
INSTR_TIME_SET_CURRENT(current_time);
372375
INSTR_TIME_SUBTRACT(current_time,query_context.query_starttime);
373376
query_context.query_planning_time=INSTR_TIME_GET_DOUBLE(current_time);
374377

375378
query_context.explain_only= ((eflags&EXEC_FLAG_EXPLAIN_ONLY)!=0);
376379

377-
if (query_context.learn_aqo&& !query_context.explain_only)
380+
if ((query_context.learn_aqo||force_collect_stat)&&
381+
!query_context.explain_only)
378382
queryDesc->instrument_options |=INSTRUMENT_ROWS;
379383

380384
/* Save all query-related parameters into the query context. */
@@ -425,9 +429,9 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
425429
query_context.collect_stat= false;
426430
}
427431

428-
if (query_context.learn_aqo)
432+
if (query_context.learn_aqo||query_context.collect_stat)
429433
{
430-
aqo_obj_statctx= {NIL,NIL,NIL};
434+
aqo_obj_statctx= {NIL,NIL,NIL,query_context.learn_aqo};
431435

432436
cardinality_sum_errors=0.;
433437
cardinality_num_objects=0;
@@ -443,13 +447,14 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
443447
INSTR_TIME_SET_CURRENT(endtime);
444448
INSTR_TIME_SUBTRACT(endtime,query_context.query_starttime);
445449
totaltime=INSTR_TIME_GET_DOUBLE(endtime);
446-
if (query_context.learn_aqo&&cardinality_num_objects>0)
450+
if (cardinality_num_objects>0)
447451
cardinality_error=cardinality_sum_errors /
448452
cardinality_num_objects;
449453
else
450454
cardinality_error=-1;
451455

452456
stat=get_aqo_stat(query_context.query_hash);
457+
453458
if (stat!=NULL)
454459
{
455460
if (query_context.use_aqo)
@@ -699,7 +704,7 @@ void print_into_explain(PlannedStmt *plannedstmt, IntoClause *into,
699704
caseAQO_MODE_LEARN:
700705
ExplainPropertyText("AQO mode","LEARN",es);
701706
break;
702-
caseAQO_MODE_FIXED:
707+
caseAQO_MODE_FROZEN:
703708
ExplainPropertyText("AQO mode","FIXED",es);
704709
break;
705710
default:

‎preprocessing.c‎

Lines changed: 54 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -122,7 +122,9 @@ aqo_planner(Query *parse,
122122
get_extension_oid("aqo", true)==InvalidOid||
123123
creating_extension||
124124
/*IsInParallelMode() ||*/IsParallelWorker()||
125-
aqo_mode==AQO_MODE_DISABLED||isQueryUsingSystemRelation(parse))
125+
(aqo_mode==AQO_MODE_DISABLED&& !force_collect_stat)||
126+
isQueryUsingSystemRelation(parse)||
127+
RecoveryInProgress())
126128
{
127129
disable_aqo_for_query();
128130
returncall_default_planner(parse,cursorOptions,boundParams);
@@ -162,7 +164,7 @@ aqo_planner(Query *parse,
162164
query_context.collect_stat= false;
163165
break;
164166
caseAQO_MODE_CONTROLLED:
165-
caseAQO_MODE_FIXED:
167+
caseAQO_MODE_FROZEN:
166168
/*
167169
* if query is not in the AQO knowledge base than disable AQO
168170
* for this query.
@@ -185,10 +187,10 @@ aqo_planner(Query *parse,
185187
/* Should never happen */
186188
break;
187189
default:
188-
elog(WARNING,"unrecognized mode in AQO: %d",aqo_mode);
190+
elog(ERROR,"unrecognized mode in AQO: %d",aqo_mode);
189191
break;
190192
}
191-
if (RecoveryInProgress())
193+
/*if (RecoveryInProgress())
192194
{
193195
if (aqo_mode == AQO_MODE_FORCED)
194196
{
@@ -202,8 +204,8 @@ aqo_planner(Query *parse,
202204
disable_aqo_for_query();
203205
return call_default_planner(parse, cursorOptions, boundParams);
204206
}
205-
}
206-
if (query_context.adding_query)
207+
}*/
208+
if (query_context.adding_query||force_collect_stat)
207209
{
208210
add_query(query_context.query_hash,query_context.learn_aqo,
209211
query_context.use_aqo,query_context.fspace_hash,
@@ -224,20 +226,60 @@ aqo_planner(Query *parse,
224226
!query_context.auto_tuning)
225227
add_deactivated_query(query_context.query_hash);
226228

227-
if (RecoveryInProgress())
229+
/*
230+
* That we can do if query exists in database.
231+
* Additional preference changes, based on AQO mode.
232+
*/
233+
switch (aqo_mode)
228234
{
235+
caseAQO_MODE_FROZEN:
236+
/*
237+
* In this mode we will suppress all writings to the knowledge base.
238+
* AQO will be used for all known queries, if it is not suppressed.
239+
*/
229240
query_context.learn_aqo= false;
230241
query_context.auto_tuning= false;
231242
query_context.collect_stat= false;
232-
}
243+
break;
233244

234-
if (aqo_mode==AQO_MODE_FIXED)
235-
{
236-
query_context.learn_aqo= false;
237-
query_context.auto_tuning= false;
245+
caseAQO_MODE_LEARN:
246+
/*
247+
* In this mode we want to learn with incoming query (if it is not
248+
* suppressed manually) and collect stats.
249+
*/
250+
query_context.collect_stat= true;
251+
query_context.fspace_hash=query_context.query_hash;
252+
break;
253+
254+
caseAQO_MODE_INTELLIGENT:
255+
caseAQO_MODE_FORCED:
256+
caseAQO_MODE_CONTROLLED:
257+
caseAQO_MODE_DISABLED:
258+
/* Use preferences as set early. */
259+
break;
260+
261+
default:
262+
elog(ERROR,"Unrecognized aqo mode %d",aqo_mode);
238263
}
239264
}
240265

266+
/*
267+
* This mode is possible here, because force collect statistics uses AQO
268+
* machinery.
269+
*/
270+
if (aqo_mode==AQO_MODE_DISABLED)
271+
disable_aqo_for_query();
272+
273+
if (force_collect_stat)
274+
{
275+
/*
276+
* If this GUC is set, AQO will analyze query results and collect
277+
* query execution statistics in any mode.
278+
*/
279+
query_context.collect_stat= true;
280+
query_context.fspace_hash=query_context.query_hash;
281+
}
282+
241283
query_context.explain_aqo=query_context.use_aqo;
242284
returncall_default_planner(parse,cursorOptions,boundParams);
243285
}

0 commit comments

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