Re: Join push-down support for foreign tables

(2014/09/04 21:37), Robert Haas wrote:> On Wed, Sep 3, 2014 at 5:16 AM,
Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com> wrote:
>> (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.

I agree that requiring round-trip per path is unbearable, so main source
of plan cost should be local statistics gathered by ANALYZE command. If
an FDW needs extra information for planning, it should obtain that from
FDW options or its private catalogs to avoid undesirable round-trips.
FDWs like postgres_fdw would want to optimize plan by providing paths
with pathkeys (not only use remote index, but it also allows MergeJoin
at upper level),

I noticed that order of join considering is an issue too. Planner
compares currently-cheapest path to newly generated path, and mostly
foreign join path would be the cheapest, so considering foreign join
would reduce planner overhead.

> 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.

Agreed. I'll design planner API along that way for now.

>> (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.

This enhancement could be applied separately from foreign join patch.

>> (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).

Indeed how to separate servers is left to users, or author of FDWs,
though postgres_fdw and most of other FDWs can join foreign tables in a
server. I think it would be good if we can know two foreign tables are
managed by same FDW, from FdwRoutine, maybe adding new API which returns
FDW identifier?

>> (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.
>

I think so too, so ForeignJoinPath should be able to be an input of
another ForeignJoinPath in upper join level. But I also think joining
on remote or not should be decided based on cost, as existing joins are
planned with bottom-up approach.

Regards,
--
Shigeru HANADA