Re: [HACKERS] Getting sorted data from foreign server for merge join

On Thu, Jan 7, 2016 at 4:05 AM, Ashutosh Bapat wrote: > In get_useful_ecs_for_relation(), while checking whether to use left or > right argument of a mergejoinable operator, the arguments to bms_is_subset() > are passed in reverse order. bms_is_subset() checks

Re: [HACKERS] Getting sorted data from foreign server for merge join

Thanks. On Wed, Dec 23, 2015 at 12:24 AM, Robert Haas wrote: > On Mon, Dec 21, 2015 at 6:34 AM, Ashutosh Bapat > wrote: > >> I went over this patch in some detail today and did a lot of cosmetic > >> cleanup. The results are attached.

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Mon, Dec 21, 2015 at 6:34 AM, Ashutosh Bapat wrote: >> I went over this patch in some detail today and did a lot of cosmetic >> cleanup. The results are attached. I'm fairly happy with this >> version, but let me know what you think. Of course, feedback from

Re: [HACKERS] Getting sorted data from foreign server for merge join

> > I went over this patch in some detail today and did a lot of cosmetic > cleanup. The results are attached. I'm fairly happy with this > version, but let me know what you think. Of course, feedback from > others is more than welcome also. > > Attached patch with some cosmetic changes (listed

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Sat, Dec 19, 2015 at 2:17 AM, Robert Haas wrote: > On Thu, Dec 17, 2015 at 3:32 AM, Ashutosh Bapat > wrote: > > On Wed, Dec 9, 2015 at 12:14 AM, Robert Haas > wrote: > >> On Wed, Dec 2, 2015 at 6:45 AM, Rushabh

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Thu, Dec 17, 2015 at 3:32 AM, Ashutosh Bapat wrote: > On Wed, Dec 9, 2015 at 12:14 AM, Robert Haas wrote: >> On Wed, Dec 2, 2015 at 6:45 AM, Rushabh Lathia >> wrote: >> > Thanks Ashutosh. >> > >> > Re-reviewed

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Wed, Dec 9, 2015 at 12:14 AM, Robert Haas wrote: > On Wed, Dec 2, 2015 at 6:45 AM, Rushabh Lathia > wrote: > > Thanks Ashutosh. > > > > Re-reviewed and Re-verified the patch, pg_sort_all_pd_v5.patch > > looks good to me. > > This patch needs a

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Wed, Dec 2, 2015 at 6:45 AM, Rushabh Lathia wrote: > Thanks Ashutosh. > > Re-reviewed and Re-verified the patch, pg_sort_all_pd_v5.patch > looks good to me. This patch needs a rebase. It's not going to work to say this is a patch proposed for commit when it's still

Re: [HACKERS] Getting sorted data from foreign server for merge join

Thanks Ashutosh. Re-reviewed and Re-verified the patch, pg_sort_all_pd_v5.patch looks good to me. On Fri, Nov 27, 2015 at 3:02 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > Thanks Rushabh for your review and comments. > > On Thu, Nov 26, 2015 at 5:39 PM, Rushabh Lathia

Re: [HACKERS] Getting sorted data from foreign server for merge join

Thanks Rushabh for your review and comments. On Thu, Nov 26, 2015 at 5:39 PM, Rushabh Lathia wrote: > Hi Ashutosh, > > I reviewed your latest version of patch and over all the implementation > and other details look good to me. > > Here are few cosmetic issues which I

Re: [HACKERS] Getting sorted data from foreign server for merge join

Hi Ashutosh, I reviewed your latest version of patch and over all the implementation and other details look good to me. Here are few cosmetic issues which I found: 1) Patch not getting applied cleanly - white space warning 2) -List *usable_pathkeys = NIL; +List

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Thu, Nov 19, 2015 at 7:30 AM, Robert Haas wrote: > On Tue, Nov 17, 2015 at 8:33 AM, Ashutosh Bapat > wrote: > >> Although I'm usually on the side of marking things as extern whenever > >> we find it convenient, I'm nervous about doing

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Tue, Nov 17, 2015 at 8:33 AM, Ashutosh Bapat wrote: >> Although I'm usually on the side of marking things as extern whenever >> we find it convenient, I'm nervous about doing that to >> make_canonical_pathkey(), because it has side effects. Searching the >>

Re: [HACKERS] Getting sorted data from foreign server for merge join

Thanks Robert for your comments. Please see my replies inlined. Updated patch is attached. On Fri, Nov 6, 2015 at 10:02 PM, Robert Haas wrote: > > I think this approach is generally reasonable, but I suggested parts > of it, so may be biased. I would be interested in

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Mon, Nov 9, 2015 at 9:39 PM, Robert Haas wrote: > On Fri, Nov 6, 2015 at 2:03 PM, Kevin Grittner wrote: > > Has anyone taken a close look at what happens if the two sides of > > the merge join have different implementations of the same collation > >

Re: [HACKERS] Getting sorted data from foreign server for merge join

