Re: Join push-down support for foreign tables

Hi all,

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.

Background
==========

At the moment FDWs can't handle join, so every join are processed on
local side even if the source relations are on the same server. It's
apparently inefficient to fetch possible rows from remote and join
them on local and waste some of them since join condition doesn't
match. If FDW (typically SQL-based FDWs like postgres_fdw) can get
control of JOIN operation, it would optimize queries for source tables
into a join query and avoid transfer of un-match rows.

With this improvement, most of joins in usual use, especially joins
between large foreign tables which don't match much, would become
remarkablly fast, for the reasons below.

a) less data transfer
Especially for inner joins, result of join is usually much smaller
than source tables. If the original target list doesn't contain join
keys, FDW might be able to omit from the SELECT list of remote queries
because they are only necessary on remote side.

b) more optimization on remote side
Join query would provide remote data source more optimization chances,
such as using index.

Changes expected
================

In the past development trial, these changes seem necessary at least.

(1) Add server oid field to RelOptInfo
This attribute is set only when the RelOptInfo is a joinrel, and all
underlying base relations are foreign tables and they have same server
oid. This field is set through join consideration from lower join
level to high (many tables) level, IOW from the bottom to the top. If
all base relations joined in a query are on same server, top
RelOptInfo which represents final output has valid server oid. In
such case, whole query could be pushed down to the server and user can
get most efficient result.

New helper function GetFdwRoutineByServerId(Oid serverid) which
returns FdwRoutine of given server oid would be handy.

(2) Add new path node for foreign join
New path node ForeignJoinPath, which inherits JoinPath like other join
path nodes, represents a join between ForeignPath or ForeignJoinPath.
ForeignJoinPath has fdw_private list to hold FDW-specific information
through the path consideration phase. This is similar to fdw_private
of ForeignPath path node.

This node cares only type of join such as INNER JOIN and LEFT OUTER
JOIN, but doesn't care how to do it. IOW foreign join is not special
case of existing join nodes such as nested loops, merge join and hash
join. FDW can implement a foreign join in arbitrary way, for
instance, file_fdw can have already-joined file for particular
combination for optimization, and postgres_fdw can generate a SELECT
query which contains JOIN clause and avoid essentially unnecessary
data transfer.

At the moment I'm not sure whether we should support SEMI/ANTI join in
the context of foreign join. It would require postgres_fdw (or other
SQL-based FDWs) to generate query with subquery connected with IN/NOT
IN clause, but it seems too far to head to in the first version.

We (and especially FDW authors) need to note that join push-down is
not the best way in some situations. In most cases OUTER JOIN
populates data on remote side more than current FDWs transfer,
especially for FULL OUTER JOIN and
CROSS JOIN (cartesian product).

(3) Add new plan node for foreign join
New plan node ForeignJoin, which inherits Join like other join plan
nodes. This node is similar to other join plan nodes such as
NestLoop, MergeJoin and HashJoin, but it delegates actual processing
to FDW associated to the server.

This means that new plan state node for ForeignJoin, say
ForeignJoinState, is also needed.

(4) Add new FDW API functions
Adding Join push-down support requires some functions to be added to
FdwRoutine to give control to FDWs.

a) GetForeignJoinPaths()
This allows FDWs to provide alternative join paths for a join
RelOptInfo. This is called from add_paths_to_joinrel() after
considering other join possibilities, and FDW should call add_path()
for each possible foreign join path. Foreign join paths are built
similarly to existing join paths, in a bottom-up manner.

FDWs may push ordered or unordered paths here, but combination of sort
keys would bloat up easily if FDW has no information about efficient
patterns such as remote indexes. FDW should not add too many paths to
prevent exponential overhead of join combination.

b) GetForeignJoinPlan()
This creates ForeignJoin plan node from ForeignJoinPath and other
planner infromation.

c) Executor functions for ForeignJoin plan node
A set of funcitons for executing ForeignJoin plan node is also needed.
Begin/ReScan/Iterate/End are basic operations of a plan node, so we
need to provide them for ForeignJoin node.

Issues
======

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

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

(3) SELECT-with-Join SQL generation in postgres_fdw
Probably Postgres-XC's shipping code would help us for implementing
deparse JOIN SQL, but I've not studied it fully, I'll continue the
study.

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

Perhaps more issues would come out later, but I'd like to get comments
about the design.

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

--
Shigeru HANADA

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

On Thu, Sep 4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> 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.

I am thinking eventually we will need to cache the foreign server
statistics on the local server.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thursday, September 4, 2014, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Sep 4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> > 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.
>
> I am thinking eventually we will need to cache the foreign server
> statistics on the local server.
>
>
>
Wouldn't that lead to issues where the statistics get outdated and we have
to anyways query the foreign server before planning any joins? Or are you
thinking of dropping the foreign table statistics once the foreign join is
complete?

Regards,

Atri

--
Regards,

Atri
*l'apprenant*

