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

Commit1ccd480

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 parenta4991a0 commit1ccd480

File tree

3 files changed

+115
-8
lines changed

3 files changed

+115
-8
lines changed

‎expected/aqo_fdw.out

Lines changed: 41 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -110,15 +110,15 @@ SELECT str FROM expln('
110110
JOINS: 0
111111
(6 rows)
112112

113-
--TODO:Should learn on postgres_fdw nodes
113+
-- Should learn on postgres_fdw nodes
114114
SELECT str FROM expln('
115115
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
116116
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
117117
') AS str WHERE str NOT LIKE '%Query Identifier%';
118118
str
119119
--------------------------------------------------------------------------------------------------------
120120
Foreign Scan (actual rows=1 loops=1)
121-
AQO not used
121+
AQO: rows=1, error=0%
122122
Output: a.x, b.x
123123
Relations: (public.frgn a) INNER JOIN (public.frgn b)
124124
Remote SQL: SELECT r1.x, r2.x FROM (public.local r1 INNER JOIN public.local r2 ON (((r1.x = r2.x))))
@@ -127,6 +127,39 @@ SELECT str FROM expln('
127127
JOINS: 0
128128
(8 rows)
129129

130+
CREATE TABLE local_a(aid int primary key, aval text);
131+
CREATE TABLE local_b(bid int primary key, aid int references local_a(aid), bval text);
132+
INSERT INTO local_a SELECT i, 'val_' || i FROM generate_series(1,100) i;
133+
INSERT INTO local_b SELECT i, mod((i+random()*10)::numeric, 10) + 1, 'val_' || i FROM generate_series(1,1000) i;
134+
ANALYZE local_a, local_b;
135+
CREATE FOREIGN TABLE frgn_a(aid int, aval text) SERVER loopback OPTIONS (table_name 'local_a');
136+
CREATE FOREIGN TABLE frgn_b(bid int, aid int, bval text) SERVER loopback OPTIONS (table_name 'local_b');
137+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
138+
SELECT * from frgn_a AS a, frgn_b AS b
139+
WHERE a.aid = b.aid AND b.bval like 'val%';
140+
QUERY PLAN
141+
-----------------------------------------------
142+
Foreign Scan (actual rows=1000 loops=1)
143+
AQO not used
144+
Relations: (frgn_a a) INNER JOIN (frgn_b b)
145+
Using aqo: true
146+
AQO mode: LEARN
147+
JOINS: 0
148+
(6 rows)
149+
150+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
151+
SELECT * from frgn_a AS a, frgn_b AS b
152+
WHERE a.aid = b.aid AND b.bval like 'val%';
153+
QUERY PLAN
154+
-----------------------------------------------
155+
Foreign Scan (actual rows=1000 loops=1)
156+
AQO: rows=1000, error=0%
157+
Relations: (frgn_a a) INNER JOIN (frgn_b b)
158+
Using aqo: true
159+
AQO mode: LEARN
160+
JOINS: 0
161+
(6 rows)
162+
130163
-- TODO: Non-mergejoinable join condition.
131164
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
132165
SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
@@ -147,7 +180,7 @@ SELECT str FROM expln('
147180
str
148181
--------------------------------------------------------------------------------------------------------
149182
Foreign Scan (actual rows=0 loops=1)
150-
AQO not used
183+
AQO: rows=1, error=100%
151184
Output: a.x, b.x
152185
Relations: (public.frgn a) INNER JOIN (public.frgn b)
153186
Remote SQL: SELECT r1.x, r2.x FROM (public.local r1 INNER JOIN public.local r2 ON (((r1.x < r2.x))))
@@ -158,8 +191,12 @@ SELECT str FROM expln('
158191

159192
DROP EXTENSION aqo CASCADE;
160193
DROP EXTENSION postgres_fdw CASCADE;
161-
NOTICE: drop cascades to3 other objects
194+
NOTICE: drop cascades to5 other objects
162195
DETAIL: drop cascades to server loopback
163196
drop cascades to user mapping for public on server loopback
164197
drop cascades to foreign table frgn
198+
drop cascades to foreign table frgn_a
199+
drop cascades to foreign table frgn_b
165200
DROP TABLE local;
201+
DROP TABLE local_b;
202+
DROP TABLE local_a;

‎path_utils.c

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

26+
#include"postgres_fdw.h"
2627

2728
/*
2829
* Hook on creation of a plan node. We need to store AQO-specific data to
@@ -61,6 +62,31 @@ create_aqo_plan_node()
6162
returnnode;
6263
}
6364

65+
66+
/* Ensure that it's postgres_fdw's foreign server oid */
67+
staticbool
68+
is_postgres_fdw_server(Oidserverid)
69+
{
70+
ForeignServer*server;
71+
ForeignDataWrapper*fdw;
72+
73+
if (!OidIsValid(serverid))
74+
return false;
75+
76+
server=GetForeignServerExtended(serverid,FSV_MISSING_OK);
77+
if (!server)
78+
return false;
79+
80+
fdw=GetForeignDataWrapperExtended(server->fdwid,FDW_MISSING_OK);
81+
if (!fdw|| !fdw->fdwname)
82+
return false;
83+
84+
if (strcmp(fdw->fdwname,"postgres_fdw")!=0)
85+
return false;
86+
87+
return true;
88+
}
89+
6490
/*
6591
* Extract an AQO node from the plan private field.
6692
* If no one node was found, return pointer to the default value or return NULL.
@@ -503,7 +529,8 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
503529
return;
504530

505531
is_join_path= (src->type==T_NestPath||src->type==T_MergePath||
506-
src->type==T_HashPath);
532+
src->type==T_HashPath||
533+
(src->type==T_ForeignPath&&IS_JOIN_REL(src->parent)));
507534

508535
node=get_aqo_plan_node(plan, true);
509536

@@ -519,8 +546,32 @@ aqo_create_plan_hook(PlannerInfo *root, Path *src, Plan **dest)
519546

520547
if (is_join_path)
521548
{
522-
node->clauses=aqo_get_clauses(root, ((JoinPath*)src)->joinrestrictinfo);
523-
node->jointype= ((JoinPath*)src)->jointype;
549+
if (IsA(src,ForeignPath))
550+
{
551+
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)src->parent->fdw_private;
552+
List*restrictclauses=NIL;
553+
554+
if (!fpinfo)
555+
return;
556+
557+
/* We have to ensure that this is postgres_fdw ForeignPath */
558+
if (!is_postgres_fdw_server(src->parent->serverid))
559+
return;
560+
561+
restrictclauses=list_concat(restrictclauses,fpinfo->joinclauses);
562+
restrictclauses=list_concat(restrictclauses,fpinfo->remote_conds);
563+
restrictclauses=list_concat(restrictclauses,fpinfo->local_conds);
564+
565+
node->clauses=aqo_get_clauses(root,restrictclauses);
566+
node->jointype=fpinfo->jointype;
567+
568+
list_free(restrictclauses);
569+
}
570+
else
571+
{
572+
node->clauses=aqo_get_clauses(root, ((JoinPath*)src)->joinrestrictinfo);
573+
node->jointype= ((JoinPath*)src)->jointype;
574+
}
524575
}
525576
elseif (IsA(src,AggPath))
526577
/* Aggregation node must store grouping clauses. */

‎sql/aqo_fdw.sql

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -61,12 +61,29 @@ SELECT str FROM expln('
6161
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
6262
')AS strWHERE str NOTLIKE'%Sort Method%';
6363

64-
--TODO:Should learn on postgres_fdw nodes
64+
-- Should learn on postgres_fdw nodes
6565
SELECT strFROM expln('
6666
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
6767
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
6868
')AS strWHERE str NOTLIKE'%Query Identifier%';
6969

70+
CREATETABLElocal_a(aidintprimary key, avaltext);
71+
CREATETABLElocal_b(bidintprimary key, aidintreferences local_a(aid), bvaltext);
72+
INSERT INTO local_aSELECT i,'val_'|| iFROM generate_series(1,100) i;
73+
INSERT INTO local_bSELECT i, mod((i+random()*10)::numeric,10)+1,'val_'|| iFROM generate_series(1,1000) i;
74+
ANALYZE local_a, local_b;
75+
76+
CREATE FOREIGN TABLE frgn_a(aidint, avaltext) SERVER loopback OPTIONS (table_name'local_a');
77+
CREATE FOREIGN TABLE frgn_b(bidint, aidint, bvaltext) SERVER loopback OPTIONS (table_name'local_b');
78+
79+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
80+
SELECT*from frgn_aAS a, frgn_bAS b
81+
WHEREa.aid=b.aidANDb.bvallike'val%';
82+
83+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
84+
SELECT*from frgn_aAS a, frgn_bAS b
85+
WHEREa.aid=b.aidANDb.bvallike'val%';
86+
7087
-- TODO: Non-mergejoinable join condition.
7188
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
7289
SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;
@@ -78,4 +95,6 @@ SELECT str FROM expln('
7895
DROP EXTENSION aqo CASCADE;
7996
DROP EXTENSION postgres_fdw CASCADE;
8097
DROPTABLE local;
98+
DROPTABLE local_b;
99+
DROPTABLE local_a;
81100

0 commit comments

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