Re: Asymmetric partition-wise JOIN

2024-08-19 Thread Andrei Lepikhov
On 1/8/2024 20:56, Alexander Korotkov wrote: On Tue, Apr 2, 2024 at 6:07 AM Andrei Lepikhov wrote: Actually, the idea I tried to express is the combination of #1 and #2: to build individual plan for every partition, but consider the 'Common Resources'. Let me explain this a bit more. Thanks fo

Re: Asymmetric partition-wise JOIN

2024-08-01 Thread Alexander Korotkov
On Sun, May 5, 2024 at 5:55 PM Andrei Lepikhov wrote: > On 18/10/2023 16:59, Ashutosh Bapat wrote: > > On Wed, Oct 18, 2023 at 10:55 AM Andrei Lepikhov > >>> The relid is also used to track the scans at executor level. Since we > >>> have so many scans on A, each may be using different plan, we wi

Re: Asymmetric partition-wise JOIN

2024-08-01 Thread Alexander Korotkov
Hi! On Tue, Apr 2, 2024 at 6:07 AM Andrei Lepikhov wrote: > On 15/10/2023 13:25, Alexander Korotkov wrote: > > Great! I'm looking forward to the revised patch. > Revising the code and opinions before restarting this work, I found two > different possible strategies mentioned in the thread: > 1.

Re: Asymmetric partition-wise JOIN

2024-04-01 Thread Andrei Lepikhov
On 15/10/2023 13:25, Alexander Korotkov wrote: Great! I'm looking forward to the revised patch. Revising the code and opinions before restarting this work, I found two different possible strategies mentioned in the thread: 1. 'Common Resources' shares the materialised result of the inner table

Re: Asymmetric partition-wise JOIN

2023-10-18 Thread Andrei Lepikhov
On 18/10/2023 16:59, Ashutosh Bapat wrote: On Wed, Oct 18, 2023 at 10:55 AM Andrei Lepikhov wrote: But the clauses of A parameterized by P will produce different translations for each of the partitions. I think we will need different RelOptInfos (for A) to store these translations. Does the

Re: Asymmetric partition-wise JOIN

2023-10-18 Thread Ashutosh Bapat
On Wed, Oct 18, 2023 at 10:55 AM Andrei Lepikhov wrote: > > > But the clauses of A parameterized by P will produce different > > translations for each of the partitions. I think we will need > > different RelOptInfos (for A) to store these translations. > > Does the answer above resolved this issu

Re: Asymmetric partition-wise JOIN

2023-10-17 Thread Andrei Lepikhov
On 17/10/2023 17:09, Ashutosh Bapat wrote: On Tue, Oct 17, 2023 at 2:05 PM Andrei Lepikhov wrote: On 16/10/2023 23:21, Ashutosh Bapat wrote: On Mon, Oct 16, 2023 at 10:24 AM Andrei Lepikhov Whenever I visited this idea, I hit one issue prominently - how would we differentiate different scans

Re: Asymmetric partition-wise JOIN

2023-10-17 Thread Ashutosh Bapat
On Tue, Oct 17, 2023 at 2:05 PM Andrei Lepikhov wrote: > > On 16/10/2023 23:21, Ashutosh Bapat wrote: > > On Mon, Oct 16, 2023 at 10:24 AM Andrei Lepikhov > > Whenever I visited this idea, I hit one issue prominently - how would > > we differentiate different scans of the non-partitioned relation.

Re: Asymmetric partition-wise JOIN

2023-10-17 Thread Andrei Lepikhov
On 16/10/2023 23:21, Ashutosh Bapat wrote: On Mon, Oct 16, 2023 at 10:24 AM Andrei Lepikhov Whenever I visited this idea, I hit one issue prominently - how would we differentiate different scans of the non-partitioned relation. Normally we do that using different Relids but in this case we wouldn

Re: Asymmetric partition-wise JOIN

2023-10-16 Thread Ashutosh Bapat
On Mon, Oct 16, 2023 at 10:24 AM Andrei Lepikhov wrote: > > > > > Great! I'm looking forward to the revised patch > Before preparing a new patch, it would be better to find the common > ground in the next issue: > So far, this optimization stays aside, proposing an alternative path for > a join R

Re: Asymmetric partition-wise JOIN