On Thu, Sep 4, 2014 at 08:41:43PM +0530, Atri Sharma wrote:
>
>
> On Thursday, September 4, 2014, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Thu, Sep  4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> > 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.
>
> I am thinking eventually we will need to cache the foreign server
> statistics on the local server.
>
>
>
>
> Wouldn't that lead to issues where the statistics get outdated and we have to
> anyways query the foreign server before planning any joins? Or are you thinking
> of dropping the foreign table statistics once the foreign join is complete?

I am thinking we would eventually have to cache the statistics, then get
some kind of invalidation message from the foreign server. I am also
thinking that cache would have to be global across all backends, I guess
similar to our invalidation cache.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Sep 4, 2014 at 9:26 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Sep 4, 2014 at 08:41:43PM +0530, Atri Sharma wrote:
> >
> >
> > On Thursday, September 4, 2014, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> > On Thu, Sep 4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> > > 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.
> >
> > I am thinking eventually we will need to cache the foreign server
> > statistics on the local server.
> >
> >
> >
> >
> > Wouldn't that lead to issues where the statistics get outdated and we
> have to
> > anyways query the foreign server before planning any joins? Or are you
> thinking
> > of dropping the foreign table statistics once the foreign join is
> complete?
>
> I am thinking we would eventually have to cache the statistics, then get
> some kind of invalidation message from the foreign server. I am also
> thinking that cache would have to be global across all backends, I guess
> similar to our invalidation cache.
>
>
>
That could lead to some bloat in storing statistics since we may have a lot
of tables for a lot of foreign servers. Also, will we have VACUUM look at
ANALYZING the foreign tables?

Also, how will we decide that the statistics are invalid? Will we have the
FDW query the foreign server and do some sort of comparison between the
statistics the foreign server has and the statistics we locally have? I am
trying to understand how the idea of invalidation message from foreign
server will work.

Regards,

Atri

On Thu, Sep 4, 2014 at 09:31:20PM +0530, Atri Sharma wrote:
> I am thinking we would eventually have to cache the statistics, then get
> some kind of invalidation message from the foreign server.  I am also
> thinking that cache would have to be global across all backends, I guess
> similar to our invalidation cache.
>
>
>
>
> That could lead to some bloat in storing statistics since we may have a lot of
> tables for a lot of foreign servers. Also, will we have VACUUM look at
> ANALYZING the foreign tables?

> Also, how will we decide that the statistics are invalid? Will we have the FDW
> query the foreign server and do some sort of comparison between the statistics
> the foreign server has and the statistics we locally have? I am trying to
> understand how the idea of invalidation message from foreign server will work.

Well, ANALYZING is running on the foreign server, and somehow it would
be nice if it would send a message to us about its new statistics, or we
can do it like http does and it gives us a last-refresh statistics date
when we connect.

I am not sure how it will work --- I am just suspecting that we might
get to a point where the statistics lookup overhead on the foreign
server might become a bottleneck.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Sep 4, 2014 at 9:33 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Sep 4, 2014 at 09:31:20PM +0530, Atri Sharma wrote:
> > I am thinking we would eventually have to cache the statistics, then
> get
> > some kind of invalidation message from the foreign server. I am also
> > thinking that cache would have to be global across all backends, I
> guess
> > similar to our invalidation cache.
> >
> >
> >
> >
> > That could lead to some bloat in storing statistics since we may have a
> lot of
> > tables for a lot of foreign servers. Also, will we have VACUUM look at
> > ANALYZING the foreign tables?
>
> > Also, how will we decide that the statistics are invalid? Will we have
> the FDW
> > query the foreign server and do some sort of comparison between the
> statistics
> > the foreign server has and the statistics we locally have? I am trying to
> > understand how the idea of invalidation message from foreign server will
> work.
>
> Well, ANALYZING is running on the foreign server, and somehow it would
> be nice if it would send a message to us about its new statistics, or we
> can do it like http does and it gives us a last-refresh statistics date
> when we connect.
>

Not sure how that would work without changing the way ANALYZE works on the
foreign server. http idea could work,though.

>
> I am not sure how it will work --- I am just suspecting that we might
> get to a point where the statistics lookup overhead on the foreign
> server might become a bottleneck.
>

Totally agree, but doing the planning only locally opens the questions I
mentioned above, and also deprives the foreign server database to do any
optimizations that it may want to do (assuming that the foreign database
and postgres query planner do not generate identical plans). This is only
my thought though, we could also be planning better than the foreign server
database, so the optimization part I raised is debatable.

Regards,

Atri

--
Regards,

Atri
*l'apprenant*

On Thu, Sep 4, 2014 at 11:56 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> I am thinking eventually we will need to cache the foreign server
>> statistics on the local server.
>>
>> Wouldn't that lead to issues where the statistics get outdated and we have to
>> anyways query the foreign server before planning any joins? Or are you thinking
>> of dropping the foreign table statistics once the foreign join is complete?
>
> I am thinking we would eventually have to cache the statistics, then get
> some kind of invalidation message from the foreign server. I am also
> thinking that cache would have to be global across all backends, I guess
> similar to our invalidation cache.