On 16 November 2015 at 17:47, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > > > collations arising from a foreign table's var are considered to be safer > (FDW_COLLATE_SAFE) to push down to the foreign server , since they are > either local default collation or are assumed to be same

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Sat, Nov 7, 2015 at 5:42 PM, Greg Stark wrote: > On Fri, Nov 6, 2015 at 4:54 AM, Ashutosh Bapat > wrote: >> PFA patch to get data sorted from the foreign server (postgres_fdw) >> according to the pathkeys useful for merge join. > > An idle

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Fri, Nov 6, 2015 at 2:03 PM, Kevin Grittner wrote: > Has anyone taken a close look at what happens if the two sides of > the merge join have different implementations of the same collation > name? Is there anything we should do to defend against the > problem? The issue of

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Sat, Nov 7, 2015 at 12:07 PM, Corey Huinker wrote: > Sorry to barge in late, but I was wondering if what we've learned with this > patch can be applied to the case of asserting a sort order on a query > returning from dblink(). Nope. Sorry to the bearer of bad news,

Re: [HACKERS] Getting sorted data from foreign server for merge join

On 2015/11/10 0:56, Robert Haas wrote: > On Sat, Nov 7, 2015 at 12:07 PM, Corey Huinker > wrote: >> Sorry to barge in late, but I was wondering if what we've learned with this >> patch can be applied to the case of asserting a sort order on a query >> returning from

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Fri, Nov 6, 2015 at 11:32 AM, Robert Haas wrote: > On Thu, Nov 5, 2015 at 11:54 PM, Ashutosh Bapat > wrote: > > Hi All, > > PFA patch to get data sorted from the foreign server (postgres_fdw) > > according to the pathkeys useful for

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Fri, Nov 6, 2015 at 4:54 AM, Ashutosh Bapat wrote: > PFA patch to get data sorted from the foreign server (postgres_fdw) > according to the pathkeys useful for merge join. An idle thought. There are going to be a lot of cases where different software systems

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Friday, November 6, 2015 10:32 AM, Robert Haas wrote: > I think this approach is generally reasonable, but I suggested > parts of it, so may be biased. I would be interested in hearing > the opinions of others. Has anyone taken a close look at what happens if the two

Re: [HACKERS] Getting sorted data from foreign server for merge join

