8000 Stabilize unsupported.sql tests with adding of 'ANALYZE t' command. · postgrespro/aqo@8a4907c · GitHub
[go: up one dir, main page]
Skip to content

Commit 8a4907c

Browse files
danolivoAndrey Lepikhov
danolivo
authored and
Andrey Lepikhov
committed
Stabilize unsupported.sql tests with adding of 'ANALYZE t' command.
It was required to change ExplainOneNode_hook code and parameters. Now, we can show AQO prediction in EXPLAIN ONLY mode. The Core patch is changed.
1 parent b0d8a33 commit 8a4907c

File tree

5 files changed

+85
-71
lines changed

5 files changed

+85
-71
lines changed

‎aqo.h

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -313,8 +313,7 @@ extern void print_into_explain(PlannedStmt *plannedstmt, IntoClause *into,
313313
ParamListInfo params,
314314
const instr_time *planduration,
315315
QueryEnvironment *queryEnv);
316-
extern void print_node_explain(ExplainState *es, PlanState *ps, Plan *plan,
317-
double rows);
316+
extern void print_node_explain(ExplainState *es, PlanState *ps, Plan *plan);
318317
extern void disable_aqo_for_query(void);
319318

320319
/* Cardinality estimation hooks */

‎aqo_master.patch

Lines changed: 18 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ index f27e458482..0c62191904 100644
1111
auto_explain \
1212
bloom \
1313
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
14-
index e81b990092..157f6e2721 100644
14+
index e81b990092..123bd27f1c 100644
1515
--- a/src/backend/commands/explain.c
1616
+++ b/src/backend/commands/explain.c
1717
@@ -24,6 +24,7 @@
@@ -46,16 +46,16 @@ index e81b990092..157f6e2721 100644
4646
ExplainCloseGroup("Query", NULL, true, es);
4747
}
4848

49-
@@ -1620,6 +1631,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
50-
appendStringInfo(es->str,
51-
" (actual rows=%.0f loops=%.0f)",
52-
rows, nloops);
53-
+
54-
+ if (ExplainOneNode_hook)
55-
+ ExplainOneNode_hook(es, planstate, plan, rows);
49+
@@ -1650,6 +1661,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
5650
}
57-
else
58-
{
51+
}
52+
53+
+ if (ExplainOneNode_hook)
54+
+ ExplainOneNode_hook(es, planstate, plan);
55+
+
56+
/* in text format, first line ends here */
57+
if (es->format == EXPLAIN_FORMAT_TEXT)
58+
appendStringInfoChar(es->str, '\n');
5959
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
6060
index bd87f23784..f4d567b8ac 100644
6161
--- a/src/backend/nodes/copyfuncs.c
@@ -459,10 +459,10 @@ index e105a4d5f1..d821ea63bd 100644
459459

460460
return ppi;
461461
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
462-
index e94d9e49cf..667ba7e58d 100644
462+
index e94d9e49cf..49236ced77 100644
463463
--- a/src/include/commands/explain.h
464464
+++ b/src/include/commands/explain.h
465-
@@ -75,6 +75,19 @@ extern PGDLLIMPORT ExplainOneQuery_hook_type ExplainOneQuery_hook;
465+
@@ -75,6 +75,18 @@ extern PGDLLIMPORT ExplainOneQuery_hook_type ExplainOneQuery_hook;
466466
typedef const char *(*explain_get_index_name_hook_type) (Oid indexId);
467467
extern PGDLLIMPORT explain_get_index_name_hook_type explain_get_index_name_hook;
468468

@@ -476,14 +476,13 @@ index e94d9e49cf..667ba7e58d 100644
476476
+/* Explain a node info */
477477
+typedef void (*ExplainOneNode_hook_type) (ExplainState *es,
478478
+ PlanState *ps,
479-
+ Plan *plan,
480-
+ double rows);
479+
+ Plan *plan);
481480
+extern PGDLLIMPORT ExplainOneNode_hook_type ExplainOneNode_hook;
482481

