Re: [HACKERS] Join push-down support for foreign tables

2015-08-26 Thread Michael Paquier
On Mon, Mar 16, 2015 at 9:51 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 4, 2015 at 4:26 AM, Shigeru Hanada shigeru.han...@gmail.com wrote: Here is v4 patch of Join push-down support for foreign tables. This patch requires Custom/Foreign join patch v7 posted by Kaigai-san. Hi,

Re: [HACKERS] Join push-down support for foreign tables

2015-03-16 Thread Robert Haas
On Wed, Mar 4, 2015 at 4:26 AM, Shigeru Hanada shigeru.han...@gmail.com wrote: Here is v4 patch of Join push-down support for foreign tables. This patch requires Custom/Foreign join patch v7 posted by Kaigai-san. Hi, I just want to point out to the folks on this thread that the action in this

Re: [HACKERS] Join push-down support for foreign tables

2015-03-13 Thread Ashutosh Bapat
Hi Hanada-san, I noticed that the patch doesn't have any tests for testing FDW join in postgres_fdw. While you are updating the patch, can you please add few tests for the same. I will suggest adding tests for a combination of these dimensions 1. Types of joins 2. Joins between multiple foreign

Re: [HACKERS] Join push-down support for foreign tables

2015-03-09 Thread Kouhei Kaigai
Thanks for finding out what we oversight. Here is still a problem because the new 'relids' field is not updated on setrefs.c (scanrelid is incremented by rtoffset here). It is easy to shift the bitmapset by rtoffset, however, I also would like to see another

Re: [HACKERS] Join push-down support for foreign tables

2015-03-08 Thread Kouhei Kaigai
To: Kaigai Kouhei(海外 浩平) Cc: Shigeru Hanada; Robert Haas; PostgreSQL-development Subject: Re: [HACKERS] Join push-down support for foreign tables Hi Kaigai-san, Hanada-san, Attached please find a patch to print the column names prefixed by the relation names. I haven't tested the patch

Re: [HACKERS] Join push-down support for foreign tables

2015-03-08 Thread Ashutosh Bapat
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat Sent: Friday, March 06, 2015 7:26 PM To: Kaigai Kouhei(海外 浩平) Cc: Shigeru Hanada; Robert Haas; PostgreSQL-development Subject: Re: [HACKERS] Join push-down support for foreign tables Hi Kaigai-san, Hanada-san, Attached

Re: [HACKERS] Join push-down support for foreign tables

2015-03-06 Thread Ashutosh Bapat
Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Shigeru Hanada Sent: Thursday, March 05, 2015 10:00 PM To: Ashutosh Bapat Cc: Kaigai Kouhei(海外 浩平); Robert Haas; PostgreSQL-development Subject: Re: [HACKERS] Join push-down

Re: [HACKERS] Join push-down support for foreign tables

2015-03-05 Thread Kouhei Kaigai
- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Shigeru Hanada Sent: Thursday, March 05, 2015 10:00 PM To: Ashutosh Bapat Cc: Kaigai Kouhei(海外 浩平); Robert Haas; PostgreSQL-development Subject: Re: [HACKERS] Join push-down support

Re: [HACKERS] Join push-down support for foreign tables

2015-03-05 Thread Shigeru Hanada
Here is the v5 patch of Join push-down support for foreign tables. Changes since v4: - Separete remote conditions into ON and WHERE, per Ashutosh. - Add regression test cases for foreign join. - Don't skip reversed relation combination in OUTER join cases. I'm now working on two issues from

Re: [HACKERS] Join push-down support for foreign tables

