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

Commitcd346ff

Browse files
danolivoAlena Rybakina
authored and
Alena Rybakina
committed
Disable materializing strategy of the planner in look-a-like tests.
It is just because of difference in behaviour of different versions of PGProexecutor. In some versions it can disable unnecessary repeatable scans of amaterialize node.XXX: Could we solve a problem by improvement of AQO logic?
1 parent9a35dd2 commitcd346ff

File tree

2 files changed

+58
-50
lines changed

2 files changed

+58
-50
lines changed

‎expected/look_a_like.out‎

Lines changed: 39 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@ CREATE EXTENSION aqo;
22
SET aqo.join_threshold = 0;
33
SET aqo.mode = 'learn';
44
SET aqo.show_details = 'on';
5+
SET enable_material = 'off';
56
DROP TABLE IF EXISTS a,b CASCADE;
67
NOTICE: table "a" does not exist, skipping
78
NOTICE: table "b" does not exist, skipping
@@ -38,65 +39,60 @@ WHERE str NOT LIKE 'Query Identifier%';
3839
JOINS: 0
3940
(8 rows)
4041

41-
-- cardinality 100 in the first Seq Scan on a
4242
SELECT str AS result
4343
FROM expln('
4444
SELECT x FROM A,B WHERE x = 5 AND A.x = B.y;') AS str
45-
WHERE str NOT LIKE 'Query Identifier%';
46-
result
47-
------------------------------------------------------------
45+
WHERE str NOT LIKE 'Query Identifier%'
46+
; -- Find cardinality for SCAN A(x=5) from a neighbour class, created by the
47+
result
48+
--------------------------------------------------------
4849
Nested Loop (actual rows=10000 loops=1)
4950
AQO not used
5051
Output: a.x
51-
-> Seq Scan on public.a (actual rows=100 loops=1)
52+
-> Seq Scan on public.b (actual rows=100 loops=1)
53+
AQO not used
54+
Output: b.y
55+
Filter: (b.y = 5)
56+
Rows Removed by Filter: 900
57+
-> Seq Scan on public.a (actual rows=100 loops=100)
5258
AQO: rows=100, error=0%
5359
Output: a.x
5460
Filter: (a.x = 5)
5561
Rows Removed by Filter: 900
56-
-> Materialize (actual rows=100 loops=100)
57-
AQO not used
58-
Output: b.y
59-
-> Seq Scan on public.b (actual rows=100 loops=1)
60-
AQO not used
61-
Output: b.y
62-
Filter: (b.y = 5)
63-
Rows Removed by Filter: 900
6462
Using aqo: true
6563
AQO mode: LEARN
6664
JOINS: 0
67-
(19 rows)
65+
(16 rows)
6866

69-
--cardinality 100 in Nesteed Loop in the first Seq Scan on a
67+
--query, executed above.
7068
SELECT str AS result
7169
FROM expln('
7270
SELECT x, sum(x) FROM A,B WHERE y = 5 AND A.x = B.y group by(x);') AS str
73-
WHERE str NOT LIKE 'Query Identifier%';
74-
result
75-
------------------------------------------------------------------
71+
WHERE str NOT LIKE 'Query Identifier%'
72+
; -- Find the JOIN cardinality from a neighbour class.
73+
result
74+
--------------------------------------------------------------
7675
GroupAggregate (actual rows=1 loops=1)
7776
AQO not used
7877
Output: a.x, sum(a.x)
7978
Group Key: a.x
8079
-> Nested Loop (actual rows=10000 loops=1)
81-
AQO not used
80+
AQO: rows=10000, error=0%
8281
Output: a.x
8382
-> Seq Scan on public.a (actual rows=100 loops=1)
8483
AQO: rows=100, error=0%
8584
Output: a.x
8685
Filter: (a.x = 5)
8786
Rows Removed by Filter: 900
88-
->Materialize (actual rows=100 loops=100)
87+
->Seq Scan on public.b (actual rows=100 loops=100)
8988
AQO: rows=100, error=0%
9089
Output: b.y
91-
-> Seq Scan on public.b (actual rows=100 loops=1)
92-
AQO: rows=100, error=0%
93-
Output: b.y
94-
Filter: (b.y = 5)
95-
Rows Removed by Filter: 900
90+
Filter: (b.y = 5)
91+
Rows Removed by Filter: 900
9692
Using aqo: true
9793
AQO mode: LEARN
9894
JOINS: 1
99-
(23 rows)
95+
(20 rows)
10096

10197
-- cardinality 100 in the first Seq Scan on a
10298
SELECT str AS result
@@ -176,37 +172,38 @@ SELECT str AS result
176172
FROM expln('
177173
SELECT x FROM A,B where x < 10 and y > 10 group by(x);') AS str
178174
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
179-
result
180-
----------------------------------------------------------------
175+
result
176+
----------------------------------------------------------
181177
HashAggregate (actual rows=0 loops=1)
182178
AQO not used
183179
Output: a.x
184180
Group Key: a.x
185181
-> Nested Loop (actual rows=0 loops=1)
186182
AQO not used
187183
Output: a.x
188-
-> Seq Scan on public.a (actual rows=1000 loops=1)
189-
AQO: rows=1000, error=0%
184+
-> Seq Scan on public.b (actual rows=0 loops=1)
185+
AQO not used
186+
Output: b.y
187+
Filter: (b.y > 10)
188+
Rows Removed by Filter: 1000
189+
-> Seq Scan on public.a (never executed)
190+
AQO: rows=1000
190191
Output: a.x
191192
Filter: (a.x < 10)
192-
-> Materialize (actual rows=0 loops=1000)
193-
AQO not used
194-
-> Seq Scan on public.b (actual rows=0 loops=1)
195-
AQO not used
196-
Filter: (b.y > 10)
197-
Rows Removed by Filter: 1000
198193
Using aqo: true
199194
AQO mode: LEARN
200195
JOINS: 1
201-
(20 rows)
196+
(19 rows)
202197

203-
-- cardinality 1000 Hash Cond: (a.x = b.y) and 1 Seq Scan on b
204-
-- this cardinality is wrong because we take it from bad neibours (previous query).
205-
-- clause y > 10 give count of rows with the same clauses.
198+
--
199+
-- TODO:
200+
-- Not executed case. What could we do better here?
201+
--
206202
SELECT str AS result
207203
FROM expln('
208204
SELECT x,y FROM A,B WHERE x < 10 and y > 10 AND A.x = B.y;') AS str
209-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
205+
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%'
206+
;
210207
result
211208
----------------------------------------------------------
212209
Hash Join (actual rows=0 loops=1)
@@ -230,6 +227,7 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
230227
JOINS: 0
231228
(19 rows)
232229

230+
RESET enable_material;
233231
DROP TABLE a,b CASCADE;
234232
SELECT true FROM aqo_reset();
235233
?column?

‎sql/look_a_like.sql‎

Lines changed: 19 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,8 @@ SET aqo.join_threshold = 0;
33
SETaqo.mode='learn';
44
SETaqo.show_details='on';
55

6+
SET enable_material='off';
7+
68
DROPTABLE IF EXISTS a,b CASCADE;
79
CREATETABLEa (xint);
810
INSERT INTO a (x)SELECT mod(ival,10)FROM generate_series(1,1000)As ival;
@@ -28,16 +30,20 @@ SELECT str AS result
2830
FROM expln('
2931
SELECT x FROM A where x = 5;')AS str
3032
WHERE str NOTLIKE'Query Identifier%';
31-
-- cardinality 100 in the first Seq Scan on a
33+
3234
SELECT strAS result
3335
FROM expln('
3436
SELECT x FROM A,B WHERE x = 5 AND A.x = B.y;')AS str
35-
WHERE str NOTLIKE'Query Identifier%';
36-
-- cardinality 100 in Nesteed Loop in the first Seq Scan on a
37+
WHERE str NOTLIKE'Query Identifier%'
38+
;-- Find cardinality for SCAN A(x=5) from a neighbour class, created by the
39+
-- query, executed above.
40+
3741
SELECT strAS result
3842
FROM expln('
3943
SELECT x, sum(x) FROM A,B WHERE y = 5 AND A.x = B.y group by(x);')AS str
40-
WHERE str NOTLIKE'Query Identifier%';
44+
WHERE str NOTLIKE'Query Identifier%'
45+
;-- Find the JOIN cardinality from a neighbour class.
46+
4147
-- cardinality 100 in the first Seq Scan on a
4248
SELECT strAS result
4349
FROM expln('
@@ -61,14 +67,18 @@ SELECT str AS result
6167
FROM expln('
6268
SELECT x FROM A,B where x < 10 and y > 10 group by(x);')AS str
6369
WHERE str NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%';
64-
-- cardinality 1000 Hash Cond: (a.x = b.y) and 1 Seq Scan on b
65-
-- this cardinality is wrong because we take it from bad neibours (previous query).
66-
-- clause y > 10 give count of rows with the same clauses.
70+
71+
--
72+
-- TODO:
73+
-- Not executed case. What could we do better here?
74+
--
6775
SELECT strAS result
6876
FROM expln('
6977
SELECT x,y FROM A,B WHERE x < 10 and y > 10 AND A.x = B.y;')AS str
70-
WHERE str NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%';
78+
WHERE str NOTLIKE'Query Identifier%'and str NOTLIKE'%Memory%'
79+
;
7180

81+
RESET enable_material;
7282
DROPTABLE a,b CASCADE;
7383
SELECT trueFROM aqo_reset();
74-
DROP EXTENSION aqo CASCADE;
84+
DROP EXTENSION aqo CASCADE;

0 commit comments

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