2023-10-15 Thread Andrei Lepikhov
On 15/10/2023 17:25, Alexander Korotkov wrote: On Sun, Oct 15, 2023 at 8:40 AM Andrei Lepikhov wrote: Thanks for such detailed feedback! The rationale for this patch was to give the optimizer additional ways to push down more joins into foreign servers. And, because of asynchronous append, the

Re: Asymmetric partition-wise JOIN

2023-10-15 Thread Alexander Korotkov
On Sun, Oct 15, 2023 at 8:40 AM Andrei Lepikhov wrote: > Thanks for such detailed feedback! > The rationale for this patch was to give the optimizer additional ways > to push down more joins into foreign servers. And, because of > asynchronous append, the benefit of that optimization was obvious.

Re: Asymmetric partition-wise JOIN

2023-10-14 Thread Andrei Lepikhov
On 15/10/2023 07:18, Alexander Korotkov wrote: Hi Alexander, Hi Andrey, Thank you for your work on this subject. On Mon, Jan 17, 2022 at 1:42 PM Alexander Pyhalov wrote: The patch does not longer apply cleanly, so I rebased it. Attaching rebased version. Not surprising that the patch doesn'

Re: Asymmetric partition-wise JOIN

2023-10-14 Thread Alexander Korotkov
Hi Alexander, Hi Andrey, Thank you for your work on this subject. On Mon, Jan 17, 2022 at 1:42 PM Alexander Pyhalov wrote: > The patch does not longer apply cleanly, so I rebased it. Attaching > rebased version. Not surprising that the patch doesn't apply after 1.5 years since the last message.

Re: Asymmetric partition-wise JOIN

2022-01-17 Thread Alexander Pyhalov
Andrey Lepikhov писал 2021-09-15 09:31: On 14/9/21 11:37, Andrey V. Lepikhov wrote: Thank you for this good catch! The problem was in the adjust_child_relids_multilevel routine. The tmp_result variable sometimes points to original required_outer. This patch adds new ways which optimizer can gen

Re: Asymmetric partition-wise JOIN

2021-09-14 Thread Andrey Lepikhov
On 14/9/21 11:37, Andrey V. Lepikhov wrote: Thank you for this good catch! The problem was in the adjust_child_relids_multilevel routine. The tmp_result variable sometimes points to original required_outer. This patch adds new ways which optimizer can generate plans. One possible way is optimiz

Re: Asymmetric partition-wise JOIN

2021-09-13 Thread Andrey V. Lepikhov
On 9/9/21 8:38 PM, Jaime Casanova wrote: On Thu, Sep 09, 2021 at 09:50:46AM +, Aleksander Alekseev wrote: It looks like this patch needs to be updated. According to http://cfbot.cputube.org/ it applies but doesn't pass any tests. Changing the status to save time for reviewers. The new sta

Re: Asymmetric partition-wise JOIN

2021-09-09 Thread Jaime Casanova
On Thu, Sep 09, 2021 at 09:50:46AM +, Aleksander Alekseev wrote: > It looks like this patch needs to be updated. According to > http://cfbot.cputube.org/ it applies but doesn't pass any tests. Changing the > status to save time for reviewers. > > The new status of this patch is: Waiting on A

Re: Asymmetric partition-wise JOIN

2021-09-09 Thread Aleksander Alekseev
It looks like this patch needs to be updated. According to http://cfbot.cputube.org/ it applies but doesn't pass any tests. Changing the status to save time for reviewers. The new status of this patch is: Waiting on Author

Re: Asymmetric partition-wise JOIN

2021-07-15 Thread Ibrar Ahmed
On Thu, Jul 15, 2021 at 11:32 AM Andrey Lepikhov wrote: > On 5/7/21 23:15, Zhihong Yu wrote: > > On Mon, Jul 5, 2021 at 2:57 AM Andrey Lepikhov > > mailto:a.lepik...@postgrespro.ru>> wrote: > > +* Can't imagine situation when join relation already > > exists. But in > > +*

Re: Asymmetric partition-wise JOIN

2021-07-14 Thread Andrey Lepikhov
On 5/7/21 23:15, Zhihong Yu wrote: On Mon, Jul 5, 2021 at 2:57 AM Andrey Lepikhov mailto:a.lepik...@postgrespro.ru>> wrote: +            * Can't imagine situation when join relation already exists. But in +            * the 'partition_join' regression test it happens. +            * It may be a

Re: Asymmetric partition-wise JOIN

