Re: WIP: Join push-down for foreign tables - Mailing list pgsql-hackers
From | Michael Paquier |
---|---|
Subject | Re: WIP: Join push-down for foreign tables |
Date | |
Msg-id | CAB7nPqRW_99t2UHozzsDXb3EF+wKr3whKCAJwyDrB0P0UTFPUw@mail.gmail.com Whole thread Raw |
In response to | WIP: Join push-down for foreign tables (Shigeru Hanada <shigeru.hanada@gmail.com>) |
Responses |
Re: WIP: Join push-down for foreign tables
|
List | pgsql-hackers |
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/>
pgsql-hackers by date: