Re: WIP: Join push-down for foreign tables

Hi,<br /><br />I am interested in the development you are doing regarding join push down and fdw stuff for remote
postgreSQLservers.<br />Is there a way to get the postgres fdw you are providing here for common 9.1?<br />I saw that
thetar you are providing here is adapted only for your patch.<br /><br />Regards,<br /><br />Michael<br /><br /><div
class="gmail_quote">2011/9/14Shigeru Hanada <span dir="ltr"><<a
href="mailto:shigeru.hanada@gmail.com">shigeru.hanada@gmail.com</a>></span><br/><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> Hi all,<br /><br /> I'd like to propose $SUBJECT
forfurther foreign query optimization.<br /> I've not finished development, but I'd appreciate it if I got someone's<br
/>review on my WIP code and its design.<br /><br /> Changes I made<br /> ==============<br /><br /> (1) Add foreign
serverOID to RelOptInfo<br /> I think it would be nice to know whether a join comes from one foreign<br /> server or
notwithout digging into child nodes during considering paths<br /> for a query.  So I added serverid field to
RelOptInfo,which defaults to<br /> InvalidOid ,and is set to OID of the server if the node and all of its<br />
childrenare from same foreign server.  This also avoids looking catalog<br /> up for foreign table entry to determine
FDWroutine.<br /><br /> (2) Add new planner node, ForeignJoinPath<br /> ForeignJoinPath derives JoinPath, like other
Joinnodes, and holds<br /> FdwPlan like ForeignPath node.<br /><br /> This node is used to represent a pushed-down join
betweenforeign tables<br /> and/or another foreign join in early planning phase, for all of<br /> combination such as
table-table,table-join, join-table and join-join<br /> will be considered.  In addition, though they might generate
same<br/> internal (FDW-specific) plan, reversed combination is considered;<br /> planner generates two ForeignJoinPath
forboth (A & B) and (B & A).<br /><br /> During creation of this node,　planner calls new FDW handler
function<br/> PlanForeignJoin to get a FdwPlan which includes costs and internal plan<br /> of a foreign join.  If a
FDWcan't (or doesn't want to) handle this<br /> join, just return NULL is OK, and then planner gives such
optimization<br/> up and considers other usual join methods such as nested loop and hash join.<br /><br /> A subtree
whichhas a ForeignJoin on its top is translated into a<br /> ForeignScan node during constructing a plan tree.  This
behavioris<br /> different from other join path nodes such as NestPath and MergePath,<br /> because they have child
plannodes correspond to path nodes.<br /><br /> (3) Add EXPALIN support for foreign join (currently just for debug)<br
/>ForeignScan might not be a simple foreign table scan, so<br /> ExplainScanTarget() can't be used for it.  An idea I
haveis adding<br /> ExplainForeignScanTarget() to handle ForeignScan separately from other<br /> scan nodes.<br /><br
/>(4) Add new GUC parameter, enable_foreignjoin<br /> If this was off, planner never generates ForeignJoinPath.  In
suchcase,<br /> foreign tables will be joined with one of NestLoop, MergeJoin and HashJoin.<br /><br /> Known issue<br
/>===========<br /><br /> I'm sorry but attached patch, join_pushdown_v1.patch, is WIP, so<br /> currently some kind of
queryfails.  Known failure patterns are:<br /><br /> *) SELECT * FROM A JOIN B (...) doesn't work.  Specifying columns
in<br/> SELECT clause explicitly like "SELECT A.col1, A.col2, ..." seems to work.<br /> *) ORDER BY causes error if no
columnis specified in SELECT clause from<br /> sort key's table.<br /><br /> Probably more problems still are
there...<br/><br /> PG-wrapper as sample implementation<br /> ===================================<br /><br />
pgsql_fdw-0.1.0.tar.gzis an WIP implementation of PG-wrapper, which can<br /> (hopefully) handle both simple foreign
tablescan and multiple foreign<br /> joins.  You can build it with placing in contrib/, or using pgxs.  Note<br /> that
ithas some issues such as memory leak of PGresult.  I'm planning<br /> to propose this wrapper as a contrib module, but
itwould be after<br /> clearing such issues.<br /><br /> Regards,<br /><font color="#888888">--<br /> Shigeru
Hanada</font><br/><br /></blockquote></div><br />-- <br />Michael Paquier<br /><a href="http://michael.otacoo.com"
target="_blank">http://michael.otacoo.com</a><br/>