2015-03-05 Thread Shigeru Hanada
Hi Ashutosh, thanks for the review. 2015-03-04 19:17 GMT+09:00 Ashutosh Bapat ashutosh.ba...@enterprisedb.com: In create_foreignscan_path() we have lines like - 1587 pathnode-path.param_info = get_baserel_parampathinfo(root, rel, 1588 required_outer); Now, that the same function is being

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Kouhei Kaigai
Here is v4 patch of Join push-down support for foreign tables. This patch requires Custom/Foreign join patch v7 posted by Kaigai-san. Thanks for your efforts, In this version I added check about query type which gives up pushing down joins when the join is a part of an underlying query of

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Etsuro Fujita
On 2015/03/03 21:34, Shigeru Hanada wrote: I rebased join push-down patch onto Kaigai-san's Custom/Foreign Join v6 patch. Thanks for the work, Hanada-san and KaiGai-san! Maybe I'm missing something, but did we agree to take this approach, ie, join push-down on top of custom join? There is a

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Kouhei Kaigai
On 2015/03/03 21:34, Shigeru Hanada wrote: I rebased join push-down patch onto Kaigai-san's Custom/Foreign Join v6 patch. Thanks for the work, Hanada-san and KaiGai-san! Maybe I'm missing something, but did we agree to take this approach, ie, join push-down on top of custom join?

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Etsuro Fujita
On 2015/03/04 17:31, Kouhei Kaigai wrote: On 2015/03/03 21:34, Shigeru Hanada wrote: I rebased join push-down patch onto Kaigai-san's Custom/Foreign Join v6 patch. Maybe I'm missing something, but did we agree to take this approach, ie, join push-down on top of custom join? There is a

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Etsuro Fujita
On 2015/03/04 17:57, Shigeru Hanada wrote: 2015-03-04 17:00 GMT+09:00 Etsuro Fujita fujita.ets...@lab.ntt.co.jp: On 2015/03/03 21:34, Shigeru Hanada wrote: I rebased join push-down patch onto Kaigai-san's Custom/Foreign Join v6 patch. but still the patch has an issue about joins underlying

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Shigeru Hanada
2015-03-04 17:00 GMT+09:00 Etsuro Fujita fujita.ets...@lab.ntt.co.jp: On 2015/03/03 21:34, Shigeru Hanada wrote: I rebased join push-down patch onto Kaigai-san's Custom/Foreign Join v6 patch. Thanks for the work, Hanada-san and KaiGai-san! Maybe I'm missing something, but did we agree to

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Shigeru Hanada
Here is v4 patch of Join push-down support for foreign tables. This patch requires Custom/Foreign join patch v7 posted by Kaigai-san. In this version I added check about query type which gives up pushing down joins when the join is a part of an underlying query of UPDATE/DELETE. As of now

Re: [HACKERS] Join push-down support for foreign tables

2015-03-04 Thread Ashutosh Bapat
Hi Hanada-san, I am looking at the patch. Here are my comments In create_foreignscan_path() we have lines like - 1587 pathnode-path.param_info = get_baserel_parampathinfo(root, rel, 1588 required_outer); Now, that the same function is being used for creating foreign scan paths for joins, we

Re: [HACKERS] Join push-down support for foreign tables

2015-03-03 Thread Shigeru Hanada
2015-03-02 23:07 GMT+09:00 Kouhei Kaigai kai...@ak.jp.nec.com: I seem to be getting a problem with whole-row references: # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people p on e.person_id = p.id inner join countries c on p.country_id = c.id; ERROR: table r has 3 columns

Re: [HACKERS] Join push-down support for foreign tables

2015-03-03 Thread Shigeru Hanada
Thanks for reviewing my patch. 2015-03-02 22:50 GMT+09:00 Thom Brown t...@linux.com: I seem to be getting a problem with whole-row references: # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people p on e.person_id = p.id inner join countries c on p.country_id = c.id; ERROR:

Re: [HACKERS] Join push-down support for foreign tables

2015-03-03 Thread Shigeru Hanada
I rebased join push-down patch onto Kaigai-san's Custom/Foreign Join v6 patch. I posted some comments to v6 patch in this post: http://www.postgresql.org/message-id/CAEZqfEcNvjqq-P=jxnw1pb4t9wvpcporcn7g6cc46jgub7d...@mail.gmail.com Before applying my v3 patch, please apply Kaigai-san's v6 patch