Maybe ... but I think this isn't really related to the ostensible
topic of this thread. We can do join pushdown just fine without the
ability to do anything like this.

I'm in full agreement that we should probably have a way to cache some
kind of statistics locally, but making that work figures to be tricky,
because (as I'm pretty sure Tom has pointed out before) there's no
guarantee that the remote side's statistics look anything like
PostgreSQL statistics, and so we might not be able to easily store
them or make sense of them. But it would be nice to at least have the
option to store such statistics if they do happen to be something we
can store and interpret.

It's also coming to seem to me more and more that we need a way to
designate several PostgreSQL machines as a cooperating cluster. This
would mean they'd keep connections to each other open and notify each
other about significant events, which could include "hey, I updated
the statistics on this table, you might want to get the new ones" or
"hey, i've replicated your definition for function X so it's safe to
push it down now" as well as "hey, I have just been promoted to be the
new master" or even automatic negotiation of which of a group of
machines should become the master after a server failure. So far,
we've taken the approach that postgres_fdw is just another FDW which
enjoys no special privileges, and I think that's a good approach on
the whole, but think if we want to create a relatively seamless
multi-node experience as some of the NoSQL databases do, we're going
to need something more than that.

But all of that is a bit pie in the sky, and the join pushdown
improvements we're talking about here don't necessitate any of it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Sep 5, 2014 at 2:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Sep 4, 2014 at 11:56 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >> I am thinking eventually we will need to cache the foreign server
> >> statistics on the local server.
> >>
> >> Wouldn't that lead to issues where the statistics get outdated and we
> have to
> >> anyways query the foreign server before planning any joins? Or are you
> thinking
> >> of dropping the foreign table statistics once the foreign join is
> complete?
> >
> > I am thinking we would eventually have to cache the statistics, then get
> > some kind of invalidation message from the foreign server. I am also
> > thinking that cache would have to be global across all backends, I guess
> > similar to our invalidation cache.
>
> Maybe ... but I think this isn't really related to the ostensible
> topic of this thread. We can do join pushdown just fine without the
> ability to do anything like this.
>
> I'm in full agreement that we should probably have a way to cache some
> kind of statistics locally, but making that work figures to be tricky,
> because (as I'm pretty sure Tom has pointed out before) there's no
> guarantee that the remote side's statistics look anything like
> PostgreSQL statistics, and so we might not be able to easily store
> them or make sense of them. But it would be nice to at least have the
> option to store such statistics if they do happen to be something we
> can store and interpret.
>

I agree that we need local statistics too (full agreement to Bruce's
proposal) but playing the Devil's advocate here and trying to figure how
will things like invalidation and as you mentioned, cross compatibility
work.

>
> It's also coming to seem to me more and more that we need a way to
> designate several PostgreSQL machines as a cooperating cluster. This
> would mean they'd keep connections to each other open and notify each
> other about significant events, which could include "hey, I updated
> the statistics on this table, you might want to get the new ones" or
> "hey, i've replicated your definition for function X so it's safe to
> push it down now" as well as "hey, I have just been promoted to be the
> new master" or even automatic negotiation of which of a group of
> machines should become the master after a server failure.

Thats a brilliant idea, and shouldnt be too much of a problem. One race
condition that is possible is that multiple backend may try to globally
propagate different statistics of the same table, but I think that any
standard logical ordering algorithm should handle that. Also, the automatic
master promotion seems like a brilliant idea and is also great since we
have time tested standard algorithms for that.

One thing I would like to see is that assuming all the interacting nodes do
not have identical schemas, if we can somehow maintain cross node
statistics and use them for planning cross node joins. That would lead to
similar problems as the ones already noted for having local statistics for
foreign databases, but if we solve those anyways for storing local
statistics, we could potentially look at having cross node relation
statistics as well.

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--
Regards,

Atri
*l'apprenant*

(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

(2014/09/05 0:56), Bruce Momjian wrote:> On Thu, Sep 4, 2014 at
08:41:43PM +0530, Atri Sharma wrote:
>> On Thursday, September 4, 2014, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>
>> On Thu, Sep 4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
>> > 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.
>>
>> I am thinking eventually we will need to cache the foreign server
>> statistics on the local server.
>>
>>
>>
>>
>> Wouldn't that lead to issues where the statistics get outdated and
we have to
>> anyways query the foreign server before planning any joins? Or are
you thinking
>> of dropping the foreign table statistics once the foreign join is
complete?
>
> I am thinking we would eventually have to cache the statistics, then get
> some kind of invalidation message from the foreign server. I am also
> thinking that cache would have to be global across all backends, I guess
> similar to our invalidation cache.

If a FDW needs to know more information than pg_statistics and pg_class
have, yes, it should cache some statistics on the local side. But such
statistics would have FDW-specific shape so it would be hard to have API
to manage. FDW can have their own functions and tables to manage their
own statistics, and it can have even background-worker for messaging.
But it would be another story.

Regards,
--
Shigeru HANADA

On Sun, Sep 7, 2014 at 7:07 PM, Shigeru HANADA <shigeru(dot)hanada(at)gmail(dot)com> wrote:
>> 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?

Do we need this? I mean, if you get the FdwRoutine, don't you have
the OID of the FDW or the foreign table also?

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

Definitely.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

I tried some patterns of implementation but I've not gotten feasible
way yet. So I'd like to hear hackers' idea.

* Foreign join hook point
First I thought that following existing cost estimating manner is the
way to go, but I tend to think it doesn't fit foreign joins because
join method is tightly-coupled to sort-ness, but foreign join would
not.

In current planner, add_paths_to_joinrel is conscious of sort-ness,
and functions directly called from it are conscious of join method.
But this seems not fit the abstraction level of FDW. FDW is highly
abstracted, say differ from custom plan providers, so most of work
should be delegated to FDW, including pathkeys consideration, IMO.

Besides that, order of join consideration is another issue. First I
try to add foreign join consideration at the last (after hash join
consideration), but after some thought I noticed that
early-elimination would work better if we try foreign join first,
because in most cases foreign join is the cheapest way to accomplish a
join between two foreign relations.

So currently I'm thinking that delegating whole join consideration to
FDWs before other join consideration in add_paths_to_joinrel, by
calling new FDW API would be promising.

This means that FDWs can add multiple arbitrary paths to RelOptInfo in
a call. Of course this allows FDWs to do round-trip per path, but it
would be optimization issue, and they can compare their own
candidates they can get without round-trip.

* Supported join types
INNER and (LEFT|RIGHT|FULL) OUTER would be safe to push down, even
though some of OUTER JOIN might not be much faster than local join.
I'm not sure that SEMI and ANTI joins are safe to push-down. Can we
leave the matter to FDWs, or should we forbid FDWs pushing down by not
calling foreign join API? Anyway SEMI/ANTI would not be supported in
the first version.

* Blockers of join push-down
Pushing down join means that foreign scans for inner and outer are
skipped, so some elements blocks pushing down. Basically the criteria
is same as scan push-down and update push-down.

After some thoughts, we should check only unsafe expression in join
qual and restrict qual. This limitation is necessary to avoid
difference between results of pushe-down or not. Target list seems to
contain only Var for necessary columns, but we should check that too.

* WIP patch
Attached is WIP patch for reviewing the design. Works should be done
are 1) judging push-down or not, and 2) generating join SQL. For 2),
I'm thinking about referring Postgres-XC's join shipping mechanism.