On Thu, Nov 5, 2015 at 11:54 PM, Ashutosh Bapat wrote: > Hi All, > PFA patch to get data sorted from the foreign server (postgres_fdw) > according to the pathkeys useful for merge join. > > For a given base relation (extendable to join when that becomes available

[HACKERS] Getting sorted data from foreign server for merge join

Hi All, PFA patch to get data sorted from the foreign server (postgres_fdw) according to the pathkeys useful for merge join. For a given base relation (extendable to join when that becomes available in postgres_fdw), the patch tries to find merge joinable clauses. It then adds paths with pathkeys

Re: [HACKERS] Getting sorted data from foreign server

On Fri, Oct 30, 2015 at 6:19 AM, Ashutosh Bapat wrote: > If there is a collate clause in the ORDER BY, the server crashes with > assertion > +Assert(loc_cxt.state == FDW_COLLATE_NONE || > +loc_cxt.state == FDW_COLLATE_SAFE); > > > The assertion is

Re: [HACKERS] Getting sorted data from foreign server

On Tue, Nov 3, 2015 at 11:35 PM, Robert Haas wrote: > On Fri, Oct 30, 2015 at 6:19 AM, Ashutosh Bapat > wrote: > > If there is a collate clause in the ORDER BY, the server crashes with > > assertion > > +Assert(loc_cxt.state ==

Re: [HACKERS] Getting sorted data from foreign server

If there is a collate clause in the ORDER BY, the server crashes with assertion +Assert(loc_cxt.state == FDW_COLLATE_NONE || +loc_cxt.state == FDW_COLLATE_SAFE); The assertion is fine as long as is_foreign_expr() tests only boolean expressions (appearing in quals). This patch

Re: [HACKERS] Getting sorted data from foreign server

On Tue, Oct 27, 2015 at 6:44 PM, Fabrízio de Royes Mello < fabriziome...@gmail.com> wrote: > > > On Tue, Oct 27, 2015 at 5:26 AM, Ashutosh Bapat < > ashutosh.ba...@enterprisedb.com> wrote: > > > > > > > > On Fri, Oct 23, 2015 at 2:43 AM, Robert Haas > wrote: > >> > >> On

Re: [HACKERS] Getting sorted data from foreign server

On Fri, Oct 23, 2015 at 2:43 AM, Robert Haas wrote: > On Wed, Oct 21, 2015 at 5:23 AM, Ashutosh Bapat > wrote: > > Increasing the sorting cost factor (when use_remote_estimates = false) > from > > 1.1 to 1.2 makes the difference disappear.

Re: [HACKERS] Getting sorted data from foreign server

On Tue, Oct 27, 2015 at 5:26 AM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > > > > On Fri, Oct 23, 2015 at 2:43 AM, Robert Haas wrote: >> >> On Wed, Oct 21, 2015 at 5:23 AM, Ashutosh Bapat >> wrote: >> > Increasing the

Re: [HACKERS] Getting sorted data from foreign server

On Wed, Oct 21, 2015 at 5:23 AM, Ashutosh Bapat wrote: > Increasing the sorting cost factor (when use_remote_estimates = false) from > 1.1 to 1.2 makes the difference disappear. > > Since the startup costs for postgres_fdw are large portion of total cost, > extra

Re: [HACKERS] Getting sorted data from foreign server

Here's patch with the regression fixed. On Wed, Oct 21, 2015 at 2:53 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > > > On Fri, Oct 16, 2015 at 11:33 PM, Robert Haas > wrote: > >> On Thu, Oct 15, 2015 at 6:28 AM, Ashutosh Bapat >>

Re: [HACKERS] Getting sorted data from foreign server

On Fri, Oct 16, 2015 at 11:33 PM, Robert Haas wrote: > On Thu, Oct 15, 2015 at 6:28 AM, Ashutosh Bapat > wrote: > > Attached is the patch which takes care of above comments. > > I spent some time on this patch today. But it's still not

Re: [HACKERS] Getting sorted data from foreign server

On Thu, Oct 15, 2015 at 6:28 AM, Ashutosh Bapat wrote: > Attached is the patch which takes care of above comments. I spent some time on this patch today. But it's still not right. I've attached a new version which fixes a serious problem with your last version

Re: [HACKERS] Getting sorted data from foreign server

On Thu, Oct 15, 2015 at 2:27 AM, Robert Haas wrote: > On Wed, Oct 14, 2015 at 7:30 AM, Ashutosh Bapat > wrote: > > The patch uses a factor of 1.1 (10% increase) to multiple the startup and > > total costs in fpinfo for unsorted data. > > >

Re: [HACKERS] Getting sorted data from foreign server

On Wed, Oct 14, 2015 at 7:30 AM, Ashutosh Bapat wrote: > The patch uses a factor of 1.1 (10% increase) to multiple the startup and > total costs in fpinfo for unsorted data. > > This change has caused the plans for few queries in the test postgres_fdw to > change.

Re: [HACKERS] Getting sorted data from foreign server

PFA the patch with all the comments addressed. On Tue, Oct 13, 2015 at 10:07 PM, Robert Haas wrote: > On Tue, Oct 13, 2015 at 3:29 AM, Ashutosh Bapat > wrote: > >> - You consider pushing down ORDER BY if any prefix of the query > >>

Re: [HACKERS] Getting sorted data from foreign server

> On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas wrote: > >> >> In the interest of full disclosure, I asked Ashutosh to work on this >> patch and have discussed the design with him several times. I believe >> that this is a good direction for PostgreSQL to be going. It's >>

Re: [HACKERS] Getting sorted data from foreign server

On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas wrote: > On Tue, Oct 6, 2015 at 6:46 AM, Ashutosh Bapat > wrote: > > standard_qp_callback() sets root->query_pathkeys to pathkeys on which the > > result of query_planner are expected be sorted

Re: [HACKERS] Getting sorted data from foreign server

On Tue, Oct 13, 2015 at 1:48 PM, Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > > On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas wrote: >> >>> >>> In the interest of full disclosure, I asked Ashutosh to work on this >>> patch and have discussed the design with him

Re: [HACKERS] Getting sorted data from foreign server

On Tue, Oct 13, 2015 at 3:29 AM, Ashutosh Bapat wrote: >> - You consider pushing down ORDER BY if any prefix of the query >> pathkeys satisfy is_foreign_expr(), but that doesn't seem right to me. >> If the user says SELECT * FROM remotetab ORDER BY a, unsafe(a),

Re: [HACKERS] Getting sorted data from foreign server

On Tue, Oct 6, 2015 at 6:46 AM, Ashutosh Bapat wrote: > standard_qp_callback() sets root->query_pathkeys to pathkeys on which the > result of query_planner are expected be sorted upon (see the function for > more details). The patch checks if any prefix of these

Re: [HACKERS] Getting sorted data from foreign server

On 08/10/15 17:09, Robert Haas wrote: > - You consider pushing down ORDER BY if any prefix of the query > pathkeys satisfy is_foreign_expr(), but that doesn't seem right to me. > If the user says SELECT * FROM remotetab ORDER BY a, unsafe(a), > ordering the result set by a doesn't help us much.

Re: [HACKERS] Getting sorted data from foreign server

On Thu, Oct 8, 2015 at 3:04 PM, Jeremy Harris wrote: > That depends how often the additional columns affect the sorted > order, if the sort method takes advantage of sorted input. What I'm saying is - ours doesn't. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The

[HACKERS] Getting sorted data from foreign server

Hi All, standard_qp_callback() sets root->query_pathkeys to pathkeys on which the result of query_planner are expected be sorted upon (see the function for more details). The patch checks if any prefix of these pathkeys can be entirely evaluated using the foreign relation and at the foreign server