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

Commit07bc563

Browse files
danolivoAlena0704
authored andcommitted
Fix the bug with serialization machinery.
Change the AQO plan node behaviour: now we add such node also in the casewhen forced stat gathering option is enabled even in disabled mode.Also:1. Do not add AQO info into an explain AQO sentence if AQO node isn't existedfor the node.2. Exclude node which aren't predicted by AQO from a cardinality errorcomputation.Introduce 'parallel_workers' regression test.
1 parent42e41a4 commit07bc563

File tree

9 files changed

+236
-84
lines changed

9 files changed

+236
-84
lines changed

‎Makefile‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ REGRESS =aqo_disabled \
2323
forced_stat_collection\
2424
unsupported\
2525
clean_aqo_data\
26+
parallel_workers\
2627
plancache\
2728
statement_timeout\
2829
temp_tables\
@@ -49,4 +50,4 @@ subdir = contrib/aqo
4950
top_builddir = ../..
5051
include$(top_builddir)/src/Makefile.global
5152
include$(top_srcdir)/contrib/contrib-global.mk
52-
endif
53+
endif

‎expected/feature_subspace.out‎

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -29,19 +29,17 @@ WHERE str NOT LIKE '%Memory%';
2929
AQO not used
3030
Merge Cond: (a.x = b.x)
3131
-> Sort (actual rows=10 loops=1)
32-
AQO not used
3332
Sort Key: a.x
3433
-> Seq Scan on a (actual rows=10 loops=1)
3534
AQO not used
3635
-> Sort (actual rows=11 loops=1)
37-
AQO not used
3836
Sort Key: b.x
3937
-> Seq Scan on b (actual rows=100 loops=1)
4038
AQO not used
4139
Using aqo: true
4240
AQO mode: LEARN
4341
JOINS: 0
44-
(16 rows)
42+
(14 rows)
4543

4644
-- TODO: Using method of other classes neighbours we get a bad estimation.
4745
SELECT str AS result
@@ -56,13 +54,12 @@ WHERE str NOT LIKE '%Memory%';
5654
-> Seq Scan on b (actual rows=100 loops=1)
5755
AQO: rows=100, error=0%
5856
-> Hash (actual rows=10 loops=1)
59-
AQO not used
6057
-> Seq Scan on a (actual rows=10 loops=1)
6158
AQO: rows=10, error=0%
6259
Using aqo: true
6360
AQO mode: LEARN
6461
JOINS: 0
65-
(12 rows)
62+
(11 rows)
6663

6764
-- Look into the reason: two JOINs from different classes have the same FSS.
6865
SELECT to_char(d1.targets[1], 'FM999.00') AS target FROM aqo_data d1

‎expected/look_a_like.out‎

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -148,15 +148,13 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
148148
Output: a.x, b.y
149149
Merge Cond: (a.x = b.y)
150150
-> Sort (actual rows=1000 loops=1)
151-
AQO not used
152151
Output: a.x
153152
Sort Key: a.x
154153
-> Seq Scan on public.a (actual rows=1000 loops=1)
155154
AQO: rows=1000, error=0%
156155
Output: a.x
157156
Filter: (a.x < 10)
158157
-> Sort (actual rows=99901 loops=1)
159-
AQO not used
160158
Output: b.y
161159
Sort Key: b.y
162160
-> Seq Scan on public.b (actual rows=1000 loops=1)
@@ -165,7 +163,7 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
165163
Using aqo: true
166164
AQO mode: LEARN
167165
JOINS: 0
168-
(22 rows)
166+
(20 rows)
169167

170168
-- cardinality 100 in Seq Scan on a and Seq Scan on b
171169
SELECT str AS result
@@ -215,7 +213,6 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%'
215213
Output: a.x
216214
Filter: (a.x < 10)
217215
-> Hash (actual rows=0 loops=1)
218-
AQO not used
219216
Output: b.y
220217
-> Seq Scan on public.b (actual rows=0 loops=1)
221218
AQO: rows=1, error=100%
@@ -225,7 +222,7 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%'
225222
Using aqo: true
226223
AQO mode: LEARN
227224
JOINS: 0
228-
(19 rows)
225+
(18 rows)
229226

230227
RESET enable_material;
231228
DROP TABLE a,b CASCADE;

‎expected/parallel_workers.out‎