Re: [HACKERS] Join push-down support for foreign tables

2015-03-03 Thread Shigeru Hanada
Thanks for the detailed comments. 2015-03-03 18:01 GMT+09:00 Kouhei Kaigai kai...@ak.jp.nec.com: Hanada-san, I checked the patch, below is the random comments from my side. * Context variables --- Sorry, I might give you a wrong suggestion. The foreign_glob_cxt and

Re: [HACKERS] Join push-down support for foreign tables

2015-03-03 Thread Thom Brown
On 3 March 2015 at 12:34, Shigeru Hanada shigeru.han...@gmail.com wrote: I rebased join push-down patch onto Kaigai-san's Custom/Foreign Join v6 patch. I posted some comments to v6 patch in this post:

Re: [HACKERS] Join push-down support for foreign tables

2015-03-03 Thread Kouhei Kaigai
: ##freemail## Re: [HACKERS] Join push-down support for foreign tables Attached is the revised/rebased version of the $SUBJECT. This patch is based on Kaigai-san's custom/foreign join patch, so please apply it before this patch. In this version I changed some points from original postgres_fdw. 1

Re: [HACKERS] Join push-down support for foreign tables

2015-03-03 Thread Kouhei Kaigai
* Bug reported by Thom Brown - # EXPLAIN VERBOSE SELECT NULL FROM (SELECT people.id FROM people INNER JOIN countries ON people.country_id = countries.id LIMIT 3) x; ERROR: could not open relation with OID 0 Sorry, it was a problem caused by my portion. The

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Thom Brown
On 2 March 2015 at 12:48, Shigeru Hanada shigeru.han...@gmail.com wrote: Attached is the revised/rebased version of the $SUBJECT. This patch is based on Kaigai-san's custom/foreign join patch, so please apply it before this patch. In this version I changed some points from original

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Kouhei Kaigai
, March 02, 2015 10:51 PM To: Shigeru Hanada Cc: Kaigai Kouhei(海外 浩平); Robert Haas; PostgreSQL-development Subject: ##freemail## Re: [HACKERS] Join push-down support for foreign tables On 2 March 2015 at 12:48, Shigeru Hanada shigeru.han...@gmail.com wrote: Attached is the revised

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Thom Brown
On 2 March 2015 at 14:07, Kouhei Kaigai kai...@ak.jp.nec.com wrote: I seem to be getting a problem with whole-row references: # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people p on e.person_id = p.id inner join countries c on p.country_id = c.id; ERROR: table r

Re: [HACKERS] Join push-down support for foreign tables

2015-03-02 Thread Shigeru Hanada
Attached is the revised/rebased version of the $SUBJECT. This patch is based on Kaigai-san's custom/foreign join patch, so please apply it before this patch. In this version I changed some points from original postgres_fdw. 1) Disabled SELECT clause optimization ~9.4 postgres_fdw lists only

Re: [HACKERS] Join push-down support for foreign tables

2015-02-18 Thread Shigeru Hanada
2015-02-17 10:39 GMT+09:00 Kouhei Kaigai kai...@ak.jp.nec.com: Let me put some comments in addition to where you're checking now. [design issues] * Cost estimation Estimation and evaluation of cost for remote join query is not an obvious issue. In principle, local side cannot determine the

Re: [HACKERS] Join push-down support for foreign tables

2015-02-16 Thread Kouhei Kaigai
Subject: ##freemail## Re: [HACKERS] Join push-down support for foreign tables 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

Re: [HACKERS] Join push-down support for foreign tables

2015-02-15 Thread Kouhei Kaigai
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.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

Re: [HACKERS] Join push-down support for foreign tables

