postgres_fdw: support parameterized foreign joins

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: postgres_fdw: support parameterized foreign joins
Date: 2017-02-27 09:40:54
Message-ID: be91628f-b754-0dcd-5998-451cea28ecd0@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: 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

Attachment Content-Type Size
postgres-fdw-param-foreign-joins-1.patch text/x-diff 18.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Erik Rijkers 2017-02-27 10:07:43 Re: Logical replication existing data copy
Previous Message Okano, Naoki 2017-02-27 09:36:44 Re: Keep ECPG comment for log_min_duration_statement