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

Commit7f7c2d1

Browse files
author
Oleg Ivanov
committed
Added disabled mode
1 parent8a41e28 commit7f7c2d1

File tree

5 files changed

+156
-2
lines changed

5 files changed

+156
-2
lines changed

‎contrib/aqo/aqo.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ static const struct config_enum_entry format_options[] = {
1313
{"intelligent",AQO_MODE_INTELLIGENT, false},
1414
{"forced",AQO_MODE_FORCED, false},
1515
{"manual",AQO_MODE_MANUAL, false},
16+
{"disabled",AQO_MODE_DISABLED, false},
1617
{NULL,0, false}
1718
};
1819

‎contrib/aqo/aqo.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -148,6 +148,8 @@ typedef enum
148148
AQO_MODE_FORCED,
149149
/* New query types are not linked with any feature space */
150150
AQO_MODE_MANUAL,
151+
/* Aqo is disabled for all queries */
152+
AQO_MODE_DISABLED,
151153
}AQO_MODE;
152154
externintaqo_mode;
153155

‎contrib/aqo/expected/aqo_disabled.out‎

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,109 @@ SELECT count(*) FROM tmp1;
3535
(1 row)
3636

3737
DROP TABLE tmp1;
38+
EXPLAIN SELECT * FROM aqo_test0
39+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
40+
QUERY PLAN
41+
----------------------------------------------------------------------------------
42+
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=1 width=16)
43+
Index Cond: (a < 3)
44+
Filter: ((b < 3) AND (c < 3) AND (d < 3))
45+
(3 rows)
46+
47+
EXPLAIN SELECT t1.a, t2.b, t3.c
48+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
49+
WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
50+
QUERY PLAN
51+
------------------------------------------------------------------------------------------------
52+
Nested Loop (cost=0.28..50.59 rows=1 width=12)
53+
Join Filter: (t1.b = t3.b)
54+
-> Nested Loop (cost=0.28..9.56 rows=1 width=12)
55+
-> Seq Scan on aqo_test1 t1 (cost=0.00..1.25 rows=1 width=8)
56+
Filter: (a < 1)
57+
-> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
58+
Index Cond: (a = t1.a)
59+
Filter: (c < 1)
60+
-> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
61+
Filter: ((b < 1) AND (d < 0))
62+
(10 rows)
63+
64+
CREATE EXTENSION aqo;
65+
SET aqo.mode = 'intelligent';
66+
CREATE TABLE tmp1 AS SELECT * FROM aqo_test0
67+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
68+
SELECT count(*) FROM tmp1;
69+
count
70+
-------
71+
3
72+
(1 row)
73+
74+
DROP TABLE tmp1;
75+
CREATE TABLE tmp1 AS SELECT t1.a, t2.b, t3.c
76+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
77+
WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
78+
SELECT count(*) FROM tmp1;
79+
count
80+
-------
81+
0
82+
(1 row)
83+
84+
DROP TABLE tmp1;
85+
SET aqo.mode = 'manual';
86+
UPDATE aqo_queries SET learn_aqo = true, use_aqo = true, auto_tuning = false;
87+
EXPLAIN SELECT * FROM aqo_test0
88+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
89+
QUERY PLAN
90+
----------------------------------------------------------------------------------
91+
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=3 width=16)
92+
Index Cond: (a < 3)
93+
Filter: ((b < 3) AND (c < 3) AND (d < 3))
94+
(3 rows)
95+
96+
EXPLAIN SELECT t1.a, t2.b, t3.c
97+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
98+
WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
99+
QUERY PLAN
100+
------------------------------------------------------------------------------------------------
101+
Nested Loop (cost=0.28..50.59 rows=1 width=12)
102+
Join Filter: (t1.b = t3.b)
103+
-> Nested Loop (cost=0.28..9.56 rows=1 width=12)
104+
-> Seq Scan on aqo_test1 t1 (cost=0.00..1.25 rows=1 width=8)
105+
Filter: (a < 1)
106+
-> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
107+
Index Cond: (a = t1.a)
108+
Filter: (c < 1)
109+
-> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
110+
Filter: ((b < 1) AND (d < 0))
111+
(10 rows)
112+
113+
SET aqo.mode = 'disabled';
114+
EXPLAIN SELECT * FROM aqo_test0
115+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
116+
QUERY PLAN
117+
----------------------------------------------------------------------------------
118+
Index Scan using aqo_test0_idx_a on aqo_test0 (cost=0.28..8.35 rows=1 width=16)
119+
Index Cond: (a < 3)
120+
Filter: ((b < 3) AND (c < 3) AND (d < 3))
121+
(3 rows)
122+
123+
EXPLAIN SELECT t1.a, t2.b, t3.c
124+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
125+
WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b = t3.b;
126+
QUERY PLAN
127+
------------------------------------------------------------------------------------------------
128+
Nested Loop (cost=0.28..50.59 rows=1 width=12)
129+
Join Filter: (t1.b = t3.b)
130+
-> Nested Loop (cost=0.28..9.56 rows=1 width=12)
131+
-> Seq Scan on aqo_test1 t1 (cost=0.00..1.25 rows=1 width=8)
132+
Filter: (a < 1)
133+
-> Index Scan using aqo_test0_idx_a on aqo_test0 t2 (cost=0.28..8.30 rows=1 width=8)
134+
Index Cond: (a = t1.a)
135+
Filter: (c < 1)
136+
-> Seq Scan on aqo_test0 t3 (cost=0.00..41.02 rows=1 width=8)
137+
Filter: ((b < 1) AND (d < 0))
138+
(10 rows)
139+
140+
DROP EXTENSION aqo;
38141
DROP INDEX aqo_test0_idx_a;
39142
DROP TABLE aqo_test0;
40143
DROP INDEX aqo_test1_idx_a;

