On Wed, Sep 3, 2014 at 5:16 AM, Shigeru Hanada <shigeru.han...@gmail.com> wrote: > In 2011 I proposed join push-down support for foreign tables, which > would improve performance of queries which contain join between > foreign tables in one server, but it has not finished before time-up. > This performance improvement would widen application range of foreign > tables, so I'd like to tackle the work again. > > The descriptions below are based on previous discussions and additional > studies.
Hanada-san, it is fantastic to see you working on this again. I think your proposal sounds promising and it is along the lines of what I have considered in the past. > (1) Separate cost estimation phases? > For existing join paths, planner estimates their costs in two phaeses. > In the first phase initial_cost_foo(), here foo is one of > nestloop/mergejoin/hashjoin, produces lower-bound estimates for > elimination. The second phase is done for only promising paths which > passed add_path_precheck(), by final_cost_foo() for cost and result > size. I'm not sure that we need to follow this manner, since FDWs > would be able to estimate final cost/size with their own methods. The main problem I see here is that accurate costing may require a round-trip to the remote server. If there is only one path that is probably OK; the cost of asking the question will usually be more than paid for by hearing that the pushed-down join clobbers the other possible methods of executing the query. But if there are many paths, for example because there are multiple sets of useful pathkeys, it might start to get a bit expensive. Probably both the initial cost and final cost calculations should be delegated to the FDW, but maybe within postgres_fdw, the initial cost should do only the work that can be done without contacting the remote server; then, let the final cost step do that if appropriate. But I'm not entirely sure what is best here. > (2) How to reflect cost of transfer > Cost of transfer is dominant in foreign table operations, including > foreign scans. It would be nice to have some mechanism to reflect > actual time of transfer to the cost estimation. An idea is to have a > FDW option which represents cost factor of transfer, say > transfer_cost. That would be reasonable. I assume users would normally wish to specify this per-server, and the default should be something reasonable for a LAN. > (4) criteria for push-down > It is assumed that FDWs can push joins down to remote when all foreign > tables are in same server. IMO a SERVER objects represents a logical > data source. For instance database for postgres_fdw and other > connection-based FDWs, and disk volumes (or directory?) for file_fdw. > Is this reasonable assumption? I think it's probably good to give an FDW the option of producing a ForeignJoinPath for any join against a ForeignPath *or ForeignJoinPath* for the same FDW. It's perhaps unlikely that an FDW can perform a join efficiently between two data sources with different server definitions, but why not give it the option? It should be pretty fast for the FDW to realize, oh, the server OIDs don't match - and at that point it can exit without doing anything further if that seems desirable. And there might be some kinds of data sources where cross-server joins actually can be executed quickly (e.g. when the underlying data is just in two files in different places on the local machine). > (5) Terminology > I used "foreign join" as a process which joins foreign tables on > *remote* side, but is this enough intuitive? Another idea is using > "remote join", is this more appropriate for this kind of process? I > hesitate to use "remote join" because it implies client-server FDWs, > but foreign join is not limited to such FDWs, e.g. file_fdw can have > extra file which is already joined files accessed via foreign tables. Foreign join is perfect. As I alluded to above, it's pretty important to make sure that this works with large join trees; that is, if I join four foreign tables, I don't want it to push down a join between two of the tables and a join between the other two tables and then join the results of those joins locally. Instead, I want to push the entire join tree to the foreign server and execute the whole thing there. Some care may be needed in designing the hooks to make sure this works as desired. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