2021-07-06 Thread Alexander Pyhalov
Andrey Lepikhov писал 2021-07-06 12:28: On 5/7/21 23:15, Zhihong Yu wrote: On Mon, Jul 5, 2021 at 2:57 AM Andrey Lepikhov mailto:a.lepik...@postgrespro.ru>> wrote: +            * Can't imagine situation when join relation already exists. But in +            * the 'partition_join' regression te

Re: Asymmetric partition-wise JOIN

2021-07-06 Thread Andrey Lepikhov
On 5/7/21 23:15, Zhihong Yu wrote: On Mon, Jul 5, 2021 at 2:57 AM Andrey Lepikhov mailto:a.lepik...@postgrespro.ru>> wrote: +            * Can't imagine situation when join relation already exists. But in +            * the 'partition_join' regression test it happens. +            * It may be a

Re: Asymmetric partition-wise JOIN

2021-07-05 Thread Zhihong Yu
On Mon, Jul 5, 2021 at 2:57 AM Andrey Lepikhov wrote: > On 18/6/21 15:02, Alexander Pyhalov wrote: > > Andrey Lepikhov писал 2021-05-27 07:27: > >> Next version of the patch. > >> For searching any problems I forced this patch during 'make check' > >> tests. Some bugs were found and fixed. > > >

Re: Asymmetric partition-wise JOIN

2021-07-05 Thread Andrey Lepikhov
On 18/6/21 15:02, Alexander Pyhalov wrote: Andrey Lepikhov писал 2021-05-27 07:27: Next version of the patch. For searching any problems I forced this patch during 'make check' tests. Some bugs were found and fixed. Hi. I've tested this patch and haven't found issues, but I have some comments.

Re: Asymmetric partition-wise JOIN

2021-06-18 Thread Alexander Pyhalov
Andrey Lepikhov писал 2021-05-27 07:27: Next version of the patch. For searching any problems I forced this patch during 'make check' tests. Some bugs were found and fixed. Hi. I've tested this patch and haven't found issues, but I have some comments. src/backend/optimizer/path/joinrels.c:

Re: Asymmetric partition-wise JOIN

2021-05-26 Thread Andrey Lepikhov
Next version of the patch. For searching any problems I forced this patch during 'make check' tests. Some bugs were found and fixed. -- regards, Andrey Lepikhov Postgres Professional From 101614b504b0b17e201d2375c8af61cfc671e51d Mon Sep 17 00:00:00 2001 From: Andrey Lepikhov Date: Fri, 2 Apr 2

Re: Asymmetric partition-wise JOIN

2021-04-29 Thread Andrey V. Lepikhov
On 11/30/20 7:43 PM, Anastasia Lubennikova wrote: This entry was inactive during this CF, so I've marked it as returned with feedback. Feel free to resubmit an updated version to a future commitfest. I return the patch to commitfest. My current reason differs from reason of origin author. This

Re: Asymmetric partition-wise JOIN

2021-04-09 Thread Andrey V. Lepikhov
On 11/30/20 7:43 PM, Anastasia Lubennikova wrote: This entry was inactive during this CF, so I've marked it as returned with feedback. Feel free to resubmit an updated version to a future commitfest. Attached version is rebased on current master and fixes problems with complex parameterized p

Re: Asymmetric partition-wise JOIN

2020-11-30 Thread Anastasia Lubennikova
On 09.11.2020 13:53, Anastasia Lubennikova wrote: On 21.08.2020 09:02, Andrey V. Lepikhov wrote: On 7/1/20 2:10 PM, Daniel Gustafsson wrote: On 27 Dec 2019, at 08:34, Kohei KaiGai wrote: The attached v2 fixed the problem, and regression test finished correctly. This patch no longer applie

Re: Asymmetric partition-wise JOIN

2020-11-09 Thread Anastasia Lubennikova
On 21.08.2020 09:02, Andrey V. Lepikhov wrote: On 7/1/20 2:10 PM, Daniel Gustafsson wrote: On 27 Dec 2019, at 08:34, Kohei KaiGai wrote: The attached v2 fixed the problem, and regression test finished correctly. This patch no longer applies to HEAD, please submit an rebased version. Markin

Re: Asymmetric partition-wise JOIN

