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

Commit2db2a3c

Browse files
committed
Add basic regression test on FDW support.
Switch from AQO_EXPLAIN define to the two user GUCS: aqo.show_detailsand aqo.show_hash.New ExplainOneNode hook allows to add info into explain of each node.The changes concern the core and the extension.
1 parent8683934 commit2db2a3c

File tree

8 files changed

+256
-82
lines changed

8 files changed

+256
-82
lines changed

‎Makefile‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,11 +14,13 @@ REGRESS =aqo_disabled \
1414
aqo_forced\
1515
aqo_learn\
1616
schema\
17+
aqo_fdw\
1718
aqo_CVE-2020-14350
1819

1920
fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw
2021
PG_CPPFLAGS += -I$(libpq_srcdir) -I$(fdw_srcdir)
2122
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
23+
EXTRA_INSTALL = contrib/postgres_fdw
2224

2325
DATA = aqo--1.0.sql aqo--1.0--1.1.sql aqo--1.1--1.2.sql aqo--1.2.sql
2426

‎aqo.c‎

Lines changed: 32 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,8 @@ void _PG_init(void);
1818
/* Strategy of determining feature space for new queries. */
1919
intaqo_mode;
2020
boolforce_collect_stat;
21+
boolaqo_show_hash;
22+
boolaqo_details;
2123

2224
/* GUC variables */
2325
staticconststructconfig_enum_entryformat_options[]= {
@@ -83,6 +85,7 @@ set_joinrel_size_estimates_hook_typeprev_set_joinrel_size_estimates_hook;
8385
get_parameterized_joinrel_size_hook_typeprev_get_parameterized_joinrel_size_hook;
8486
copy_generic_path_info_hook_typeprev_copy_generic_path_info_hook;
8587
ExplainOnePlan_hook_typeprev_ExplainOnePlan_hook;
88+
ExplainOneNode_hook_typeprev_ExplainOneNode_hook;
8689

8790
/*****************************************************************************
8891
*
@@ -116,7 +119,33 @@ _PG_init(void)
116119
NULL,
117120
NULL,
118121
NULL
119-
);
122+
);
123+
124+
DefineCustomBoolVariable(
125+
"aqo.show_hash",
126+
"Show query and node hash on explain.",
127+
"Hash value depend on each instance and is not good to enable it in regression or TAP tests.",
128+
&aqo_show_hash,
129+
false,
130+
PGC_USERSET,
131+
0,
132+
NULL,
133+
NULL,
134+
NULL
135+
);
136+
137+
DefineCustomBoolVariable(
138+
"aqo.show_details",
139+
"Show AQO state on a query.",
140+
NULL,
141+
&aqo_details,
142+
false,
143+
PGC_USERSET,
144+
0,
145+
NULL,
146+
NULL,
147+
NULL
148+
);
120149

121150
prev_planner_hook=planner_hook;
122151
planner_hook=aqo_planner;
@@ -139,6 +168,8 @@ _PG_init(void)
139168
copy_generic_path_info_hook=aqo_copy_generic_path_info;
140169
prev_ExplainOnePlan_hook=ExplainOnePlan_hook;
141170
ExplainOnePlan_hook=print_into_explain;
171+
prev_ExplainOneNode_hook=ExplainOneNode_hook;
172+
ExplainOneNode_hook=print_node_explain;
142173
parampathinfo_postinit_hook=ppi_hook;
143174

144175
init_deactivated_queries_storage();

‎aqo.h‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -174,6 +174,8 @@ typedef enum
174174

175175
externintaqo_mode;
176176
externboolforce_collect_stat;
177+
externboolaqo_show_hash;
178+
externboolaqo_details;
177179

178180
/*
179181
* It is mostly needed for auto tuning of query. with auto tuning mode aqo
@@ -311,6 +313,10 @@ void print_into_explain(PlannedStmt *plannedstmt, IntoClause *into,
311313
ExplainState*es,constchar*queryString,
312314
ParamListInfoparams,constinstr_time*planduration,
313315
QueryEnvironment*queryEnv);
316+
externvoidprint_node_explain(ExplainState*es,
317+
PlanState*ps,
318+
Plan*plan,
319+
doublerows);
314320
voiddisable_aqo_for_query(void);
315321

316322
/* Cardinality estimation hooks */

‎aqo_pg12.patch‎

Lines changed: 18 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ index 92184ed487..ac8abbfd03 100644
1111
auto_explain\
1212
bloom\
1313
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
14-
index a40bc14ec5..28ec281a70 100644
14+
index a40bc14ec5..d157c3043e 100644
1515
--- a/src/backend/commands/explain.c
1616
+++ b/src/backend/commands/explain.c
1717
@@ -24,6 +24,7 @@
@@ -22,17 +22,20 @@ index a40bc14ec5..28ec281a70 100644
2222
#include "parser/parsetree.h"
2323
#include "rewrite/rewriteHandler.h"
2424
#include "storage/bufmgr.h"
25-
@@ -46,6 +47,9 @@ ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL;
25+
@@ -46,6 +47,12 @@ ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL;
2626
/* Hook for plugins to get control in explain_get_index_name() */
2727
explain_get_index_name_hook_type explain_get_index_name_hook = NULL;
2828

2929
+/* Hook for plugins to get control in ExplainOnePlan() */
3030
+ExplainOnePlan_hook_type ExplainOnePlan_hook = NULL;
31+
+
32+
+/* Hook for plugins to get control in ExplainOnePlan() */
33+
+ExplainOneNode_hook_type ExplainOneNode_hook = NULL;
3134
+
3235

3336
/* OR-able flags for ExplainXMLTag() */
3437
#define X_OPENING 0
35-
@@ -597,6 +601,10 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
38+
@@ -597,6 +604,10 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
3639
ExplainPropertyFloat("Execution Time", "ms", 1000.0 * totaltime, 3,
3740
es);
3841

@@ -43,42 +46,13 @@ index a40bc14ec5..28ec281a70 100644
4346
ExplainCloseGroup("Query", NULL, true, es);
4447
}
4548