Any comments or questions are welcome.
--
Shigeru HANADA

Hi hackers,

I'm working on $SUBJECT and would like to get comments about the
design. Attached patch is for the design below. Note that the patch
requires Kaigai-san's custom foriegn join patch[1]

[1] http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F80108C355@BPXM15GP.gisp.nec.co.jp

Joins to be pushed down
=======================
We have two levels of decision about Join push-down, core and FDW. I
think we should allow them to push down joins as much as we can unless
it doesn't break the semantics of join. Anyway FDWs should decide
whether the join can be pushed down or not, on the basis of the FDW's
capability.

Here is the list of checks which should be done in core:

1. Join source relations
All of foreign tables used in a join should be managed by one foreign
data wrapper. I once proposed that all source tables should belong to
one server, because at that time I assumed that FDWs use SERVER to
express physical place of data source. But Robert's comment gave me
an idea that SERVER is not important for some FDWs, so now I think
check about server matching should be done by FDWs.

USER MAPPING is another important attribute of foreign scan/join, and
IMO it should be checked by FDW because some of FDWs don't require
USER MAPPING. If an FDW want to check user mapping, all tables in the
join should belong to the same server and have same
RangeTablEntry#checkAsUser to ensure that only one user mapping is
derived.

2. Join type
Join type can be any, except JOIN_UNIQUE_OUTER and JOIN_UNIQUE_INNER,
though most of FDWs would support only INNER and OUTER.
Pushing down CROSS joins might seem inefficient, because obviously
CROSS JOIN always produces more result than retrieving all rows from
each foreign table separately. However, some FDW might be able to
accelerate such join with cache or something. So I think we should
leave such decision to FDWs.

Here is the list of checks which shold be done in postgres_fdw:

1. Join source relations
As described above, postgres_fdw (and most of SQL-based FDWs) needs to
check that 1) all foreign tables in the join belong to a server, and
2) all foreign tables have same checkAsUser.
In addition to that, I add extra limitation that both inner/outer
should be plain foreign tables, not a result of foreign join. This
limiation makes SQL generator simple. Fundamentally it's possible to
join even join relations, so N-way join is listed as enhancement item
below.

2. Join type
In the first proposal, postgres_fdw allows INNER and OUTER joins to be
pushed down. CROSS, SEMI and ANTI would have much less use cases.

3. Join conditions and WHERE clauses
Join conditions should consist of semantically safe expressions.
Where the "semantically safe" means is same as WHERE clause push-down.

Planned enhancements for 9.5
============================
These features will be proposed as enhancements, hopefully in the 9.5
development cycle, but probably in 9.6.