483482
extern void ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
484483
ParamListInfo params, DestReceiver *dest);
485484
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
486-
index b7b2817a5d..fe6f7266f5 100644
485+
index b7b2817a5d..cafad7009b 100644
487486
--- a/src/include/nodes/pathnodes.h
488487
+++ b/src/include/nodes/pathnodes.h
489488
@@ -751,6 +751,10 @@ typedef struct RelOptInfo
@@ -497,15 +496,17 @@ index b7b2817a5d..fe6f7266f5 100644
497496
/* used for partitioned relations: */
498497
PartitionScheme part_scheme; /* Partitioning scheme */
499498
int nparts; /* Number of partitions; -1 if not yet set; in
500-
@@ -765,6 +769,8 @@ typedef struct RelOptInfo
499+
@@ -765,7 +769,9 @@ typedef struct RelOptInfo
501500
Relids all_partrels; /* Relids set of all partition relids */
502501
List **partexprs; /* Non-nullable partition key expressions */
503502
List **nullable_partexprs; /* Nullable partition key expressions */
503+
-} RelOptInfo;
504504
+
505505
B41A + List *private;
506-
} RelOptInfo;
506+
+} RelOptInfo;
507507

508508
/*
509+
* Is given relation partitioned?
509510
@@ -1133,6 +1139,10 @@ typedef struct ParamPathInfo
510511
Relids ppi_req_outer; /* rels supplying parameters used by path */
511512
double ppi_rows; /* estimated number of result tuples */

‎expected/unsupported.out

Lines changed: 30 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -3,41 +3,36 @@ SET aqo.mode = 'learn';
33
SET aqo.show_details = 'on';
44
DROP TABLE IF EXISTS t;
55
CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;
6-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
7-
SELECT * FROM t GROUP BY (x) HAVING x > 3;
8-
QUERY PLAN
9-
-----------------------------------------------
10-
HashAggregate (actual rows=17 loops=1)
11-
AQO not used
12-
Group Key: x
13-
Batches: 1 Memory Usage: 40kB
14-
-> Seq Scan on t (actual rows=801 loops=1)
15-
AQO not used
16-
Filter: (x > 3)
17-
Rows Removed by Filter: 199
18-
Using aqo: true
19-
AQO mode: LEARN
20-
JOINS: 0
21-
(11 rows)
6+
ANALYZE t;
7+
--
8+
-- Do not support HAVING clause for now.
9+
--
10+
SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
11+
count
12+
-------
13+
17
14+
(1 row)
2215

23-
-- Do not support having clauses for now.
24-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
25-
SELECT * FROM t GROUP BY (x) HAVING x > 3;
26-
QUERY PLAN
27-
-----------------------------------------------
28-
HashAggregate (actual rows=17 loops=1)
16+
EXPLAIN (COSTS OFF)
17+
SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
18+
QUERY PLAN
19+
-------------------------------
20+
Aggregate
2921
AQO not used
30-
Group Key: x
31-
Batches: 1 Memory Usage: 40kB
32-
-> Seq Scan on t (actual rows=801 loops=1)
33-
AQO: rows=801, error=0%
34-
Filter: (x > 3)
35-
Rows Removed by Filter: 199
22+
-> HashAggregate
23+
AQO not used
24+
Group Key: t.x
25+
-> Seq Scan on t
26+
AQO: rows=801
27+
Filter: (x > 3)
3628
Using aqo: true
3729
AQO mode: LEARN
3830
JOINS: 0
3931
(11 rows)
4032

33+
--
34+
-- The subplans issue
35+
--
4136
SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t WHERE x = 1);
4237
count
4338
-------
@@ -322,18 +317,18 @@ SELECT * FROM
322317
JOIN
323318
(SELECT * FROM t WHERE x > 20) AS t1
324319
USING(x);
325-
QUERY PLAN
326-
------------------------------------------------
327-
Hash Join (actual rows=0 loops=1)
320+
QUERY PLAN
321+
---------------------------------------------
322+
Nested Loop (actual rows=0 loops=1)
328323
AQO not used
329-
Hash Cond: (t.x = t_1.x)
324+
Join Filter: (t.x = t_1.x)
330325
-> Seq Scan on t (actual rows=0 loops=1)
331326
AQO not used
332327
Filter: (x < 0)
333328
Rows Removed by Filter: 1000
334-
-> Hash (never executed)
335-
-> Seq Scan on t t_1 (never executed)
336-
Filter: (x > 20)
329+
-> Seq Scan on t t_1 (never executed)
330+
AQO not used
331+
Filter: (x > 20)
337332
Using aqo: true
338333
AQO mode: LEARN
339334
JOINS: 0