46-
@@ -1521,6 +1529,38 @@ ExplainNode(PlanState *planstate, List *ancestors,
49+
@@ -1521,6 +1532,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
4750
appendStringInfo(es->str,
4851
" (actual rows=%.0f loops=%.0f)",
4952
rows, nloops);
5053
+
51-
+#ifdef AQO_EXPLAIN
52-
+if (es->verbose && plan && planstate->instrument)
53-
+{
54-
+int wrkrs = 1;
55-
+double error = -1.;
56-
+
57-
+if (planstate->worker_instrument && IsParallelTuplesProcessing(plan))
58-
+{
59-
+int i;
60-
+for (i = 0; i < planstate->worker_instrument->num_workers; i++)
61-
+{
62-
+Instrumentation *instrument = &planstate->worker_instrument->instrument[i];
63-
+if (instrument->nloops <= 0)
64-
+continue;
65-
+wrkrs++;
66-
+}
67-
+}
68-
+
69-
+if (plan->predicted_cardinality > 0.)
70-
+{
71-
+error = 100. * (plan->predicted_cardinality - (rows*wrkrs))
72-
+/ plan->predicted_cardinality;
73-
+appendStringInfo(es->str,
74-
+" (AQO: cardinality=%.0lf, error=%.0lf%%, fsspace_hash=%d)",
75-
+plan->predicted_cardinality, error, plan->fss_hash);
76-
+}
77-
+else
78-
+appendStringInfo(es->str, " (AQO not used, fsspace_hash=%d)",
79-
+plan->fss_hash);
80-
+}
81-
+#endif
54+
+if (ExplainOneNode_hook)
55+
+ExplainOneNode_hook(es, planstate, plan, rows);
8256
}
8357
else
8458
{
@@ -909,10 +883,10 @@ index 7758a49fa4..4f221a000b 100644
909883

910884
return ppi;
911885
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
912-
index f8b79ec120..b5eda01907 100644
886+
index f8b79ec120..f970971a4a 100644
913887
--- a/src/include/commands/explain.h
914888
+++ b/src/include/commands/explain.h
915-
@@ -62,6 +62,12 @@ extern PGDLLIMPORT ExplainOneQuery_hook_type ExplainOneQuery_hook;
889+
@@ -62,6 +62,19 @@ extern PGDLLIMPORT ExplainOneQuery_hook_type ExplainOneQuery_hook;
916890
typedef const char *(*explain_get_index_name_hook_type) (Oid indexId);
917891
extern PGDLLIMPORT explain_get_index_name_hook_type explain_get_index_name_hook;
918892

@@ -922,6 +896,13 @@ index f8b79ec120..b5eda01907 100644
922896
+ ParamListInfo params, const instr_time *planduration,
923897
+ QueryEnvironment *queryEnv);
924898
+extern PGDLLIMPORT ExplainOnePlan_hook_type ExplainOnePlan_hook;
899+
+
900+
+/* Explain a plan node info */
901+
+typedef void (*ExplainOneNode_hook_type) (ExplainState *es,
902+
+ PlanState *ps,
903+
+ Plan *plan,
904+
+ double rows);
905+
+extern PGDLLIMPORT ExplainOneNode_hook_type ExplainOneNode_hook;
925906

