Re: Join push-down support for foreign tables

On Wed, Sep 3, 2014 at 5:16 AM, Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)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