Lines changed: 125 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,125 @@
1+
-- Specifically test AQO machinery for queries uses partial paths and executed
2+
-- with parallel workers.
3+
CREATE EXTENSION aqo;
4+
-- Utility tool. Allow to filter system-dependent strings from explain output.
5+
CREATE OR REPLACE FUNCTION expln(query_string text) RETURNS SETOF text AS $$
6+
BEGIN
7+
RETURN QUERY
8+
EXECUTE format('%s', query_string);
9+
RETURN;
10+
END;
11+
$$ LANGUAGE PLPGSQL;
12+
SET aqo.join_threshold = 0;
13+
SET aqo.mode = 'learn';
14+
SET aqo.show_details = true;
15+
-- Be generous with a number parallel workers to test the machinery
16+
SET max_parallel_workers = 64;
17+
SET max_parallel_workers_per_gather = 64;
18+
-- Enforce usage of parallel workers
19+
SET parallel_setup_cost = 0.1;
20+
SET parallel_tuple_cost = 0.0001;
21+
CREATE TABLE t AS (
22+
SELECT x AS id, repeat('a', 512) AS payload FROM generate_series(1, 1E5) AS x
23+
);
24+
ANALYZE t;
25+
-- Simple test. Check serialization machinery mostly.
26+
SELECT count(*) FROM t WHERE id % 100 = 0; -- Learning stage
27+
count
28+
-------
29+
1000
30+
(1 row)
31+
32+
SELECT str FROM expln('
33+
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
34+
SELECT count(*) FROM t WHERE id % 100 = 0;') AS str
35+
WHERE str NOT LIKE '%Worker%';
36+
str
37+
--------------------------------------------------------------------
38+
Finalize Aggregate (actual rows=1 loops=1)
39+
AQO not used
40+
-> Gather (actual rows=3 loops=1)
41+
AQO not used
42+
-> Partial Aggregate (actual rows=1 loops=3)
43+
AQO not used
44+
-> Parallel Seq Scan on t (actual rows=333 loops=3)
45+
AQO: rows=1000, error=0%
46+
Filter: ((id % '100'::numeric) = '0'::numeric)
47+
Rows Removed by Filter: 33000
48+
Using aqo: true
49+
AQO mode: LEARN
50+
JOINS: 0
51+
(13 rows)
52+
53+
-- More complex query just to provoke errors
54+
SELECT count(*) FROM
55+
(SELECT id FROM t WHERE id % 100 = 0 GROUP BY (id)) AS q1,
56+
(SELECT max(id) AS id, payload FROM t
57+
WHERE id % 101 = 0 GROUP BY (payload)) AS q2
58+
WHERE q1.id = q2.id; -- Learning stage
59+
count
60+
-------
61+
0
62+
(1 row)
63+
64+
-- XXX: Why grouping prediction isn't working here?
65+
SELECT str FROM expln('
66+
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
67+
SELECT count(*) FROM
68+
(SELECT id FROM t WHERE id % 100 = 0 GROUP BY (id)) AS q1,
69+
(SELECT max(id) AS id, payload FROM t
70+
WHERE id % 101 = 0 GROUP BY (payload)) AS q2
71+
WHERE q1.id = q2.id;') AS str
72+
WHERE str NOT LIKE '%Workers%' AND str NOT LIKE '%Sort Method%';
73+
str
74+
--------------------------------------------------------------------------------------------------
75+
Aggregate (actual rows=1 loops=1)
76+
AQO not used
77+
-> Merge Join (actual rows=0 loops=1)
78+
AQO not used
79+
Merge Cond: (q2.id = t_1.id)
80+
-> Sort (actual rows=1 loops=1)
81+
Sort Key: q2.id
82+
-> Subquery Scan on q2 (actual rows=1 loops=1)
83+
AQO not used
84+
-> Finalize GroupAggregate (actual rows=1 loops=1)
85+
AQO not used
86+
Group Key: t.payload
87+
-> Gather Merge (actual rows=3 loops=1)
88+
AQO not used
89+
-> Partial GroupAggregate (actual rows=1 loops=3)
90+
AQO not used
91+
Group Key: t.payload
92+
-> Sort (actual rows=330 loops=3)
93+
AQO not used
94+
Sort Key: t.payload
95+
-> Parallel Seq Scan on t (actual rows=330 loops=3)
96+
AQO: rows=991, error=0%
97+
Filter: ((id % '101'::numeric) = '0'::numeric)
98+
Rows Removed by Filter: 33003
99+
-> Group (actual rows=1000 loops=1)
100+
AQO not used
101+
Group Key: t_1.id
102+
-> Gather Merge (actual rows=1000 loops=1)
103+
AQO not used
104+
-> Group (actual rows=333 loops=3)
105+
AQO not used
106+
Group Key: t_1.id
107+
-> Sort (actual rows=333 loops=3)
108+
AQO not used
109+
Sort Key: t_1.id
110+
-> Parallel Seq Scan on t t_1 (actual rows=333 loops=3)
111+
AQO: rows=991, error=-1%
112+
Filter: ((id % '100'::numeric) = '0'::numeric)
113+
Rows Removed by Filter: 33000
114+
Using aqo: true
115+
AQO mode: LEARN
116+
JOINS: 1
117+
(42 rows)
118+
119+
RESET parallel_tuple_cost;
120+
RESET parallel_setup_cost;
121+
RESET max_parallel_workers;
122+
RESET max_parallel_workers_per_gather;
123+
DROP TABLE t;
124+
DROP FUNCTION expln;
125+
DROP EXTENSION aqo;

‎expected/unsupported.out‎

Lines changed: 3 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -375,7 +375,6 @@ SELECT count(*) FROM
375375
Filter: (x <> t_1.x)
376376
Rows Removed by Filter: 50
377377
-> Hash (actual rows=851 loops=1)
378-
AQO not used
379378
-> Seq Scan on t (actual rows=851 loops=1)
380379
AQO: rows=851, error=0%
381380
Filter: (((x % 3))::numeric < (SubPlan 1))
@@ -390,7 +389,7 @@ SELECT count(*) FROM
390389
Using aqo: true
391390
AQO mode: LEARN
392391
JOINS: 1
393-
(31 rows)
392+
(30 rows)
394393

395394
-- Two identical subplans in a clause
396395
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
@@ -543,12 +542,11 @@ WHERE str NOT LIKE '%Heap Blocks%' AND str NOT LIKE '%Query Identifier%';
543542
Filter: (t.x < 3)
544543
Rows Removed by Filter: 300
545544
-> Bitmap Index Scan on ind2 (actual rows=350 loops=1)
546-
AQO not used
547545
Index Cond: (mod(t.x, 3) = 1)
548546
Using aqo: true
549547
AQO mode: LEARN
550548
JOINS: 0
551-
(14 rows)
549+
(13 rows)
552550

553551
-- Best choice is ...
554552
ANALYZE t;
@@ -577,7 +575,7 @@ ORDER BY (md5(query_text),error) DESC;
577575
-------+------------------------------------------------------------------------------------------------
578576
0.768 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,y)) AS q1;
579577
0.070 | SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
580-
1.416 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
578+
0.000 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
581579
0.000 | SELECT * FROM +
582580
| (SELECT * FROM t WHERE x < 0) AS t0 +
583581
| JOIN +

