Skip to content

Commit 7f7c2d1

Browse files
author
Oleg Ivanov
committed
Added disabled mode
1 parent 8a41e28 commit 7f7c2d1

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
extern int aqo_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 is not "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
return call_default_planner(parse, cursorOptions, boundParams);
@@ -149,6 +150,9 @@ aqo_planner(Query *parse,
149150
use_aqo = false;
150151
collect_stat = false;
151152
break;
153+
case AQO_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
SELECT count(*) FROM tmp1;
3030
DROP TABLE tmp1;
3131

32+
EXPLAIN SELECT * FROM aqo_test0
33+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
34+
35+
EXPLAIN SELECT t1.a, t2.b, t3.c
36+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
37+
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;
38+
39+
CREATE EXTENSION aqo;
40+
41+
SET aqo.mode = 'intelligent';
42+
43+
CREATE TABLE tmp1 AS SELECT * FROM aqo_test0
44+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
45+
SELECT count(*) FROM tmp1;
46+
DROP TABLE tmp1;
47+
48+
CREATE TABLE tmp1 AS SELECT t1.a, t2.b, t3.c
49+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
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 = t3.b;
51+
SELECT count(*) FROM tmp1;
52+
DROP TABLE tmp1;
53+
54+
SET aqo.mode = 'manual';
55+
56+
UPDATE aqo_queries SET learn_aqo = true, use_aqo = true, auto_tuning = false;
57+
58+
EXPLAIN SELECT * FROM aqo_test0
59+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
60+
61+
EXPLAIN SELECT t1.a, t2.b, t3.c
62+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
63+
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;
64+
65+
SET aqo.mode = 'disabled';
66+
67+
EXPLAIN SELECT * FROM aqo_test0
68+
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
69+
70+
EXPLAIN SELECT t1.a, t2.b, t3.c
71+
FROM aqo_test1 AS t1, aqo_test0 AS t2, aqo_test0 AS t3
72+
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;
73+
74+
DROP EXTENSION aqo;
75+
3276
DROP INDEX aqo_test0_idx_a;
3377
DROP TABLE aqo_test0;
3478

0 commit comments

Comments
 (0)