[HACKERS] postgres_fdw: support parameterized foreign joins - Mailing list pgsql-hackers

Hi,

I'd like to propose to support parameterized foreign joins.  Attached is  
a patch for that, which has been created on top of [1].

In [2], I said that postgres_fdw could create parameterized paths from  
joinable combinations of the cheapest-parameterized paths for the  
inner/outer relations, but for identifying the joinable combinations,  
postgres_fdw would need to do the same work as the core, which wouldn't  
be good.  Also, I thought that the parameterized paths could be created  
by using the required_outer relations in ParamPathInfos stored in the  
join relation's ppilist, which I thought would have already built  
ParamPathInfos for parameterized local-join paths, but I noticed it  
isn't guaranteed that such local-join paths are always created and their  
ParamPathInfos are always stored in the pplilist.  Instead, I'd propose  
to collect the required_outer outer relations that the core tried to  
create parameterized local-join paths for during add_paths_to_joinrel(),  
and build parameterized foreign-join paths for those outer relations  
during postgresGetForeignJoinPaths().

Here is an example:

postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# create server loopback foreign data wrapper postgres_fdw  
options (dbname 'postgres');
CREATE SERVER
postgres=# create user mapping for public server loopback;
CREATE USER MAPPING
postgres=# create table t1 (a int , b int, CONSTRAINT t1_pkey PRIMARY  
KEY (a));
CREATE TABLE
postgres=# create table t2 (a int , b int, CONSTRAINT t2_pkey PRIMARY  
KEY (a));
CREATE TABLE
postgres=# create foreign table ft1 (a int, b int) server loopback  
options (table_name 't1');
CREATE FOREIGN TABLE
postgres=# create foreign table ft2 (a int, b int) server loopback  
options (table_name 't2');
CREATE FOREIGN TABLE
postgres=# insert into t1 select id, id % 10 from generate_series(1,  
10000) id;
INSERT 0 10000
postgres=# insert into t2 select id, id % 10 from generate_series(1,  
10000) id;
INSERT 0 10000
postgres=# alter foreign table ft1 options (use_remote_estimate 'true');
ALTER FOREIGN TABLE
postgres=# alter foreign table ft2 options (use_remote_estimate 'true');
ALTER FOREIGN TABLE
postgres=# create table test (a int, b int);
CREATE TABLE
postgres=# insert into test values (1, 1);
INSERT 0 1
postgres=# analyze test;
ANALYZE
postgres=# explain verbose select * from test r1 left join (ft1 r2 inner  
join ft2 r3 on (r2.a = r3.a)) on (r3.a = r1.a) limit 1;
  
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=100.58..100.92 rows=1 width=24)
    Output: r1.a, r1.b, r2.a, r2.b, r3.a, r3.b
    ->  Nested Loop Left Join  (cost=100.58..117.67 rows=50 width=24)
          Output: r1.a, r1.b, r2.a, r2.b, r3.a, r3.b
          ->  Seq Scan on public.test r1  (cost=0.00..1.01 rows=1 width=8)
                Output: r1.a, r1.b
          ->  Foreign Scan  (cost=100.58..116.65 rows=1 width=16)
                Output: r2.a, r2.b, r3.a, r3.b
                Relations: (public.ft1 r2) INNER JOIN (public.ft2 r3)
                Remote SQL: SELECT r2.a, r2.b, r3.a, r3.b FROM  
(public.t1 r2 INNER JOIN public.t2 r3 ON (((r2.a = r3.a)))) WHERE ((r3.a  
= $1::integer))
(10 rows)

Notes:
* Since add_paths_to_joinrel() for join {B, A} might provide different  
parameterizations of result local-join paths from that for join {A, B},  
so the patch allows postgresGetForeignJoinPaths() to build paths after  
that function has pushdown_safe=true.
* create_foreignscan_path() only calls get_baserel_parampathinfo() to  
set the param_info member.  We would need to do something about that so  
it can handle the parameterized-foreign-join-path case properly.  Though  
I left that function as-is because get_baserel_parampathinfo() can  
return the ParamPathInfo created in postgresGetForeignJoinPaths() for an  
input parameterized foreign-join path, by accident.

I'll add this to the upcoming commitfest.

Best regards,
Etsuro Fujita

[1]  
https://www.postgresql.org/message-id/0700eb97-d9db-33da-4ba2-e28d2a1631d9%40lab.ntt.co.jp
[2]  
https://www.postgresql.org/message-id/e18b9bf5-1557-cb9c-001e-0861a1d7dfce%40lab.ntt.co.jp

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers