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 <kai...@ak.jp.nec.com> > -----Original Message----- > From: Shigeru Hanada [mailto:shigeru.han...@gmail.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 <kai...@ak.jp.nec.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 > > <kai...@ak.jp.nec.com> > > > > > >> -----Original Message----- > >> From: pgsql-hackers-ow...@postgresql.org > >> [mailto:pgsql-hackers-ow...@postgresql.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 > >> 108c...@bpxm15gp.gisp.nec.co.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.han...@gmail.com>: > >> > 2014-12-16 1:22 GMT+09:00 Robert Haas <robertmh...@gmail.com>: > >> >> On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada > >> >> <shigeru.han...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
