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

Commitdcf7c0e

Browse files
pyhalovdanolivo
authored andcommitted
[PGPRO-7183] bring in line stable 13, 14, 15
Cherry-pick commit:58ea474Extract info from a Foreign Join plan node.
1 parent7b5c7bc commitdcf7c0e

File tree

3 files changed

+114
-6
lines changed

3 files changed

+114
-6
lines changed

‎expected/aqo_fdw.out‎

Lines changed: 40 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -104,7 +104,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
104104
QUERY PLAN
105105
--------------------------------------------------------------------------------------------------------
106106
Foreign Scan (actual rows=1 loops=1)
107-
AQO not used
107+
AQO: rows=1, error=0%
108108
Output: a.x, b.x
109109
Relations: (public.frgn a) INNER JOIN (public.frgn b)
110110
Remote SQL: SELECT r1.x, r2.x FROM (public.local r1 INNER JOIN public.local r2 ON (((r1.x = r2.x))))
@@ -113,6 +113,39 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
113113
JOINS: 0
114114
(8 rows)
115115

116+
CREATE TABLE local_a(aid int primary key, aval text);
117+
CREATE TABLE local_b(bid int primary key, aid int references local_a(aid), bval text);
118+
INSERT INTO local_a SELECT i, 'val_' || i FROM generate_series(1,100) i;
119+
INSERT INTO local_b SELECT i, mod((i+random()*10)::numeric, 10) + 1, 'val_' || i FROM generate_series(1,1000) i;
120+
ANALYZE local_a, local_b;
121+
CREATE FOREIGN TABLE frgn_a(aid int, aval text) SERVER loopback OPTIONS (table_name 'local_a');
122+
CREATE FOREIGN TABLE frgn_b(bid int, aid int, bval text) SERVER loopback OPTIONS (table_name 'local_b');
123+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
124+
SELECT * from frgn_a AS a, frgn_b AS b
125+
WHERE a.aid = b.aid AND b.bval like 'val%';
126+
QUERY PLAN
127+
-----------------------------------------------
128+
Foreign Scan (actual rows=1000 loops=1)
129+
AQO not used
130+
Relations: (frgn_a a) INNER JOIN (frgn_b b)
131+
Using aqo: true
132+
AQO mode: LEARN
133+
JOINS: 0
134+
(6 rows)
135+
136+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
137+
SELECT * from frgn_a AS a, frgn_b AS b
138+
WHERE a.aid = b.aid AND b.bval like 'val%';
139+
QUERY PLAN
140+
-----------------------------------------------
141+
Foreign Scan (actual rows=1000 loops=1)
142+
AQO: rows=1000, error=0%
143+
Relations: (frgn_a a) INNER JOIN (frgn_b b)
144+
Using aqo: true
145+
AQO mode: LEARN
146+
JOINS: 0
147+
(6 rows)
148+
116149
-- TODO: Non-mergejoinable join condition.
117150
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
118151
SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
@@ -131,7 +164,7 @@ SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
131164
QUERY PLAN
132165
--------------------------------------------------------------------------------------------------------
133166
Foreign Scan (actual rows=0 loops=1)
134-
AQO not used
167+
AQO: rows=1, error=100%
135168
Output: a.x, b.x
136169
Relations: (public.frgn a) INNER JOIN (public.frgn b)
137170
Remote SQL: SELECT r1.x, r2.x FROM (public.local r1 INNER JOIN public.local r2 ON (((r1.x < r2.x))))
@@ -142,8 +175,12 @@ SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
142175

143176
DROP EXTENSION aqo CASCADE;
144177
DROP EXTENSION postgres_fdw CASCADE;
145-
NOTICE: drop cascades to3 other objects
178+
NOTICE: drop cascades to5 other objects
146179
DETAIL: drop cascades to server loopback
147180
drop cascades to user mapping for public on server loopback
148181
drop cascades to foreign table frgn
182+
drop cascades to foreign table frgn_a
183+
drop cascades to foreign table frgn_b
149184
DROP TABLE local;
185+
DROP TABLE local_b;
186+
DROP TABLE local_a;

‎path_utils.c‎

Lines changed: 55 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,8 @@
2323
#include"aqo.h"
2424
#include"hash.h"
2525

26+
#include"postgres_fdw.h"
27+
2628
#ifdefPGPRO_STD
2729
# defineexpression_tree_mutator(node,mutator,context) \
2830
expression_tree_mutator(node, mutator, context, 0)
@@ -65,6 +67,31 @@ create_aqo_plan_node()
6567
returnnode;
6668
}
6769