1. Remove unnecessary column from SELECT clause
Columns which are used for only join conditions can be removed from
the target list, as postgres_fdw does in simple foreign scans.

2. Support N-way joins
Mostly for difficulty of SQL generation, I didn't add support of N-Way joins.

3. Proper cost estimation
Currently postgres_fdw always gives 0 as the cost of a foreign join,
as a compromise. This is because estimating costs of each join
without round-trip (EXPLAIN) is not easy. A rough idea about that I
currently have is to use local statistics, but determining join method
used at remote might require whole planner to run for the join
subtree.

Regards,
--
Shigeru HANADA

Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com> writes:
> I'm working on $SUBJECT and would like to get comments about the
> design. Attached patch is for the design below. Note that the patch
> requires Kaigai-san's custom foriegn join patch[1]

For the record, I'm not particularly on-board with custom scan, and
even less so with custom join. I don't want FDW features like this
depending on those kluges, especially not when you're still in need
of core-code changes (which really points up the inadequacy of those
concepts).

Also, please don't redefine struct NestPath like that. That adds a
whole bunch of notational churn (and backpatch risk) for no value
that I can see. It might've been better to do it like that in a
green field, but you're about twenty years too late to do it now.

regards, tom lane

On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada
<shigeru(dot)hanada(at)gmail(dot)com> wrote:
> I'm working on $SUBJECT and would like to get comments about the
> design. Attached patch is for the design below.

I'm glad you are working on this.

> 1. Join source relations
> As described above, postgres_fdw (and most of SQL-based FDWs) needs to
> check that 1) all foreign tables in the join belong to a server, and
> 2) all foreign tables have same checkAsUser.
> In addition to that, I add extra limitation that both inner/outer
> should be plain foreign tables, not a result of foreign join. This
> limiation makes SQL generator simple. Fundamentally it's possible to
> join even join relations, so N-way join is listed as enhancement item
> below.

It seems pretty important to me that we have a way to push the entire
join nest down. Being able to push down a 2-way join but not more
seems like quite a severe limitation.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hanada-san,

Thanks for proposing this great functionality people waited for.

> On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>
> wrote:
> > I'm working on $SUBJECT and would like to get comments about the
> > design. Attached patch is for the design below.
>
> I'm glad you are working on this.
>
> > 1. Join source relations
> > As described above, postgres_fdw (and most of SQL-based FDWs) needs to
> > check that 1) all foreign tables in the join belong to a server, and
> > 2) all foreign tables have same checkAsUser.
> > In addition to that, I add extra limitation that both inner/outer
> > should be plain foreign tables, not a result of foreign join. This
> > limiation makes SQL generator simple. Fundamentally it's possible to
> > join even join relations, so N-way join is listed as enhancement item
> > below.
>
> It seems pretty important to me that we have a way to push the entire join
> nest down. Being able to push down a 2-way join but not more seems like
> quite a severe limitation.
>
As long as we don't care about simpleness/gracefulness of the remote
query, what we need to do is not complicated. All the optimization jobs
are responsibility of remote system.

Let me explain my thought:
We have three cases to be considered; (1) a join between foreign tables
that is the supported case, (2) a join either of relations is foreign
join, and (3) a join both of relations are foreign joins.

In case of (1), remote query shall have the following form:
SELECT <tlist> FROM FT1 JOIN FT2 ON <cond> WHERE <qual>

In case of (2) or (3), because we already construct inner/outer join,
it is not difficult to replace the FT1 or FT2 above by sub-query, like:
SELECT <tlist> FROM FT3 JOIN
(SELECT <tlist> FROM FT1 JOIN FT2 ON <cond> WHERE <qual>) as FJ1
ON <joincond> WHERE <qual>

How about your thought?

Let me comment on some other points at this moment:

* Enhancement in src/include/foreign/fdwapi.h

It seems to me GetForeignJoinPath_function and GetForeignJoinPlan_function
are not used anywhere. Is it an oversight to remove definitions from your
previous work, isn't it?
Now ForeignJoinPath is added on set_join_pathlist_hook, but not callback of
FdwRoutine.

* Is ForeignJoinPath really needed?

I guess the reason why you added ForeignJoinPath is, to have the fields
of inner_path/outer_path. If we want to have paths of underlying relations,
a straightforward way for the concept (join relations is replaced by
foreign-/custom-scan on a result set of remote join) is enhancement of the
fields in ForeignPath.
How about an idea that adds "List *fdw_subpaths" to save the list of
underlying Path nodes. It also allows to have more than two relations
to be joined.
(Probably, it should be a feature of interface portion. I may have to
enhance my portion.)

* Why NestPath is re-defined?

-typedef JoinPath NestPath;
+typedef struct NestPath
+{
+ JoinPath jpath;
+} NestPath;

It looks to me this change makes patch scale larger...

Best regards,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

Hanada-san,

One other question from my side:
How postgres_fdw tries to solve the varno/varattno mapping when it
replaces relations join by foreign-scan?