2020-08-26 Thread Amul Sul
On Sat, Aug 24, 2019 at 2:03 PM Kohei KaiGai wrote: > > 2019年8月24日(土) 7:02 Thomas Munro : > > > > On Fri, Aug 23, 2019 at 4:05 AM Kohei KaiGai wrote: > > > We can consider the table join ptable X t1 above is equivalent to: > > > (ptable_p0 + ptable_p1 + ptable_p2) X t1 > > > = (ptable_p0 X t1)

Re: Asymmetric partition-wise JOIN

2020-08-25 Thread Daniel Gustafsson
> On 21 Aug 2020, at 08:02, Andrey V. Lepikhov > wrote: > > On 7/1/20 2:10 PM, Daniel Gustafsson wrote: >>> On 27 Dec 2019, at 08:34, Kohei KaiGai wrote: >>> The attached v2 fixed the problem, and regression test finished correctly. >> This patch no longer applies to HEAD, please submit an reba

Re: Asymmetric partition-wise JOIN

2020-08-20 Thread Andrey V. Lepikhov
On 7/1/20 2:10 PM, Daniel Gustafsson wrote: On 27 Dec 2019, at 08:34, Kohei KaiGai wrote: The attached v2 fixed the problem, and regression test finished correctly. This patch no longer applies to HEAD, please submit an rebased version. Marking the entry Waiting on Author in the meantime.

Re: Asymmetric partition-wise JOIN

2020-07-06 Thread Andrey V. Lepikhov
On 12/27/19 12:34 PM, Kohei KaiGai wrote: The attached v2 fixed the problem, and regression test finished correctly. Using your patch I saw incorrect value of predicted rows at the top node of the plan: "Append (cost=270.02..35165.37 rows=40004 width=16)" Full explain of the query plan see in a

Re: Asymmetric partition-wise JOIN

2020-07-01 Thread Daniel Gustafsson
> On 27 Dec 2019, at 08:34, Kohei KaiGai wrote: > The attached v2 fixed the problem, and regression test finished correctly. This patch no longer applies to HEAD, please submit an rebased version. Marking the entry Waiting on Author in the meantime. cheers ./daniel

Re: Asymmetric partition-wise JOIN

2020-03-27 Thread David Steele
Hi Thomas, On 12/27/19 2:34 AM, Kohei KaiGai wrote: > This crash was reproduced on our environment also. It looks to me adjust_child_relids_multilevel() didn't expect a case when supplied 'relids' (partially) indicate normal and non-partitioned relation. It tries to build a new 'parent_relids' t

Re: Asymmetric partition-wise JOIN

2019-12-26 Thread Kohei KaiGai
Hello, This crash was reproduced on our environment also. It looks to me adjust_child_relids_multilevel() didn't expect a case when supplied 'relids' (partially) indicate normal and non-partitioned relation. It tries to build a new 'parent_relids' that is a set of appinfo->parent_relid related to

Re: Asymmetric partition-wise JOIN

2019-11-30 Thread Michael Paquier
On Sat, Aug 24, 2019 at 05:33:01PM +0900, Kohei KaiGai wrote: > On the other hands, it eventually consumes almost equivalent amount > of memory to load the inner relations, if no leafs are pruned, and if we > could extend the Hash-node to share the hash-table with sibling > join-nodess. The patch

Re: Asymmetric partition-wise JOIN

2019-08-24 Thread Kohei KaiGai
2019年8月24日(土) 7:02 Thomas Munro : > > On Fri, Aug 23, 2019 at 4:05 AM Kohei KaiGai wrote: > > We can consider the table join ptable X t1 above is equivalent to: > > (ptable_p0 + ptable_p1 + ptable_p2) X t1 > > = (ptable_p0 X t1) + (ptable_p1 X t1) + (ptable_p2 X t1) > > It returns an equivalent

Re: Asymmetric partition-wise JOIN

2019-08-23 Thread Thomas Munro
On Fri, Aug 23, 2019 at 4:05 AM Kohei KaiGai wrote: > We can consider the table join ptable X t1 above is equivalent to: > (ptable_p0 + ptable_p1 + ptable_p2) X t1 > = (ptable_p0 X t1) + (ptable_p1 X t1) + (ptable_p2 X t1) > It returns an equivalent result, however, rows are already reduced by H

Re: Asymmetric partition-wise JOIN

2019-08-22 Thread Kohei KaiGai
Hello, Even though nobody has respond the thread, I tried to make a prototype of the asymmetric partition-wise join support. This feature tries to join non-partitioned and partitioned relation before append. See the example below: create table ptable (dist int, a int, b int) partition by hash (d