70+
71+
/* Ensure that it's postgres_fdw's foreign server oid */
72+
staticbool
73+
is_postgres_fdw_server(Oidserverid)
74+
{
75+
ForeignServer*server;
76+
ForeignDataWrapper*fdw;
77+
78+
if (!OidIsValid(serverid))
79+
return false;
80+
81+
server=GetForeignServerExtended(serverid,FSV_MISSING_OK);
82+
if (!server)
83+
return false;
84+
85+
fdw=GetForeignDataWrapperExtended(server->fdwid,FDW_MISSING_OK);
86+
if (!fdw|| !fdw->fdwname)
87+
return false;
88+
89+
if (strcmp(fdw->fdwname,"postgres_fdw")!=0)
90+
return false;
91+
92+
return true;
93+
}
94+
6895
/*
6996
* Extract an AQO node from the plan private field.
7097
* If no one node was found, return pointer to the default value or return NULL.
@@ -519,7 +546,8 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
519546
return;
520547

521548
is_join_path= (src->type==T_NestPath||src->type==T_MergePath||
522-
src->type==T_HashPath);
549+
src->type==T_HashPath||
550+
(src->type==T_ForeignPath&&IS_JOIN_REL(src->parent)));
523551

524552
node=get_aqo_plan_node(plan, true);
525553

@@ -535,8 +563,32 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
535563

536564
if (is_join_path)
537565
{
538-
node->clauses=aqo_get_clauses(root, ((JoinPath*)src)->joinrestrictinfo);
539-
node->jointype= ((JoinPath*)src)->jointype;
566+
if (IsA(src,ForeignPath))
567+
{
568+
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)src->parent->fdw_private;
569+
List*restrictclauses=NIL;
570+
571+
if (!fpinfo)
572+
return;
573+
574+
/* We have to ensure that this is postgres_fdw ForeignPath */
575+
if (!is_postgres_fdw_server(src->parent->serverid))
576+
return;
577+
578+
restrictclauses=list_concat(restrictclauses,fpinfo->joinclauses);
579+
restrictclauses=list_concat(restrictclauses,fpinfo->remote_conds);
580+
restrictclauses=list_concat(restrictclauses,fpinfo->local_conds);
581+
582+
node->clauses=aqo_get_clauses(root,restrictclauses);
583+
node->jointype=fpinfo->jointype;
584+
585+
list_free(restrictclauses);
586+
}
587+
else
588+
{
589+
node->clauses=aqo_get_clauses(root, ((JoinPath*)src)->joinrestrictinfo);
590+
node->jointype= ((JoinPath*)src)->jointype;
591+
}
540592
}
541593
elseif (IsA(src,AggPath))
542594
/* Aggregation node must store grouping clauses. */

‎sql/aqo_fdw.sql‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -60,6 +60,23 @@ SELECT str FROM expln('
6060
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
6161
SELECT*FROM frgnAS a, frgnAS bWHEREa.x=b.x;
6262

63+
CREATETABLElocal_a(aidintprimary key, avaltext);
64+
CREATETABLElocal_b(bidintprimary key, aidintreferences local_a(aid), bvaltext);
65+
INSERT INTO local_aSELECT i,'val_'|| iFROM generate_series(1,100) i;
66+
INSERT INTO local_bSELECT i, mod((i+random()*10)::numeric,10)+1,'val_'|| iFROM generate_series(1,1000) i;
67+
ANALYZE local_a, local_b;
68+
69+
CREATE FOREIGN TABLE frgn_a(aidint, avaltext) SERVER loopback OPTIONS (table_name'local_a');
70+
CREATE FOREIGN TABLE frgn_b(bidint, aidint, bvaltext) SERVER loopback OPTIONS (table_name'local_b');
71+
72+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
73+
SELECT*from frgn_aAS a, frgn_bAS b
74+
WHEREa.aid=b.aidANDb.bvallike'val%';
75+
76+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
77+
SELECT*from frgn_aAS a, frgn_bAS b
78+
WHEREa.aid=b.aidANDb.bvallike'val%';
79+
6380
-- TODO: Non-mergejoinable join condition.
6481
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
6582
SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;
@@ -69,4 +86,6 @@ SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
6986
DROP EXTENSION aqo CASCADE;
7087
DROP EXTENSION postgres_fdw CASCADE;
7188
DROPTABLE local;
89+
DROPTABLE local_b;
90+
DROPTABLE local_a;
7291

0 commit comments

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