JOINS: 0

(6 rows)

--TODO:Should learn on postgres_fdw nodes

-- Should learn on postgres_fdw nodes

SELECT str FROM expln('

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)

SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;

') AS str WHERE str NOT LIKE '%Query Identifier%';

str

--------------------------------------------------------------------------------------------------------

Foreign Scan (actual rows=1 loops=1)

AQO not used

AQO: rows=1, error=0%

Output: a.x, b.x

Relations: (public.frgn a) INNER JOIN (public.frgn b)

Remote SQL: SELECT r1.x, r2.x FROM (public.local r1 INNER JOIN public.local r2 ON (((r1.x = r2.x))))

JOINS: 0

(8 rows)

CREATE TABLE local_a(aid int primary key, aval text);

CREATE TABLE local_b(bid int primary key, aid int references local_a(aid), bval text);

INSERT INTO local_a SELECT i, 'val_' || i FROM generate_series(1,100) i;

INSERT INTO local_b SELECT i, mod((i+random()*10)::numeric, 10) + 1, 'val_' || i FROM generate_series(1,1000) i;

ANALYZE local_a, local_b;

CREATE FOREIGN TABLE frgn_a(aid int, aval text) SERVER loopback OPTIONS (table_name 'local_a');

CREATE FOREIGN TABLE frgn_b(bid int, aid int, bval text) SERVER loopback OPTIONS (table_name 'local_b');

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

SELECT * from frgn_a AS a, frgn_b AS b

WHERE a.aid = b.aid AND b.bval like 'val%';

QUERY PLAN

-----------------------------------------------

Foreign Scan (actual rows=1000 loops=1)

AQO not used

Relations: (frgn_a a) INNER JOIN (frgn_b b)

Using aqo: true

AQO mode: LEARN

JOINS: 0

(6 rows)

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

SELECT * from frgn_a AS a, frgn_b AS b

WHERE a.aid = b.aid AND b.bval like 'val%';

QUERY PLAN

-----------------------------------------------

Foreign Scan (actual rows=1000 loops=1)

AQO: rows=1000, error=0%

Relations: (frgn_a a) INNER JOIN (frgn_b b)

Using aqo: true

AQO mode: LEARN

JOINS: 0

(6 rows)

-- TODO: Non-mergejoinable join condition.

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;

str

--------------------------------------------------------------------------------------------------------

Foreign Scan (actual rows=0 loops=1)

AQO not used

AQO: rows=1, error=100%

Output: a.x, b.x

Relations: (public.frgn a) INNER JOIN (public.frgn b)

Remote SQL: SELECT r1.x, r2.x FROM (public.local r1 INNER JOIN public.local r2 ON (((r1.x < r2.x))))

DROP EXTENSION aqo CASCADE;

DROP EXTENSION postgres_fdw CASCADE;

NOTICE: drop cascades to3 other objects

NOTICE: drop cascades to5 other objects

DETAIL: drop cascades to server loopback

drop cascades to user mapping for public on server loopback

drop cascades to foreign table frgn

drop cascades to foreign table frgn_a

drop cascades to foreign table frgn_b

DROP TABLE local;

DROP TABLE local_b;

DROP TABLE local_a;

returnnode;

}

is_postgres_fdw_server(Oidserverid)

{

ForeignServer*server;

ForeignDataWrapper*fdw;

if (!OidIsValid(serverid))

return false;

server=GetForeignServerExtended(serverid,FSV_MISSING_OK);

if (!server)

return false;

fdw=GetForeignDataWrapperExtended(server->fdwid,FDW_MISSING_OK);

if (!fdw|| !fdw->fdwname)

return false;

if (strcmp(fdw->fdwname,"postgres_fdw")!=0)

return false;

return true;

}

return;

is_join_path= (src->type==T_NestPath||src->type==T_MergePath||

src->type==T_HashPath);

(src->type==T_ForeignPath&&IS_JOIN_REL(src->parent)));

node=get_aqo_plan_node(plan, true);

if (is_join_path)

{

node->clauses=aqo_get_clauses(root, ((JoinPath*)src)->joinrestrictinfo);

node->jointype= ((JoinPath*)src)->jointype;

if (IsA(src,ForeignPath))

{

PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)src->parent->fdw_private;

List*restrictclauses=NIL;

if (!fpinfo)

return;

if (!is_postgres_fdw_server(src->parent->serverid))

return;

restrictclauses=list_concat(restrictclauses,fpinfo->joinclauses);

restrictclauses=list_concat(restrictclauses,fpinfo->remote_conds);

restrictclauses=list_concat(restrictclauses,fpinfo->local_conds);

node->clauses=aqo_get_clauses(root,restrictclauses);

node->jointype=fpinfo->jointype;

list_free(restrictclauses);

}

{

node->clauses=aqo_get_clauses(root, ((JoinPath*)src)->joinrestrictinfo);

node->jointype= ((JoinPath*)src)->jointype;

}

}

elseif (IsA(src,AggPath))

')AS strWHERE str NOTLIKE'%Sort Method%';

SELECT strFROM expln('

')AS strWHERE str NOTLIKE'%Query Identifier%';

CREATETABLElocal_a(aidintprimary key, avaltext);

CREATETABLElocal_b(bidintprimary key, aidintreferences local_a(aid), bvaltext);

INSERT INTO local_aSELECT i,'val_'|| iFROM generate_series(1,100) i;

INSERT INTO local_bSELECT i, mod((i+random()*10)::numeric,10)+1,'val_'|| iFROM generate_series(1,1000) i;

ANALYZE local_a, local_b;

CREATE FOREIGN TABLE frgn_a(aidint, avaltext) SERVER loopback OPTIONS (table_name'local_a');

CREATE FOREIGN TABLE frgn_b(bidint, aidint, bvaltext) SERVER loopback OPTIONS (table_name'local_b');

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

SELECT*from frgn_aAS a, frgn_bAS b

WHEREa.aid=b.aidANDb.bvallike'val%';

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

SELECT*from frgn_aAS a, frgn_bAS b

WHEREa.aid=b.aidANDb.bvallike'val%';

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;

DROP EXTENSION aqo CASCADE;

DROP EXTENSION postgres_fdw CASCADE;

DROPTABLE local;

DROPTABLE local_b;

DROPTABLE local_a;