Re: Join push-down support for foreign tables

From: Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com>
To: Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Join push-down support for foreign tables
Date: 2015-02-17 01:39:02
Message-ID: 9A28C8860F777E439AA12E8AEA7694F8010B20A3@BPXM15GP.gisp.nec.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Let me put some comments in addition to where you're checking now.

[design issues]
* Cost estimation
Estimation and evaluation of cost for remote join query is not an
obvious issue. In principle, local side cannot determine the cost
to run remote join without remote EXPLAIN, because local side has
no information about JOIN logic applied on the remote side.
Probably, we have to put an assumption for remote join algorithm,
because local planner has no idea about remote planner's choice
unless foreign-join don't take "use_remote_estimate".
I think, it is reasonable assumption (even if it is incorrect) to
calculate remote join cost based on local hash-join algorithm.
If user wants more correct estimation, remote EXPLAIN will make
more reliable cost estimation.

It also needs a consensus whether cost for remote CPU execution is
equivalent to local CPU. If we think local CPU is rare resource
than remote one, a discount rate will make planner more preferable
to choose remote join than local one.

Once we assume a join algorithm for remote join, unit cost for
remote CPU, we can calculate a cost for foreign join based on
the local join logic plus cost for network translation (maybe
fdw_tuple_cost?).

* FDW options
Unlike table scan, FDW options we should refer is unclear.
Table level FDW options are associated with a foreign table as
literal. I think we have two options here:
1. Foreign-join refers FDW options for foreign-server, but ones
for foreign-tables are ignored.
2. Foreign-join is prohibited when both of relations don't have
identical FDW options.
My preference is 2. Even though N-way foreign join, it ensures
all the tables involved with (N-1)-way foreign join has identical
FDW options, thus it leads we can make N-way foreign join with
all identical FDW options.
One exception is "updatable" flag of postgres_fdw. It does not
make sense on remote join, so I think mixture of updatable and
non-updatable foreign tables should be admitted, however, it is
a decision by FDW driver.

Probably, above points need to take time for getting consensus.
I'd like to see your opinion prior to editing your patch.

[implementation issues]
The interface does not intend to add new Path/Plan type for each scan
that replaces foreign joins. What postgres_fdw should do is, adding
ForeignPath towards a particular joinrel, then it populates ForeignScan
with remote join query once it got chosen by the planner.

A few functions added in src/backend/foreign/foreign.c are not
called by anywhere, at this moment.

create_plan_recurse() is reverted to static. It is needed for custom-
join enhancement, if no other infrastructure can support.

I'll check the code to construct remote query later.

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

> -----Original Message-----
> From: Shigeru Hanada [mailto:shigeru(dot)hanada(at)gmail(dot)com]
> Sent: Monday, February 16, 2015 1:54 PM
> To: Kaigai Kouhei(海外 浩平)
> Cc: Robert Haas; PostgreSQL-development
> Subject: ##freemail## Re: [HACKERS] Join push-down support for foreign
> tables
>
> Kaigai-san,
>
> Oops. I rebased the patch onto your v4 custom/foreign join patch.
> But as you mentioned off-list, I found a flaw about inappropriate change
> about NestPath still remains in the patch... I might have made my dev branch
> into unexpected state. I'll check it soon.
>
> 2015-02-16 13:13 GMT+09:00 Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com>:
> > Hanada-san,
> >
> > Your patch mixtures enhancement of custom-/foreign-scan interface and
> > enhancement of contrib/postgres_fdw... Probably, it is a careless mis-
> > operation.
> > Please make your patch as differences from my infrastructure portion.
> >
> >
> > Also, I noticed this "Join pushdown support for foreign tables" patch
> > is unintentionally rejected in the last commit fest.
> > https://commitfest.postgresql.org/3/20/
> > I couldn't register myself as reviewer. How do I operate it on the new
> > commitfest application?
> >
> > Thanks,
> > --
> > NEC OSS Promotion Center / PG-Strom Project KaiGai Kohei
> > <kaigai(at)ak(dot)jp(dot)nec(dot)com>
> >
> >
> >> -----Original Message-----
> >> From: pgsql-hackers-owner(at)postgresql(dot)org
> >> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Shigeru
> >> Hanada
> >> Sent: Monday, February 16, 2015 1:03 PM
> >> To: Robert Haas
> >> Cc: PostgreSQL-development
> >> Subject: Re: [HACKERS] Join push-down support for foreign tables
> >>
> >> Hi
> >>
> >> I've revised the patch based on Kaigai-san's custom/foreign join
> >> patch posted in the thread below.
> >>
> >>
> http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8
> >> 0
> >> 108C355(at)BPXM15GP(dot)gisp(dot)nec(dot)co(dot)jp
> >>
> >> Basically not changed from the version in the last CF, but as Robert
> >> commented before, N-way (not only 2-way) joins should be supported in
> >> the first version by construct SELECT SQL by containing source query
> >> in FROM clause as inline views (a.k.a. from clause subquery).
> >>
> >> 2014-12-26 13:48 GMT+09:00 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>:
> >> > 2014-12-16 1:22 GMT+09:00 Robert Haas <robertmhaas(at)gmail(dot)com>:
> >> >> On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada
> >> >> <shigeru(dot)hanada(at)gmail(dot)com> wrote:
> >> >>> I'm working on $SUBJECT and would like to get comments about the
> >> >>> design. Attached patch is for the design below.
> >> >>
> >> >> I'm glad you are working on this.
> >> >>
> >> >>> 1. Join source relations
> >> >>> As described above, postgres_fdw (and most of SQL-based FDWs)
> >> >>> needs to check that 1) all foreign tables in the join belong to a
> >> >>> server, and
> >> >>> 2) all foreign tables have same checkAsUser.
> >> >>> In addition to that, I add extra limitation that both inner/outer
> >> >>> should be plain foreign tables, not a result of foreign join.
> >> >>> This limiation makes SQL generator simple. Fundamentally it's
> >> >>> possible to join even join relations, so N-way join is listed as
> >> >>> enhancement item below.
> >> >>
> >> >> It seems pretty important to me that we have a way to push the
> >> >> entire join nest down. Being able to push down a 2-way join but
> >> >> not more seems like quite a severe limitation.
> >> >
> >> > Hmm, I agree to support N-way join is very useful. Postgres-XC's
> >> > SQL generator seems to give us a hint for such case, I'll check it
> >> > out again.
> >> >
> >> > --
> >> > Shigeru HANADA
> >>
> >>
> >>
> >> --
> >> Shigeru HANADA
>
>
>
> --
> Shigeru HANADA

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Petr Jelinek 2015-02-17 01:48:09 Re: Add min and max execute statement time in pg_stat_statement
Previous Message Tom Lane 2015-02-17 01:27:55 We do not need pg_promote_v4_to_v6_addr/mask