Let me explain the issue using an example. If SELECT has a target-
list that references 2nd-column of the inner relation and 2nd-column
of the outer relation, how varno/varattno of ForeignScan shall be
assigned on?
Unless FDW driver does not set fdw_ps_tlist, setrefs.c deals with
this ForeignScan as usual relation scan, then varno of Var will
have non-special varno (even though it may be shifted by rtoffset
in setrefs.c).
Then, ExecEvalScalarVar() is invoked on executor to evaluate the
value of fetched tuple. At that time, which slot and attribute will
be referenced? The varattno of Var-node is neutral on setrefs.c, so
both of the var-nodes that references 2nd-column of the inner relation
and 2nd-column of the outer relation will reference the 2nd-column
of the econtext->ecxt_scantuple, however, it is uncertain which
column of foreign-table is mapped to 2nd-column of the ecxt_scantuple.
So, it needs to inform the planner which underlying column is
mapped to the pseudo scan tlist.

Another expression of what I'm saying is:

SELECT
ft_1.second_col X, --> varno=1 / varattno=2
ft_2.second_col Y --> varno=2 / varattno=2
FROM
ft_1 NATURAL JOIN ft_2;

When FROM-clause is replaced by ForeignScan, the relevant varattno
also needs to be updated, according to the underlying remote query.
If postgres_fdw runs the following remote query, X should have varattno=1
and Y should have varattno=2 on the pseudo scan tlist.
remote: SELECT t_1.second_col, t_2.second_col
FROM t_1 NATURAL JOIN t_2;

You can inform the planner this mapping using fdw_ps_tlist field of
ForeignScan, if FDW driver put a list of TargetEntry.
In above example, fdw_ps_tlist will have two elements and both of then
has Var-node of the underlying foreign tables.

The patch to replace join by foreign-/custom-scan adds a functionality
to fix-up varno/varattno in these cases.

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Kouhei Kaigai
> Sent: Tuesday, December 16, 2014 9:01 AM
> To: Robert Haas; Shigeru Hanada
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] Join push-down support for foreign tables
>
> Hanada-san,
>
> Thanks for proposing this great functionality people waited for.
>
> > On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada
> > <shigeru(dot)hanada(at)gmail(dot)com>
> > wrote:
> > > I'm working on $SUBJECT and would like to get comments about the
> > > design. Attached patch is for the design below.
> >
> > I'm glad you are working on this.
> >
> > > 1. Join source relations
> > > As described above, postgres_fdw (and most of SQL-based FDWs) needs
> > > to check that 1) all foreign tables in the join belong to a server,
> > > and
> > > 2) all foreign tables have same checkAsUser.
> > > In addition to that, I add extra limitation that both inner/outer
> > > should be plain foreign tables, not a result of foreign join. This
> > > limiation makes SQL generator simple. Fundamentally it's possible
> > > to join even join relations, so N-way join is listed as enhancement
> > > item below.
> >
> > It seems pretty important to me that we have a way to push the entire
> > join nest down. Being able to push down a 2-way join but not more
> > seems like quite a severe limitation.
> >
> As long as we don't care about simpleness/gracefulness of the remote query,
> what we need to do is not complicated. All the optimization jobs are
> responsibility of remote system.
>
> Let me explain my thought:
> We have three cases to be considered; (1) a join between foreign tables
> that is the supported case, (2) a join either of relations is foreign join,
> and (3) a join both of relations are foreign joins.
>
> In case of (1), remote query shall have the following form:
> SELECT <tlist> FROM FT1 JOIN FT2 ON <cond> WHERE <qual>
>
> In case of (2) or (3), because we already construct inner/outer join, it
> is not difficult to replace the FT1 or FT2 above by sub-query, like:
> SELECT <tlist> FROM FT3 JOIN
> (SELECT <tlist> FROM FT1 JOIN FT2 ON <cond> WHERE <qual>) as FJ1
> ON <joincond> WHERE <qual>
>
> How about your thought?
>
>
> Let me comment on some other points at this moment:
>
> * Enhancement in src/include/foreign/fdwapi.h
>
> It seems to me GetForeignJoinPath_function and
> GetForeignJoinPlan_function are not used anywhere. Is it an oversight to
> remove definitions from your previous work, isn't it?
> Now ForeignJoinPath is added on set_join_pathlist_hook, but not callback
> of FdwRoutine.
>
>
> * Is ForeignJoinPath really needed?
>
> I guess the reason why you added ForeignJoinPath is, to have the fields
> of inner_path/outer_path. If we want to have paths of underlying relations,
> a straightforward way for the concept (join relations is replaced by
> foreign-/custom-scan on a result set of remote join) is enhancement of the
> fields in ForeignPath.
> How about an idea that adds "List *fdw_subpaths" to save the list of
> underlying Path nodes. It also allows to have more than two relations to
> be joined.
> (Probably, it should be a feature of interface portion. I may have to enhance
> my portion.)
>
> * Why NestPath is re-defined?
>
> -typedef JoinPath NestPath;
> +typedef struct NestPath
> +{
> + JoinPath jpath;
> +} NestPath;
>
> It looks to me this change makes patch scale larger...
>
> Best regards,
> --
> NEC OSS Promotion Center / PG-Strom Project KaiGai Kohei
> <kaigai(at)ak(dot)jp(dot)nec(dot)com>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