2015-02-15 Thread Shigeru Hanada
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/9a28c8860f777e439aa12e8aea7694f80108c...@bpxm15gp.gisp.nec.co.jp Basically not changed from the version in the last CF, but as Robert commented before,

Re: [HACKERS] Join push-down support for foreign tables

2015-02-15 Thread Shigeru Hanada
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 108c...@bpxm15gp.gisp.nec.co.jp Basically not changed from the version

Re: [HACKERS] Join push-down support for foreign tables

2015-01-14 Thread Michael Paquier
On Fri, Dec 26, 2014 at 1:48 PM, Shigeru Hanada shigeru.han...@gmail.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

Re: [HACKERS] Join push-down support for foreign tables

2014-12-25 Thread Shigeru Hanada
2014-12-16 0:45 GMT+09:00 Tom Lane t...@sss.pgh.pa.us: Shigeru Hanada shigeru.han...@gmail.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

Re: [HACKERS] Join push-down support for foreign tables

2014-12-25 Thread Shigeru Hanada
2014-12-16 1:22 GMT+09:00 Robert Haas robertmh...@gmail.com: On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada shigeru.han...@gmail.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.

Re: [HACKERS] Join push-down support for foreign tables

2014-12-15 Thread 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]

Re: [HACKERS] Join push-down support for foreign tables

2014-12-15 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.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

Re: [HACKERS] Join push-down support for foreign tables

2014-12-15 Thread Robert Haas
On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada shigeru.han...@gmail.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

Re: [HACKERS] Join push-down support for foreign tables

2014-12-15 Thread Kouhei Kaigai
Hanada-san, Thanks for proposing this great functionality people waited for. On Mon, Dec 15, 2014 at 3:40 AM, Shigeru Hanada shigeru.han...@gmail.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

Re: [HACKERS] Join push-down support for foreign tables

2014-12-15 Thread Kouhei Kaigai
: [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.han...@gmail.com wrote: I'm working on $SUBJECT and would like to get comments about the design

Re: [HACKERS] Join push-down support for foreign tables

2014-10-03 Thread Shigeru Hanada
2014-09-08 8:07 GMT+09:00 Shigeru HANADA shigeru.han...@gmail.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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-09 Thread Robert Haas
On Sun, Sep 7, 2014 at 7:07 PM, Shigeru HANADA shigeru.han...@gmail.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

Re: [HACKERS] Join push-down support for foreign tables

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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-07 Thread 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 br...@momjian.us wrote: On Thu, Sep 4, 2014 at 08:37:08AM -0400, Robert Haas wrote: The main problem I see here is that accurate

Re: [HACKERS] Join push-down support for foreign tables

2014-09-05 Thread Atri Sharma
On Fri, Sep 5, 2014 at 2:20 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Sep 4, 2014 at 11:56 AM, Bruce Momjian br...@momjian.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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Robert Haas
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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Bruce Momjian
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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Atri Sharma
On Thursday, September 4, 2014, Bruce Momjian br...@momjian.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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Bruce Momjian
On Thu, Sep 4, 2014 at 08:41:43PM +0530, Atri Sharma wrote: On Thursday, September 4, 2014, Bruce Momjian br...@momjian.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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Atri Sharma
On Thu, Sep 4, 2014 at 9:26 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Sep 4, 2014 at 08:41:43PM +0530, Atri Sharma wrote: On Thursday, September 4, 2014, Bruce Momjian br...@momjian.us wrote: On Thu, Sep 4, 2014 at 08:37:08AM -0400, Robert Haas wrote: The main

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Bruce Momjian
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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Atri Sharma
On Thu, Sep 4, 2014 at 9:33 PM, Bruce Momjian br...@momjian.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

Re: [HACKERS] Join push-down support for foreign tables

2014-09-04 Thread Robert Haas
On Thu, Sep 4, 2014 at 11:56 AM, Bruce Momjian br...@momjian.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

[HACKERS] Join push-down support for foreign tables

2014-09-03 Thread Shigeru Hanada
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