Skip to content

Commit 8965c10

Browse files
vbwagnervbwagner
committed
Merge branch 'PGPROEE9_6_aqo' into PGPROEE9_6
2 parents 8427f74 + c9ea116 commit 8965c10

File tree

12 files changed

+659
-27
lines changed

12 files changed

+659
-27
lines changed

‎contrib/aqo/aqo.c

Lines changed: 6 additions & 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

@@ -42,6 +43,7 @@ bool auto_tuning;
4243
bool collect_stat;
4344
bool adding_query;
4445
bool explain_only;
46+
bool explain_aqo;
4547

4648
/* Query execution time */
4749
instr_time query_starttime;
@@ -57,6 +59,7 @@ get_parameterized_baserel_size_hook_type prev_get_parameterized_baserel_size_hoo
5759
set_joinrel_size_estimates_hook_type prev_set_joinrel_size_estimates_hook;
5860
get_parameterized_joinrel_size_hook_type prev_get_parameterized_joinrel_size_hook;
5961
copy_generic_path_info_hook_type prev_copy_generic_path_info_hook;
62+
ExplainOnePlan_hook_type prev_ExplainOnePlan_hook;
6063

6164
/*****************************************************************************
6265
*
@@ -101,6 +104,8 @@ _PG_init(void)
101104
&aqo_get_parameterized_joinrel_size;
102105
prev_copy_generic_path_info_hook = copy_generic_path_info_hook;
103106
copy_generic_path_info_hook = &aqo_copy_generic_path_info;
107+
prev_ExplainOnePlan_hook = ExplainOnePlan_hook;
108+
ExplainOnePlan_hook = print_into_explain;
104109
init_deactivated_queries_storage();
105110
}
106111

@@ -118,6 +123,7 @@ _PG_fini(void)
118123
get_parameterized_joinrel_size_hook =
119124
prev_get_parameterized_joinrel_size_hook;
120125
copy_generic_path_info_hook = prev_copy_generic_path_info_hook;
126+
ExplainOnePlan_hook = prev_ExplainOnePlan_hook;
121127
fini_deactivated_queries_storage();
122128
}
123129

‎contrib/aqo/aqo.h

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,7 @@
121121
#include "catalog/indexing.h"
122122
#include "catalog/pg_type.h"
123123
#include "catalog/pg_operator.h"
124+
#include "commands/explain.h"
124125
#include "executor/executor.h"
125126
#include "executor/execdesc.h"
126127
#include "nodes/makefuncs.h"
@@ -148,6 +149,8 @@ typedef enum
148149
AQO_MODE_FORCED,
149150
/* New query types are not linked with any feature space */
150151
AQO_MODE_MANUAL,
152+
/* Aqo is disabled for all queries */
153+
AQO_MODE_DISABLED,
151154
} AQO_MODE;
152155
extern int aqo_mode;
153156

@@ -193,6 +196,7 @@ extern bool auto_tuning;
193196
extern bool collect_stat;
194197
extern bool adding_query;
195198
extern bool explain_only;
199+
extern bool explain_aqo;
196200

197201
/* Query execution time */
198202
extern instr_time query_starttime;
@@ -213,6 +217,7 @@ extern get_parameterized_joinrel_size_hook_type
213217
prev_get_parameterized_joinrel_size_hook;
214218
extern copy_generic_path_info_hook_type
215219
prev_copy_generic_path_info_hook;
220+
extern ExplainOnePlan_hook_type prev_ExplainOnePlan_hook;
216221

217222

218223
/* Hash functions */
@@ -252,6 +257,9 @@ PlannedStmt *call_default_planner(Query *parse,
252257
PlannedStmt *aqo_planner(Query *parse,
253258
int cursorOptions,
254259
ParamListInfo boundParams);
260+
void print_into_explain(PlannedStmt *plannedstmt, IntoClause *into,
261+
ExplainState *es, const char *queryString,
262+
ParamListInfo params, const instr_time *planduration);
255263
void disable_aqo_for_query(void);
256264

257265
/* Cardinality estimation hooks */

‎contrib/aqo/expected/aqo_disabled.out

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ AS (
1616
) INSERT INTO aqo_test1 (SELECT * FROM t);
1717
CREATE INDEX aqo_test1_idx_a ON aqo_test1 (a);
1818
ANALYZE aqo_test1;
19+
SET aqo.mode = 'disabled';
1920
CREATE TABLE tmp1 AS SELECT * FROM aqo_test0
2021
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
2122
SELECT count(*) FROM tmp1;
@@ -35,6 +36,111 @@ SELECT count(*) FROM tmp1;
3536
(1 row)
3637

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

‎contrib/aqo/expected/aqo_forced.out

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -65,7 +65,8 @@ WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
6565
Index Scan using aqo_test0_idx_a on aqo_test0
6666
Index Cond: (a < 3)
6767
Filter: ((b < 3) AND (c < 3) AND (d < 3))
68-
(3 rows)
68+
Using aqo: true
69+
(4 rows)
6970

7071
EXPLAIN (COSTS FALSE)
7172
SELECT * FROM aqo_test0
@@ -75,7 +76,8 @@ WHERE a < 5 AND b < 5 AND c < 5 AND d < 5;
7576
Index Scan using aqo_test0_idx_a on aqo_test0
7677
Index Cond: (a < 5)
7778
Filter: ((b < 5) AND (c < 5) AND (d < 5))
78-
(3 rows)
79+
Using aqo: true
80+
(4 rows)
7981

8082
DROP INDEX aqo_test0_idx_a;
8183
DROP TABLE aqo_test0;

0 commit comments

Comments
 (0)