‎path_utils.c‎

Lines changed: 20 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -63,9 +63,7 @@ create_aqo_plan_node()
6363

6464
/*
6565
* Extract an AQO node from the plan private field.
66-
* If no one node was found, return pointer to the default value or allocate new
67-
* node (with default value) according to 'create' field.
68-
* Can't return NULL value at all.
66+
* If no one node was found, return pointer to the default value or return NULL.
6967
*/
7068
AQOPlanNode*
7169
get_aqo_plan_node(Plan*plan,boolcreate)
@@ -90,7 +88,7 @@ get_aqo_plan_node(Plan *plan, bool create)
9088
if (node==NULL)
9189
{
9290
if (!create)
93-
return&DefaultAQOPlanNode;
91+
returnNULL;
9492

9593
node=create_aqo_plan_node();
9694
plan->ext_nodes=lappend(plan->ext_nodes,node);
@@ -481,9 +479,14 @@ is_appropriate_path(Path *path)
481479
}
482480

483481
/*
484-
* Converts path info into plan node for collecting it after query execution.
482+
* Add AQO data into the plan node, if necessary.
483+
*
484+
* The necesssary case is when AQO is learning on this query, used for a
485+
* prediction (and we will need the data to show prediction error at the end) or
486+
* just to gather a plan statistics.
485487
* Don't switch here to any AQO-specific memory contexts, because we should
486-
* store AQO prediction in the same context, as the plan.
488+
* store AQO prediction in the same context, as the plan. So, explicitly free
489+
* all unneeded data.
487490
*/
488491
void
489492
aqo_create_plan_hook(PlannerInfo*root,Path*src,Plan**dest)
@@ -495,7 +498,8 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
495498
if (prev_create_plan_hook)
496499
prev_create_plan_hook(root,src,dest);
497500

498-
if (!query_context.use_aqo&& !query_context.learn_aqo)
501+
if (!query_context.use_aqo&& !query_context.learn_aqo&&
502+
!query_context.collect_stat)
499503
return;
500504

501505
is_join_path= (src->type==T_NestPath||src->type==T_MergePath||
@@ -552,6 +556,11 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
552556
}
553557
else
554558
{
559+
/*
560+
* In the case of forced stat gathering AQO must store fss as well as
561+
* parallel divisor. Negative predicted cardinality field will be a sign
562+
* that it is not a prediction, just statistics.
563+
*/
555564
node->prediction=src->parent->predicted_cardinality;
556565
node->fss=src->parent->fss_hash;
557566
}
@@ -624,11 +633,6 @@ AQOnodeOut(struct StringInfoData *str, const struct ExtensibleNode *enode)
624633
{
625634
AQOPlanNode*node= (AQOPlanNode*)enode;
626635

627-
node->had_path= false;
628-
node->jointype=0;
629-
node->parallel_divisor=1.0;
630-
node->was_parametrized= false;
631-
632636
/* For Adaptive optimization DEBUG purposes */
633637
WRITE_INT_FIELD(fss);
634638
WRITE_FLOAT_FIELD(prediction,"%.0f");
@@ -676,10 +680,10 @@ AQOnodeRead(struct ExtensibleNode *enode)
676680
constchar*token;
677681
intlength;
678682

679-
READ_BOOL_FIELD(had_path);
680-
READ_ENUM_FIELD(jointype,JoinType);
681-
READ_FLOAT_FIELD(parallel_divisor);
682-
READ_BOOL_FIELD(was_parametrized);
683+
local_node->had_path= false;
684+
local_node->jointype=0;
685+
local_node->parallel_divisor=1.0;
686+
local_node->was_parametrized= false;
683687

684688
local_node->rels=palloc0(sizeof(RelSortOut));
685689
local_node->clauses=NIL;

0 commit comments

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