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

Commita90ab8f

Browse files
committed
Cumulative commit:
1. Extend aqo_status() function.2. Ignore query plans that contains at least one 'never executed' node.3. Show query hash on explain verbose mode.4. Bugfix: remove query environment, created by AQO after the end of queryexecution.5. Bugfix: predicted rows can't be 0.
1 parenta96e018 commita90ab8f

File tree

2 files changed

+82
-43
lines changed

2 files changed

+82
-43
lines changed

‎aqo--1.1--1.2.sql‎

Lines changed: 19 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -31,18 +31,31 @@ RETURNS TABLE (
3131
"use aqo"BOOL,
3232
"auto tune"BOOL,
3333
"fspace hash"INT,
34-
"aqo error"TEXT,
35-
"base error"TEXT
34+
"t_naqo"TEXT,
35+
"err_naqo"TEXT,
36+
"iters"BIGINT,
37+
"t_aqo"TEXT,
38+
"err_aqo"TEXT,
39+
"iters_aqo"BIGINT
3640
)
3741
AS $func$
3842
SELECTlearn_aqo,use_aqo,auto_tuning,fspace_hash,
43+
to_char(execution_time_without_aqo[n4],'9.99EEEE'),
44+
to_char(cardinality_error_without_aqo[n2],'9.99EEEE'),
45+
executions_without_aqo,
46+
to_char(execution_time_with_aqo[n3],'9.99EEEE'),
3947
to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),
40-
to_char(cardinality_error_without_aqo[n2],'9.99EEEE')
48+
executions_with_aqo
4149
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
50+
(SELECT array_length(n1,1)AS n1, array_length(n2,1)AS n2,
51+
array_length(n3,1)AS n3, array_length(n4,1)AS n4
52+
FROM
53+
(SELECT cardinality_error_with_aqoAS n1,
54+
cardinality_error_without_aqoAS n2,
55+
execution_time_with_aqoAS n3,
56+
execution_time_without_aqoAS n4
4457
FROM aqo_query_stat aqsWHERE
45-
aqs.query_hash= $1)AS al)AS q
58+
aqs.query_hash= $1)AS al)AS q
4659
WHERE (aqs.query_hash=aq.query_hash)AND
4760
aqs.query_hash= $1;
4861
$func$ LANGUAGE SQL;

‎postprocessing.c‎

Lines changed: 63 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -183,6 +183,17 @@ restore_selectivities(List *clauselist,
183183
returnlst;
184184
}
185185

186+
staticbool
187+
HasNeverVisitedNodes(PlanState*ps,void*context)
188+
{
189+
Assert(context==NULL);
190+
191+
InstrEndLoop(ps->instrument);
192+
if (ps->instrument==NULL||ps->instrument->nloops==0)
193+
return true;
194+
195+
returnplanstate_tree_walker(ps,HasNeverVisitedNodes,NULL);
196+
}
186197
/*
187198
* Walks over obtained PlanState tree, collects relation objects with their
188199
* clauses, selectivities and relids and passes each object to learn_sample.
@@ -233,7 +244,6 @@ learnOnPlanState(PlanState *p, void *context)
233244
doublelearn_rows=0.;
234245
doublepredicted=0.;
235246

236-
InstrEndLoop(p->instrument);
237247
if (p->instrument->nloops>0.)
238248
{
239249
/* If we can strongly calculate produced rows, do it. */
@@ -269,41 +279,42 @@ learnOnPlanState(PlanState *p, void *context)
269279

270280
if (p->plan->predicted_cardinality>0.)
271281
predicted=p->plan->predicted_cardinality;
272-
elseif (p->plan->parallel_aware||
273-
(p->plan->path_parallel_workers>0&&
274-
(nodeTag(p->plan)==T_HashJoin||
275-
nodeTag(p->plan)==T_MergeJoin||
276-
nodeTag(p->plan)==T_NestLoop)))
282+
elseif (IsParallelTuplesProcessing(p->plan))
277283
predicted=p->plan->plan_rows*
278284
get_parallel_divisor(p->plan->path_parallel_workers);
279285
else
280286
predicted=p->plan->plan_rows;
281287