‎postprocessing.c

Lines changed: 26 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -755,17 +755,28 @@ RemoveFromQueryEnv(QueryDesc *queryDesc)
755755
}
756756

757757
void
758-
print_node_explain(ExplainState *es, PlanState *ps, Plan *plan, double rows)
758+
print_node_explain(ExplainState *es, PlanState *ps, Plan *plan)
759759
{
760760
int wrkrs = 1;
761761
double error = -1.;
762762
AQOPlanNode *aqo_node;
763763

764-
if (!aqo_show_details || !plan || !ps->instrument)
764+
/* Extension, which took a hook early can be executed early too. */
765+
if (prev_ExplainOneNode_hook)
766+
prev_ExplainOneNode_hook(es, ps, plan);
767+
768+
if (es->format != EXPLAIN_FORMAT_TEXT)
769+
/* Only text format is supported. */
770+
return;
771+
772+
if (!aqo_show_details || !plan || !ps)
765773
goto explain_end;
766774

767775
aqo_node = get_aqo_plan_node(plan, false);
768-
Assert(es->format == EXPLAIN_FORMAT_TEXT);
776+
777+
if (!ps->instrument)
778+
/* We can show only prediction, without error calculation */
779+
goto explain_print;
769780

770781
if (ps->worker_instrument &&
771782
IsParallelTuplesProcessing(plan, aqo_node->parallel_divisor > 0))
@@ -783,28 +794,32 @@ print_node_explain(ExplainState *es, PlanState *ps, Plan *plan, double rows)
783794
}
784795
}
785796

797+
explain_print:
786798
appendStringInfoChar(es->str, '\n');
787-
Assert(es->format == EXPLAIN_FORMAT_TEXT);
788799
if (es->str->len == 0 || es->str->data[es->str->len - 1] == '\n')
789800
appendStringInfoSpaces(es->str, es->indent * 2);
790801

791802
if (aqo_node->prediction > 0.)
792803
{
793-
error = 100. * (aqo_node->prediction - (rows*wrkrs))
804+
appendStringInfo(es->str, "AQO: rows=%.0lf", aqo_node->prediction);
805+
806+
if (ps->instrument)
807+
{
808+
double nloops = ps->instrument->nloops;
809+
double rows = ps->instrument->ntuples / nloops;
810+
811+
error = 100. * (aqo_node->prediction - (rows*wrkrs))
794812
/ aqo_node->prediction;
795-
appendStringInfo(es->str,
796-
"AQO: rows=%.0lf, error=%.0lf%%",
797-
aqo_node->prediction, error);
813+
appendStringInfo(es->str, ", error=%.0lf%%", error);
814+
}
798815
}
799816
else
800817
appendStringInfo(es->str, "AQO not used");
801818

802819
explain_end:
820+
/* XXX: Do we really have situations than plan is NULL? */
803821
if (plan && aqo_show_hash)
804822
appendStringInfo(es->str, ", fss=%d", aqo_node->fss);
805-
806-
if (prev_ExplainOneNode_hook)
807-
prev_ExplainOneNode_hook(es, ps, plan, rows);
808823
}
809824

810825
/*

‎sql/unsupported.sql

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -4,14 +4,18 @@ SET aqo.show_details = 'on';
44

55
DROP TABLE IF EXISTS t;
66
CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;
7+
ANALYZE t;
78

8-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
9-
SELECT * FROM t GROUP BY (x) HAVING x > 3;
10-
11-
-- Do not support having clauses for now.
12-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
13-
SELECT * FROM t GROUP BY (x) HAVING x > 3;
9+
--
10+
-- Do not support HAVING clause for now.
11+
--
12+
SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
13+
EXPLAIN (COSTS OFF)
14+
SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
1415

16+
--
17+
-- The subplans issue
18+
--
1519
SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t WHERE x = 1);
1620
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
1721
SELECT count(*) FROM t WHERE x = (

0 commit comments

Comments
 (0)
0