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

Commit8965c10

Browse files
committed
Merge branch 'PGPROEE9_6_aqo' into PGPROEE9_6
2 parents8427f74 +c9ea116 commit8965c10

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 @@ boolauto_tuning;
4243
boolcollect_stat;
4344
booladding_query;
4445
boolexplain_only;
46+
boolexplain_aqo;
4547

4648
/* Query execution time */
4749
instr_timequery_starttime;
@@ -57,6 +59,7 @@ get_parameterized_baserel_size_hook_type prev_get_parameterized_baserel_size_hoo
5759
set_joinrel_size_estimates_hook_typeprev_set_joinrel_size_estimates_hook;
5860
get_parameterized_joinrel_size_hook_typeprev_get_parameterized_joinrel_size_hook;
5961
copy_generic_path_info_hook_typeprev_copy_generic_path_info_hook;
62+
ExplainOnePlan_hook_typeprev_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
externintaqo_mode;
153156

@@ -193,6 +196,7 @@ extern bool auto_tuning;
193196
externboolcollect_stat;
194197
externbooladding_query;
195198
externboolexplain_only;
199+
externboolexplain_aqo;
196200

197201
/* Query execution time */
198202
externinstr_timequery_starttime;
@@ -213,6 +217,7 @@ externget_parameterized_joinrel_size_hook_type
213217
prev_get_parameterized_joinrel_size_hook;
214218
externcopy_generic_path_info_hook_type
215219
prev_copy_generic_path_info_hook;
220+
externExplainOnePlan_hook_typeprev_ExplainOnePlan_hook;
216221

217222

218223
/* Hash functions */
@@ -252,6 +257,9 @@ PlannedStmt *call_default_planner(Query *parse,
252257
PlannedStmt*aqo_planner(Query*parse,
253258
intcursorOptions,
254259
ParamListInfoboundParams);
260+
voidprint_into_explain(PlannedStmt*plannedstmt,IntoClause*into,
261+
ExplainState*es,constchar*queryString,
262+
ParamListInfoparams,constinstr_time*planduration);
255263
voiddisable_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)
[8]ページ先頭
©2009-2025 Movatter.jp