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

Commit5654c20

Browse files
committed
[PGPRO-7183] bring in line stable 13, 14, 15
Cherry-pick commit:9595a94Fix the bug with serialization machinery.
1 parent1ff3190 commit5654c20

File tree

7 files changed

+234
-44
lines changed

7 files changed

+234
-44
lines changed

‎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
@@ -349,7 +349,6 @@ SELECT count(*) FROM
349349
Filter: (x <> t_1.x)
350350
Rows Removed by Filter: 50
351351
-> Hash (actual rows=851 loops=1)
352-
AQO not used
353352
-> Seq Scan on t (actual rows=851 loops=1)
354353
AQO: rows=851, error=0%
355354
Filter: (((x % 3))::numeric < (SubPlan 1))
@@ -364,7 +363,7 @@ SELECT count(*) FROM
364363
Using aqo: true
365364
AQO mode: LEARN
366365
JOINS: 1
367-
(31 rows)
366+
(30 rows)
368367

369368
-- Two identical subplans in a clause
370369
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
@@ -527,12 +526,11 @@ WHERE str NOT LIKE '%Heap Blocks%';
527526
Filter: (t.x < 3)
528527
Rows Removed by Filter: 300
529528
-> Bitmap Index Scan on ind2 (actual rows=350 loops=1)
530-
AQO not used
531529
Index Cond: (mod(t.x, 3) = 1)
532530
Using aqo: true
533531
AQO mode: LEARN
534532
JOINS: 0
535-
(14 rows)
533+
(13 rows)
536534

537535
-- Best choice is ...
538536
ANALYZE t;
@@ -561,7 +559,7 @@ ORDER BY (md5(query_text),error) DESC;
561559
-------+------------------------------------------------------------------------------------------------
562560
0.768 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,y)) AS q1;
563561
0.070 | SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
564-
1.416 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
562+
0.000 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
565563
0.000 | SELECT * FROM +
566564
| (SELECT * FROM t WHERE x < 0) AS t0 +
567565
| JOIN +

‎path_utils.c‎

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

6868
/*
6969
* Extract an AQO node from the plan private field.
70-
* If no one node was found, return pointer to the default value or allocate new
71-
* node (with default value) according to 'create' field.
72-
* Can't return NULL value at all.
70+
* If no one node was found, return pointer to the default value or return NULL.
7371
*/
7472
AQOPlanNode*
7573
get_aqo_plan_node(Plan*plan,boolcreate)
@@ -94,7 +92,7 @@ get_aqo_plan_node(Plan *plan, bool create)
9492
if (node==NULL)
9593
{
9694
if (!create)
97-
return&DefaultAQOPlanNode;
95+
returnNULL;
9896

9997
node=create_aqo_plan_node();
10098
plan->ext_nodes=lappend(plan->ext_nodes,node);
@@ -497,9 +495,14 @@ is_appropriate_path(Path *path)
497495
}
498496

499497
/*
500-
* Converts path info into plan node for collecting it after query execution.
498+
* Add AQO data into the plan node, if necessary.
499+
*
500+
* The necesssary case is when AQO is learning on this query, used for a
501+
* prediction (and we will need the data to show prediction error at the end) or
502+
* just to gather a plan statistics.
501503
* Don't switch here to any AQO-specific memory contexts, because we should
502-
* store AQO prediction in the same context, as the plan.
504+
* store AQO prediction in the same context, as the plan. So, explicitly free
505+
* all unneeded data.
503506
*/
504507
void
505508
aqo_create_plan_hook(PlannerInfo*root,Path*src,Plan**dest)
@@ -511,7 +514,8 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
511514
if (prev_create_plan_hook)
512515
prev_create_plan_hook(root,src,dest);
513516

514-
if (!query_context.use_aqo&& !query_context.learn_aqo)
517+
if (!query_context.use_aqo&& !query_context.learn_aqo&&
518+
!query_context.collect_stat)
515519
return;
516520

517521
is_join_path= (src->type==T_NestPath||src->type==T_MergePath||
@@ -568,6 +572,11 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
568572
}
569573
else
570574
{
575+
/*
576+
* In the case of forced stat gathering AQO must store fss as well as
577+
* parallel divisor. Negative predicted cardinality field will be a sign
578+
* that it is not a prediction, just statistics.
579+
*/
571580
node->prediction=src->parent->predicted_cardinality;
572581
node->fss=src->parent->fss_hash;
573582
}
@@ -640,11 +649,6 @@ AQOnodeOut(struct StringInfoData *str, const struct ExtensibleNode *enode)
640649
{
641650
AQOPlanNode*node= (AQOPlanNode*)enode;
642651

643-
node->had_path= false;
644-
node->jointype=0;
645-
node->parallel_divisor=1.0;
646-
node->was_parametrized= false;
647-
648652
/* For Adaptive optimization DEBUG purposes */
649653
WRITE_INT_FIELD(fss);
650654
WRITE_FLOAT_FIELD(prediction,"%.0f");
@@ -692,10 +696,10 @@ AQOnodeRead(struct ExtensibleNode *enode)
692696
constchar*token;
693697
intlength;
694698

695-
READ_BOOL_FIELD(had_path);
696-
READ_ENUM_FIELD(jointype,JoinType);
697-
READ_FLOAT_FIELD(parallel_divisor);
698-
READ_BOOL_FIELD(was_parametrized);
699+
local_node->had_path= false;
700+
local_node->jointype=0;
701+
local_node->parallel_divisor=1.0;
702+
local_node->was_parametrized= false;
699703

700704
local_node->rels=palloc0(sizeof(RelSortOut));
701705
local_node->clauses=NIL;

0 commit comments

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