926907
extern void ExplainQuery(ParseState *pstate, ExplainStmt *stmt, const char *queryString,
927908
ParamListInfo params, QueryEnvironment *queryEnv, DestReceiver *dest);

‎conf.add‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1 +1,3 @@
1-
shared_preload_libraries = 'aqo'
1+
autovacuum = off
2+
shared_preload_libraries = 'postgres_fdw, aqo'
3+
max_parallel_workers = 0 # switch off parallel workers because of unsteadiness

‎expected/aqo_fdw.out‎

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,74 @@
1+
-- Tests on cardinality estimation of FDW-queries:
2+
-- simple ForeignScan.
3+
-- JOIN push-down (check push of baserestrictinfo and joininfo)
4+
-- Aggregate push-down
5+
-- Push-down of groupings with HAVING clause.
6+
CREATE EXTENSION aqo;
7+
CREATE EXTENSION postgres_fdw;
8+
SET aqo.mode = 'learn';
9+
SET aqo.show_details = 'true'; -- show AQO info for each node and entire query.
10+
SET aqo.show_hash = 'false'; -- a hash value is system-depended. Ignore it.
11+
DO $d$
12+
BEGIN
13+
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
14+
OPTIONS (dbname '$$||current_database()||$$',
15+
port '$$||current_setting('port')||$$'
16+
)$$;
17+
END;
18+
$d$;
19+
CREATE USER MAPPING FOR PUBLIC SERVER loopback;
20+
CREATE TABLE local (x int);
21+
CREATE FOREIGN TABLE frgn(x int) SERVER loopback OPTIONS (table_name 'local');
22+
INSERT INTO frgn (x) VALUES (1);
23+
ANALYZE local;
24+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn;
25+
QUERY PLAN
26+
-------------------------------------------------------------
27+
Foreign Scan on frgn (actual rows=1 loops=1) (AQO not used)
28+
Using aqo: true
29+
AQO mode: LEARN
30+
JOINS: 0
31+
(4 rows)
32+
33+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn;
34+
QUERY PLAN
35+
-----------------------------------------------------------------------------
36+
Foreign Scan on frgn (actual rows=1 loops=1) (AQO: cardinality=1, error=0%)
37+
Using aqo: true
38+
AQO mode: LEARN
39+
JOINS: 0
40+
(4 rows)
41+
42+
-- Push down base filters.
43+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE) SELECT x FROM frgn WHERE x < 10;
44+
QUERY PLAN
45+
--------------------------------------------------------------------
46+
Foreign Scan on public.frgn (actual rows=1 loops=1) (AQO not used)
47+
Output: x
48+
Remote SQL: SELECT x FROM public.local WHERE ((x < 10))
49+
Using aqo: true
50+
AQO mode: LEARN
51+
JOINS: 0
52+
(6 rows)
53+
54+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE) SELECT x FROM frgn WHERE x < 10;
55+
QUERY PLAN
56+
------------------------------------------------------------------------------------
57+
Foreign Scan on public.frgn (actual rows=1 loops=1) (AQO: cardinality=1, error=0%)
58+
Output: x
59+
Remote SQL: SELECT x FROM public.local WHERE ((x < 10))
60+
Using aqo: true
61+
AQO mode: LEARN
62+
JOINS: 0
63+
(6 rows)
64+
65+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
66+
QUERY PLAN
67+
-------------------------------------------------------------------------------
68+
Foreign Scan on frgn (actual rows=0 loops=1) (AQO: cardinality=1, error=100%)
69+
Using aqo: true
70+
AQO mode: LEARN
71+
JOINS: 0
72+
(4 rows)
73+
74+
DROP EXTENSION aqo;

0 commit comments

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