282288
/* It is needed for correct exp(result) calculation. */
289+
predicted=clamp_row_est(predicted);
283290
learn_rows=clamp_row_est(learn_rows);
284291
}
285292
else
286293
{
287294
/*
288-
* LAV: I found only one case for this code: if query returns
289-
* with error. May be we will process this case and not learn
290-
* AQO on the query?
295+
* LAV: I found two cases for this code:
296+
* 1. if query returns with error.
297+
* 2. plan node has never visited.
298+
* Both cases can't be used to learning AQO because give an
299+
* incorrect number of rows.
291300
*/
292-
learn_rows=1.;
301+
elog(PANIC,"AQO: impossible situation");
293302
}
294303

295-
cardinality_sum_errors+=fabs(log(predicted)-
296-
log(learn_rows));
304+
Assert(predicted >=1&&learn_rows >=1);
305+
cardinality_sum_errors+=fabs(log(predicted)-log(learn_rows));
297306
cardinality_num_objects+=1;
298307

299308
/*
300309
* A subtree was not visited. In this case we can not teach AQO
301310
* because ntuples value is equal to 0 and we will got
302311
* learn rows == 1.
303-
* It is falseteaching, becauseat another place of a plan
304-
*scanning ofthe node may produce many tuples.
312+
* It is falseknowledge:at another place of a plan, scanning of
313+
* the node may produce many tuples.
305314
*/
306-
if (ctx->learn&&p->instrument->nloops >=1)
315+
Assert(p->instrument->nloops >=1);
316+
317+
if (ctx->learn)
307318
learn_sample(SubplanCtx.clauselist,SubplanCtx.selectivities,
308319
p->plan->path_relids,learn_rows,predicted);
309320
}
@@ -327,16 +338,18 @@ update_query_stat_row(double *et, int *et_size,
327338
doublecardinality_error,
328339
int64*n_exec)
329340
{
330-
inti;
341+
inti;
331342

332-
if (cardinality_error >=0)
333-
{
334-
if (*ce_size >=aqo_stat_size)
343+
/*
344+
* If plan contains one or more "never visited" nodes, cardinality_error
345+
* have -1 value and will be written to the knowledge base. User can use it
346+
* as a sign that AQO ignores this query.
347+
*/
348+
if (*ce_size >=aqo_stat_size)
335349
for (i=1;i<aqo_stat_size;++i)
336350
ce[i-1]=ce[i];
337351
*ce_size= (*ce_size >=aqo_stat_size) ?aqo_stat_size : (*ce_size+1);
338352
ce[*ce_size-1]=cardinality_error;
339-
}
340353

341354
if (*et_size >=aqo_stat_size)
342355
for (i=1;i<aqo_stat_size;++i)
@@ -367,9 +380,12 @@ void
367380
aqo_ExecutorStart(QueryDesc*queryDesc,inteflags)
368381
{
369382
instr_timecurrent_time;
383+
booluse_aqo;
384+
385+
use_aqo= !IsParallelWorker()&& (query_context.use_aqo||
386+
query_context.learn_aqo||force_collect_stat);
370387

371-
if (!IsParallelWorker()&&
372-
(query_context.use_aqo||query_context.learn_aqo||force_collect_stat))
388+
if (use_aqo)
373389
{
374390
INSTR_TIME_SET_CURRENT(current_time);
375391
INSTR_TIME_SUBTRACT(current_time,query_context.query_starttime);
@@ -391,8 +407,8 @@ aqo_ExecutorStart(QueryDesc *queryDesc, int eflags)
391407
standard_ExecutorStart(queryDesc,eflags);
392408

393409
/* Plan state has initialized */
394-
395-
StorePlanInternals(queryDesc);
410+
if (use_aqo)
411+
StorePlanInternals(queryDesc);
396412
}
397413

398414
/*
@@ -403,12 +419,15 @@ aqo_ExecutorStart(QueryDesc *queryDesc, int eflags)
403419
void
404420
aqo_ExecutorEnd(QueryDesc*queryDesc)
405421
{
406-
doubletotaltime;
407-
doublecardinality_error;
408-
QueryStat*stat=NULL;
409-
instr_timeendtime;
422+
doubletotaltime;
423+
doublecardinality_error;
424+
QueryStat*stat=NULL;
425+
instr_timeendtime;
410426
EphemeralNamedRelationenr=get_ENR(queryDesc->queryEnv,PlanStateInfo);
411427

428+
cardinality_sum_errors=0.;
429+
cardinality_num_objects=0;
430+
412431
if (!ExtractFromQueryContext(queryDesc))
413432
/* AQO keep all query-related preferences at the query context.
414433
* It is needed to prevent from possible recursive changes, at
@@ -418,8 +437,7 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
418437
*/
419438
gotoend;
420439

421-
if (enr)
422-
njoins=*(int*)enr->reldata;
440+
njoins= (enr!=NULL) ?*(int*)enr->reldata :-1;
423441

424442
Assert(!IsParallelWorker());
425443

@@ -429,13 +447,11 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
429447
query_context.collect_stat= false;
430448
}
431449

432-
if (query_context.learn_aqo||query_context.collect_stat)
450+
if ((query_context.learn_aqo||query_context.collect_stat)&&
451+
!HasNeverVisitedNodes(queryDesc->planstate,NULL))
433452
{
434453
aqo_obj_statctx= {NIL,NIL,NIL,query_context.learn_aqo};
435454

436-
cardinality_sum_errors=0.;
437-
cardinality_num_objects=0;
438-
439455
learnOnPlanState(queryDesc->planstate, (void*)&ctx);
440456
list_free(ctx.clauselist);
441457
list_free(ctx.relidslist);
@@ -448,8 +464,7 @@ aqo_ExecutorEnd(QueryDesc *queryDesc)
448464
INSTR_TIME_SUBTRACT(endtime,query_context.query_starttime);
449465
totaltime=INSTR_TIME_GET_DOUBLE(endtime);
450466
if (cardinality_num_objects>0)
451-
cardinality_error=cardinality_sum_errors /
452-
cardinality_num_objects;
467+
cardinality_error=cardinality_sum_errors /cardinality_num_objects;
453468
else
454469
cardinality_error=-1;
455470

@@ -668,6 +683,12 @@ RemoveFromQueryContext(QueryDesc *queryDesc)
668683
unregister_ENR(queryDesc->queryEnv,AQOPrivateData);
669684
pfree(enr->reldata);
670685
pfree(enr);
686+
687+
/* Remove the plan state internals */
688+
enr=get_ENR(queryDesc->queryEnv,PlanStateInfo);
689+
unregister_ENR(queryDesc->queryEnv,PlanStateInfo);
690+
pfree(enr->reldata);
691+
pfree(enr);
671692
}
672693

673694
/*
@@ -705,13 +726,18 @@ void print_into_explain(PlannedStmt *plannedstmt, IntoClause *into,
705726
ExplainPropertyText("AQO mode","LEARN",es);
706727
break;
707728
caseAQO_MODE_FROZEN:
708-
ExplainPropertyText("AQO mode","FIXED",es);
729+
ExplainPropertyText("AQO mode","FROZEN",es);
709730
break;
710731
default:
711732
elog(ERROR,"Bad AQO state");
712733
break;
713734
}
714735

736+
/*
737+
* Query hash provides an user the conveniently use of the AQO
738+
* auxiliary functions.
739+
*/
740+
ExplainPropertyInteger("Query hash",NULL,query_context.query_hash,es);
715741
ExplainPropertyInteger("JOINS",NULL,njoins,es);
716742
}
717743
query_context.explain_aqo= false;

0 commit comments

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