‎contrib/aqo/preprocessing.c‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -87,7 +87,7 @@ call_default_planner(Query *parse,
8787
* This hook computes query_hash, and sets values of learn_aqo,
8888
* use_aqo and is_common flags for given query.
8989
* Creates an entry in aqo_queries for new type of query if it is
90-
* necessary, i. e. AQO mode isnot "manual".
90+
* necessary, i. e. AQO mode is"intelligent".
9191
*/
9292
PlannedStmt*
9393
aqo_planner(Query*parse,
@@ -105,7 +105,8 @@ aqo_planner(Query *parse,
105105
strncmp(query_text,CREATE_EXTENSION_STARTSTRING_0,
106106
strlen(CREATE_EXTENSION_STARTSTRING_0))==0||
107107
strncmp(query_text,CREATE_EXTENSION_STARTSTRING_1,
108-
strlen(CREATE_EXTENSION_STARTSTRING_1))==0)
108+
strlen(CREATE_EXTENSION_STARTSTRING_1))==0||
109+
aqo_mode==AQO_MODE_DISABLED)
109110
{
110111
disable_aqo_for_query();
111112
returncall_default_planner(parse,cursorOptions,boundParams);
@@ -149,6 +150,9 @@ aqo_planner(Query *parse,
149150
use_aqo= false;
150151
collect_stat= false;
151152
break;
153+
caseAQO_MODE_DISABLED:
154+
/* Should never happen */
155+
break;
152156
default:
153157
elog(WARNING,
154158
"unrecognized mode in AQO: %d",

‎contrib/aqo/sql/aqo_disabled.sql‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,50 @@ WHERE t1.a < 1 AND t3.b < 1 AND t2.c < 1 AND t3.d < 0 AND t1.a = t2.a AND t1.b =
2929
SELECTcount(*)FROM tmp1;
3030
DROPTABLE tmp1;
3131

32+
EXPLAINSELECT*FROM aqo_test0
33+
WHERE a<3AND b<3AND c<3AND d<3;
34+
35+
EXPLAINSELECTt1.a,t2.b,t3.c
36+
FROM aqo_test1AS t1, aqo_test0AS t2, aqo_test0AS t3
37+
WHEREt1.a<1ANDt3.b<1ANDt2.c<1ANDt3.d<0ANDt1.a=t2.aANDt1.b=t3.b;
38+
39+
CREATE EXTENSION aqo;
40+
41+
SETaqo.mode='intelligent';
42+
43+
CREATETABLEtmp1ASSELECT*FROM aqo_test0
44+
WHERE a<3AND b<3AND c<3AND d<3;
45+
SELECTcount(*)FROM tmp1;
46+
DROPTABLE tmp1;
47+
48+
CREATETABLEtmp1ASSELECTt1.a,t2.b,t3.c
49+
FROM aqo_test1AS t1, aqo_test0AS t2, aqo_test0AS t3
50+
WHEREt1.a<1ANDt3.b<1ANDt2.c<1ANDt3.d<0ANDt1.a=t2.aANDt1.b=t3.b;
51+
SELECTcount(*)FROM tmp1;
52+
DROPTABLE tmp1;
53+
54+
SETaqo.mode='manual';
55+
56+
UPDATE aqo_queriesSET learn_aqo= true, use_aqo= true, auto_tuning= false;
57+
58+
EXPLAINSELECT*FROM aqo_test0
59+
WHERE a<3AND b<3AND c<3AND d<3;
60+
61+
EXPLAINSELECTt1.a,t2.b,t3.c
62+
FROM aqo_test1AS t1, aqo_test0AS t2, aqo_test0AS t3
63+
WHEREt1.a<1ANDt3.b<1ANDt2.c<1ANDt3.d<0ANDt1.a=t2.aANDt1.b=t3.b;
64+
65+
SETaqo.mode='disabled';
66+
67+
EXPLAINSELECT*FROM aqo_test0
68+
WHERE a<3AND b<3AND c<3AND d<3;
69+
70+
EXPLAINSELECTt1.a,t2.b,t3.c
71+
FROM aqo_test1AS t1, aqo_test0AS t2, aqo_test0AS t3
72+
WHEREt1.a<1ANDt3.b<1ANDt2.c<1ANDt3.d<0ANDt1.a=t2.aANDt1.b=t3.b;
73+
74+
DROP EXTENSION aqo;
75+
3276
DROPINDEX aqo_test0_idx_a;
3377
DROPTABLE aqo_test0;
3478

0 commit comments

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