2014-12-16 0:45 GMT+09:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com> writes:
>> I'm working on $SUBJECT and would like to get comments about the
>> design. Attached patch is for the design below. Note that the patch
>> requires Kaigai-san's custom foriegn join patch[1]
>
> For the record, I'm not particularly on-board with custom scan, and
> even less so with custom join. I don't want FDW features like this
> depending on those kluges, especially not when you're still in need
> of core-code changes (which really points up the inadequacy of those
> concepts).

This design derived from discussion about custom scan/join. In that
discussion Robert suggested common infrastructure for replacing Join
path with Scan node. Here I agree to user such common infrastructure.
One concern is introducing hook function I/F which seems to break
FdwRoutine I/F boundary...

>
> Also, please don't redefine struct NestPath like that. That adds a
> whole bunch of notational churn (and backpatch risk) for no value
> that I can see. It might've been better to do it like that in a
> green field, but you're about twenty years too late to do it now.

Ok, will revert it.

--
Shigeru HANADA

2014-12-16 1:22 GMT+09:00 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada
> <shigeru(dot)hanada(at)gmail(dot)com> wrote:
>> I'm working on $SUBJECT and would like to get comments about the
>> design. Attached patch is for the design below.
>
> I'm glad you are working on this.
>
>> 1. Join source relations
>> As described above, postgres_fdw (and most of SQL-based FDWs) needs to
>> check that 1) all foreign tables in the join belong to a server, and
>> 2) all foreign tables have same checkAsUser.
>> In addition to that, I add extra limitation that both inner/outer
>> should be plain foreign tables, not a result of foreign join. This
>> limiation makes SQL generator simple. Fundamentally it's possible to
>> join even join relations, so N-way join is listed as enhancement item
>> below.
>
> It seems pretty important to me that we have a way to push the entire
> join nest down. Being able to push down a 2-way join but not more
> seems like quite a severe limitation.

Hmm, I agree to support N-way join is very useful. Postgres-XC's SQL
generator seems to give us a hint for such case, I'll check it out
again.

--
Shigeru HANADA

On Fri, Dec 26, 2014 at 1:48 PM, Shigeru Hanada
<shigeru(dot)hanada(at)gmail(dot)com> wrote:
> Hmm, I agree to support N-way join is very useful. Postgres-XC's SQL
> generator seems to give us a hint for such case, I'll check it out
> again.
Switching to returned with feedback, as this patch is waiting for
feedback for a couple of weeks now.
--
Michael

Hi

I've revised the patch based on Kaigai-san's custom/foreign join patch
posted in the thread below.

http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F80108C355@BPXM15GP.gisp.nec.co.jp

Basically not changed from the version in the last CF, but as Robert
commented before, N-way (not only 2-way) joins should be supported in
the first version by construct SELECT SQL by containing source query
in FROM clause as inline views (a.k.a. from clause subquery).

2014-12-26 13:48 GMT+09:00 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>:
> 2014-12-16 1:22 GMT+09:00 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada
>> <shigeru(dot)hanada(at)gmail(dot)com> wrote:
>>> I'm working on $SUBJECT and would like to get comments about the
>>> design. Attached patch is for the design below.
>>
>> I'm glad you are working on this.
>>
>>> 1. Join source relations
>>> As described above, postgres_fdw (and most of SQL-based FDWs) needs to
>>> check that 1) all foreign tables in the join belong to a server, and
>>> 2) all foreign tables have same checkAsUser.
>>> In addition to that, I add extra limitation that both inner/outer
>>> should be plain foreign tables, not a result of foreign join. This
>>> limiation makes SQL generator simple. Fundamentally it's possible to
>>> join even join relations, so N-way join is listed as enhancement item
>>> below.
>>
>> It seems pretty important to me that we have a way to push the entire
>> join nest down. Being able to push down a 2-way join but not more
>> seems like quite a severe limitation.
>
> Hmm, I agree to support N-way join is very useful. Postgres-XC's SQL
> generator seems to give us a hint for such case, I'll check it out
> again.
>
> --
> Shigeru HANADA

--
Shigeru HANADA

Hanada-san,

Your patch mixtures enhancement of custom-/foreign-scan interface and
enhancement of contrib/postgres_fdw... Probably, it is a careless mis-
operation.
Please make your patch as differences from my infrastructure portion.

