Skip to content

Commit a90ab8f

Browse files
danolivodanolivo
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 query execution. 5. Bugfix: predicted rows can't be 0.
1 parent a96e018 commit a90ab8f

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
SELECT learn_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 n2 FROM
43-
(SELECT cardinality_error_with_aqo AS n1, cardinality_error_without_aqo AS 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_aqo AS n1,
54+
cardinality_error_without_aqo AS n2,
55+
execution_time_with_aqo AS n3,
56+
execution_time_without_aqo AS n4
4457
FROM aqo_query_stat aqs WHERE
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
return lst;
184184
}
185185

186+
static bool
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+
return planstate_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
double learn_rows = 0.;
234245
double predicted = 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-
else if (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+
else if (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 false teaching, because at another place of a plan
304-
* scanning of the node may produce many tuples.
312+
* It is false knowledge: 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
double cardinality_error,
328339
int64 *n_exec)
329340
{
330-
int i;
341+
int i;
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, int eflags)
368381
{
369382
instr_time current_time;
383+
bool use_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-
double totaltime;
407-
double cardinality_error;
408-
QueryStat *stat = NULL;
409-
instr_time endtime;
422+
double totaltime;
423+
double cardinality_error;
424+
QueryStat *stat = NULL;
425+
instr_time endtime;
410426
EphemeralNamedRelation enr = 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
goto end;
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_stat ctx = {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
case AQO_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)