Also, I noticed this "Join pushdown support for foreign tables" patch
is unintentionally rejected in the last commit fest.
https://commitfest.postgresql.org/3/20/
I couldn't register myself as reviewer. How do I operate it on the
new commitfest application?

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Shigeru Hanada
> Sent: Monday, February 16, 2015 1:03 PM
> To: Robert Haas
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] Join push-down support for foreign tables
>
> Hi
>
> I've revised the patch based on Kaigai-san's custom/foreign join patch
> posted in the thread below.
>
> http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F80
> 108C355(at)BPXM15GP(dot)gisp(dot)nec(dot)co(dot)jp
>
> Basically not changed from the version in the last CF, but as Robert
> commented before, N-way (not only 2-way) joins should be supported in the
> first version by construct SELECT SQL by containing source query in FROM
> clause as inline views (a.k.a. from clause subquery).
>
> 2014-12-26 13:48 GMT+09:00 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>:
> > 2014-12-16 1:22 GMT+09:00 Robert Haas <robertmhaas(at)gmail(dot)com>:
> >> On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada
> >> <shigeru(dot)hanada(at)gmail(dot)com> wrote:
> >>> I'm working on $SUBJECT and would like to get comments about the
> >>> design. Attached patch is for the design below.
> >>
> >> I'm glad you are working on this.
> >>
> >>> 1. Join source relations
> >>> As described above, postgres_fdw (and most of SQL-based FDWs) needs
> >>> to check that 1) all foreign tables in the join belong to a server,
> >>> and
> >>> 2) all foreign tables have same checkAsUser.
> >>> In addition to that, I add extra limitation that both inner/outer
> >>> should be plain foreign tables, not a result of foreign join. This
> >>> limiation makes SQL generator simple. Fundamentally it's possible
> >>> to join even join relations, so N-way join is listed as enhancement
> >>> item below.
> >>
> >> It seems pretty important to me that we have a way to push the entire
> >> join nest down. Being able to push down a 2-way join but not more
> >> seems like quite a severe limitation.
> >
> > Hmm, I agree to support N-way join is very useful. Postgres-XC's SQL
> > generator seems to give us a hint for such case, I'll check it out
> > again.
> >
> > --
> > Shigeru HANADA
>
>
>
> --
> Shigeru HANADA

Kaigai-san,

Oops. I rebased the patch onto your v4 custom/foreign join patch.
But as you mentioned off-list, I found a flaw about inappropriate
change about NestPath still remains in the patch... I might have made
my dev branch into unexpected state. I'll check it soon.

2015-02-16 13:13 GMT+09:00 Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com>:
> Hanada-san,
>
> Your patch mixtures enhancement of custom-/foreign-scan interface and
> enhancement of contrib/postgres_fdw... Probably, it is a careless mis-
> operation.
> Please make your patch as differences from my infrastructure portion.
>
>
> Also, I noticed this "Join pushdown support for foreign tables" patch
> is unintentionally rejected in the last commit fest.
> https://commitfest.postgresql.org/3/20/
> I couldn't register myself as reviewer. How do I operate it on the
> new commitfest application?
>
> Thanks,
> --
> NEC OSS Promotion Center / PG-Strom Project
> KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>
>
>
>> -----Original Message-----
>> From: pgsql-hackers-owner(at)postgresql(dot)org
>> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Shigeru Hanada
>> Sent: Monday, February 16, 2015 1:03 PM
>> To: Robert Haas
>> Cc: PostgreSQL-development
>> Subject: Re: [HACKERS] Join push-down support for foreign tables
>>
>> Hi
>>
>> I've revised the patch based on Kaigai-san's custom/foreign join patch
>> posted in the thread below.
>>
>> http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F80
>> 108C355(at)BPXM15GP(dot)gisp(dot)nec(dot)co(dot)jp
>>
>> Basically not changed from the version in the last CF, but as Robert
>> commented before, N-way (not only 2-way) joins should be supported in the
>> first version by construct SELECT SQL by containing source query in FROM
>> clause as inline views (a.k.a. from clause subquery).
>>
>> 2014-12-26 13:48 GMT+09:00 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>:
>> > 2014-12-16 1:22 GMT+09:00 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> >> On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada
>> >> <shigeru(dot)hanada(at)gmail(dot)com> wrote:
>> >>> I'm working on $SUBJECT and would like to get comments about the
>> >>> design. Attached patch is for the design below.
>> >>
>> >> I'm glad you are working on this.
>> >>
>> >>> 1. Join source relations
>> >>> As described above, postgres_fdw (and most of SQL-based FDWs) needs
>> >>> to check that 1) all foreign tables in the join belong to a server,
>> >>> and
>> >>> 2) all foreign tables have same checkAsUser.
>> >>> In addition to that, I add extra limitation that both inner/outer
>> >>> should be plain foreign tables, not a result of foreign join. This
>> >>> limiation makes SQL generator simple. Fundamentally it's possible
>> >>> to join even join relations, so N-way join is listed as enhancement
>> >>> item below.
>> >>
>> >> It seems pretty important to me that we have a way to push the entire
>> >> join nest down. Being able to push down a 2-way join but not more
>> >> seems like quite a severe limitation.
>> >
>> > Hmm, I agree to support N-way join is very useful. Postgres-XC's SQL
>> > generator seems to give us a hint for such case, I'll check it out
>> > again.
>> >
>> > --
>> > Shigeru HANADA
>>
>>
>>
>> --
>> Shigeru HANADA

--
Shigeru HANADA