Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Aug 10, 2017 at 6:23 PM, Ashutosh Bapat wrote: > Your patch didn't improve planning time without partition-wise join, > so it's something good to have along-with partition-wise join. Given > that Bitmapsets are used in other parts of code as well, the >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Aug 10, 2017 at 9:28 AM, Thomas Munro wrote: > On Thu, Aug 10, 2017 at 1:39 AM, Thomas Munro > wrote: >> On my computer it took ~1.5 seconds to plan a 1000 partition join, >> ~7.1 seconds to plan a 2000 partition join, and ~50

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Aug 10, 2017 at 1:39 AM, Thomas Munro wrote: > On my computer it took ~1.5 seconds to plan a 1000 partition join, > ~7.1 seconds to plan a 2000 partition join, and ~50 seconds to plan a > 4000 partition join. I poked around in a profiler a bit and saw that

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Aug 8, 2017 at 8:51 PM, Ashutosh Bapat wrote: > Updated patches attached. Hi, I started reviewing this. It's nicely commented, but it's also very complicated, and it's going to take me several rounds to understand what all the parts do, but here's some

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Aug 3, 2017 at 9:38 PM, Ashutosh Bapat wrote: > On Thu, Aug 3, 2017 at 2:10 AM, Robert Haas wrote: >> On Mon, Jul 31, 2017 at 7:59 AM, Ashutosh Bapat >> wrote: >>> Adding AppendRelInfos to

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Aug 3, 2017 at 2:10 AM, Robert Haas wrote: > On Mon, Jul 31, 2017 at 7:59 AM, Ashutosh Bapat > wrote: >> Adding AppendRelInfos to root->append_rel_list as and when they are >> created would keep parent AppendRelInfos before those of

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Jul 31, 2017 at 9:07 AM, Ashutosh Bapat wrote: > Forgot the patch set. Here it is. The commit message for 0005 isn't really accurate given that it follows 0004. I think you could just flatten 0005 and 0006 into one patch. Reviewing those together: -

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Jul 31, 2017 at 7:59 AM, Ashutosh Bapat wrote: > Adding AppendRelInfos to root->append_rel_list as and when they are > created would keep parent AppendRelInfos before those of children. But > that function throws away the AppendRelInfo it created when

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Jul 31, 2017 at 8:32 AM, Robert Haas wrote: > On Fri, Jul 14, 2017 at 3:02 AM, Ashutosh Bapat > wrote: >> Here's revised patch set with only 0004 revised. That patch deals with >> creating multi-level inheritance hierarchy from

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Jul 14, 2017 at 3:02 AM, Ashutosh Bapat wrote: > Here's revised patch set with only 0004 revised. That patch deals with > creating multi-level inheritance hierarchy from multi-level partition > hierarchy. The original logic of recursively calling >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 26, 2017 at 12:02 PM, Ashutosh Bapat wrote: > > Ok. If those queries have equi-join between partitioned tables and are > not picking up partition-wise join, that case needs to be > investigated. Q21 for example has join between three lineitem >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 26, 2017 at 10:38 AM, Ashutosh Bapat wrote: > > On Tue, Jul 25, 2017 at 9:39 PM, Dilip Kumar wrote: > > On Tue, Jul 25, 2017 at 8:59 PM, Robert Haas wrote: > >> On Tue, Jul 25, 2017 at 1:31 AM, Rafia

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 26, 2017 at 11:08 AM, Rafia Sabih wrote: > > > On Wed, Jul 26, 2017 at 11:06 AM, Ashutosh Bapat > wrote: >> >> On Wed, Jul 26, 2017 at 11:00 AM, Rafia Sabih >> wrote: >> > >> > >> > On Wed,

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 26, 2017 at 11:06 AM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > On Wed, Jul 26, 2017 at 11:00 AM, Rafia Sabih > wrote: > > > > > > On Wed, Jul 26, 2017 at 10:58 AM, Ashutosh Bapat > > wrote: > >> > >> On

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 26, 2017 at 11:00 AM, Rafia Sabih wrote: > > > On Wed, Jul 26, 2017 at 10:58 AM, Ashutosh Bapat > wrote: >> >> On Tue, Jul 25, 2017 at 11:01 AM, Rafia Sabih >> wrote: >> >> > Query plans for

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 26, 2017 at 10:58 AM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > On Tue, Jul 25, 2017 at 11:01 AM, Rafia Sabih > wrote: > > > Query plans for the above mentioned queries is attached. > > > > Can you please share plans for all the queries,

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Jul 25, 2017 at 11:01 AM, Rafia Sabih wrote: > Query plans for the above mentioned queries is attached. > Can you please share plans for all the queries, even if they haven't chosen partition-wise join when run on partitioned tables with

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Jul 25, 2017 at 9:39 PM, Dilip Kumar wrote: > On Tue, Jul 25, 2017 at 8:59 PM, Robert Haas wrote: >> On Tue, Jul 25, 2017 at 1:31 AM, Rafia Sabih >> wrote: >>> - other queries show a good 20-30% improvement in

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Jul 25, 2017 at 8:59 PM, Robert Haas wrote: > On Tue, Jul 25, 2017 at 1:31 AM, Rafia Sabih > wrote: >> - other queries show a good 20-30% improvement in performance. Performance >> numbers are as follows, >> >> Query| un_part_head

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Jul 25, 2017 at 1:31 AM, Rafia Sabih wrote: > - other queries show a good 20-30% improvement in performance. Performance > numbers are as follows, > > Query| un_part_head (seconds) | part_head (seconds) | part_patch (seconds) | > 3 | 76 |127 | 88 | > 4 |17 |

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Jul 21, 2017 at 11:54 AM, Rafia Sabih wrote: > So, does this > also mean that a partitioned table will not join with an unpartitioned > table without append of partitions? > Yes. When you join an unpartitioned table with a partitioned table, the planner will

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Jul 20, 2017 at 2:44 PM, Ashutosh Bapat wrote: > > On Thu, Jul 20, 2017 at 11:46 AM, Amit Langote > wrote: > > On 2017/07/20 15:05, Ashutosh Bapat wrote: > >> On Wed, Jul 19, 2017 at 9:54 AM, Rafia Sabih > >>

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Jul 21, 2017 at 11:42 AM, Rafia Sabih wrote: > > Following the discussion at [1], with the patch Thomas posted there, now Q21 > completes in some 160 seconds. Your earlier reports mentioned unpartitioned case taking 300 seconds, partitioned case without

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Jul 20, 2017 at 8:53 AM, Thomas Munro wrote: > On Thu, Jul 20, 2017 at 2:02 PM, Robert Haas > wrote: > > On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro > > wrote: > >> Isn't this the same as the issue

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Jul 20, 2017 at 11:46 AM, Amit Langote wrote: > On 2017/07/20 15:05, Ashutosh Bapat wrote: >> On Wed, Jul 19, 2017 at 9:54 AM, Rafia Sabih >> wrote: >>> >>> Partition information: >>> Type of partitioning - single column range

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Jul 20, 2017 at 12:30 AM, Robert Haas wrote: > >> This suggests that partitioning is not a suitable strategy for this query, >> but then may be partition wise should not be picked for such a case to >> aggravate the performance issue. > > In the unpartitioned case,

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On 2017/07/20 15:05, Ashutosh Bapat wrote: > On Wed, Jul 19, 2017 at 9:54 AM, Rafia Sabih > wrote: >> >> Partition information: >> Type of partitioning - single column range partition >> Tables partitioned - Lineitem and orders >> >> Lineitem - >> Partition key =

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 19, 2017 at 9:54 AM, Rafia Sabih wrote: > > Partition information: > Type of partitioning - single column range partition > Tables partitioned - Lineitem and orders > > Lineitem - > Partition key = l_orderkey > No of partitions = 18 > > Orders - >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Jul 20, 2017 at 2:02 PM, Robert Haas wrote: > On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro > wrote: >> Isn't this the same as the issue reported here? >> >>

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 19, 2017 at 7:45 PM, Thomas Munro wrote: > On Thu, Jul 20, 2017 at 7:00 AM, Robert Haas wrote: >> I think the problem is that the row count estimates for the child >> joins seem to be totally bogus: >> >> -> Hash Semi Join

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Jul 20, 2017 at 7:00 AM, Robert Haas wrote: > I think the problem is that the row count estimates for the child > joins seem to be totally bogus: > > -> Hash Semi Join (cost=309300.53..491665.60 rows=1 width=12) > (actual time=10484.422..15945.851 rows=1523493

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Jul 19, 2017 at 12:24 AM, Rafia Sabih wrote: > On testing this patch for TPC-H (for scale factor 20) benchmark I found a > regression for Q21, on head it was taking some 600 seconds and with this > patch it is taking 3200 seconds. This comparison is on the

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Jun 30, 2017 at 2:53 PM, Rafia Sabih wrote: > > > On Mon, May 22, 2017 at 12:02 PM, Ashutosh Bapat > wrote: >> >> >> Here's set of patches rebased on latest head. > > > In an attempt to test this set of patches, I found that

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, May 22, 2017 at 12:02 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > > Here's set of patches rebased on latest head. > In an attempt to test this set of patches, I found that not all of the patches could be applied on latest head-- commit

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Sat, Apr 29, 2017 at 12:37 AM, Robert Haas wrote: > On Fri, Apr 28, 2017 at 1:18 AM, Ashutosh Bapat > wrote: >> For two-way join this works and is fairly straight-forward. I am >> assuming that A an B are base relations and not joins.

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, May 18, 2017 at 4:38 AM, Ashutosh Bapat wrote: > But in case a user has written an = operator which returns true for > two NULL values, per description in [1], that comparison operator is > flawed and > using that operator is going to result in

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Apr 6, 2017 at 6:37 AM, Robert Haas wrote: > >> There's a relevant comment in 0006, build_joinrel_partition_info() >> (probably that name needs to change, but I will do that once we have >> settled on design) >> + /* >> +* Construct partition keys for the

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Apr 28, 2017 at 1:18 AM, Ashutosh Bapat wrote: > For two-way join this works and is fairly straight-forward. I am > assuming that A an B are base relations and not joins. But making it > work for N-way join is the challenge. I don't think it's much

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Apr 28, 2017 at 1:32 AM, Robert Haas wrote: > On Thu, Apr 27, 2017 at 3:41 AM, Ashutosh Bapat > wrote: >> The third goal requires that the partition bounds be compared based on >> the partition keys present in the equi-join. While

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Apr 27, 2017 at 3:41 AM, Ashutosh Bapat wrote: > The third goal requires that the partition bounds be compared based on > the partition keys present in the equi-join. While matching the > partitions to be joined, the partition bounds corresponding the base

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Apr 26, 2017 at 9:30 PM, Robert Haas wrote: > On Mon, Apr 24, 2017 at 7:06 AM, Ashutosh Bapat > wrote: >> This assumes that datums in partition bounds have one to one mapping >> with the partitions, which isn't true for list

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Apr 26, 2017 at 12:41 PM, Tom Lane wrote: > Robert Haas writes: >> So this is about a cross-type join, >> not multiple types within a single partitioning hierarchy, as you >> might also gather from the subject line of this thread. > > OK, but I

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

Robert Haas writes: > So this is about a cross-type join, > not multiple types within a single partitioning hierarchy, as you > might also gather from the subject line of this thread. OK, but I still don't understand why any type conversion is needed in such a case. The

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Apr 26, 2017 at 12:19 PM, Tom Lane wrote: > What I'm going to ask one more time, though, is why we are even discussing > this. Surely the partition bounds of a partitioned table must all be of > the same type already. If there is a case where they are not, that is >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

Robert Haas writes: > I'm going to say this one more time: I really, really, really think > you need to avoid trying to convert the partition bounds to a common > type. I said before that the infrastructure to do that is not present > in our type system, and I'm pretty

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Apr 24, 2017 at 7:06 AM, Ashutosh Bapat wrote: > This assumes that datums in partition bounds have one to one mapping > with the partitions, which isn't true for list partitions. For list > partitions we have multiple datums corresponding to the items

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Apr 24, 2017 at 5:02 PM, Rajkumar Raghuwanshi wrote: > On Fri, Apr 21, 2017 at 7:59 PM, Ashutosh Bapat > wrote: >> >> Here's an updated patch set > > > Hi, > > I have applied v18 patches and got a crash in m-way

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Apr 21, 2017 at 7:59 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > Here's an updated patch set > Hi, I have applied v18 patches and got a crash in m-way joins when partition ranges differ, below are steps to reproduce this. CREATE TABLE prt1 (a int, b int, c varchar)

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Sat, Apr 22, 2017 at 3:52 AM, Robert Haas wrote: > On Fri, Apr 21, 2017 at 8:41 AM, Ashutosh Bapat > wrote: >> I don't see how is that fixed. For a join relation we need to come up >> with one set of partition bounds by merging partition

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Apr 21, 2017 at 8:41 AM, Ashutosh Bapat wrote: > I don't see how is that fixed. For a join relation we need to come up > with one set of partition bounds by merging partition bounds of the > joining relation and in order to understand how to interpret the

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Apr 21, 2017 at 1:34 AM, Robert Haas wrote: >> You seem to be suggesting that we keep as many sets of >> partition bounds as there are base relations participating in the join >> and then use appropriate partition bounds based on the columns in the >> join

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Apr 20, 2017 at 8:45 AM, Ashutosh Bapat wrote: > I think you are confusing join condition application and partition > bounds of a join relation. You're right, I misunderstood what you were talking about. > But the problem we are trying to solve here

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Apr 20, 2017 at 3:35 PM, Amit Langote wrote: > On 2017/04/20 15:45, Ashutosh Bapat wrote: >> On Thu, Apr 20, 2017 at 10:42 AM, Robert Haas wrote: >>> I don't understand why you think that partition-wise join needs any >>> new logic

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On 2017/04/20 15:45, Ashutosh Bapat wrote: > On Thu, Apr 20, 2017 at 10:42 AM, Robert Haas wrote: >> I don't understand why you think that partition-wise join needs any >> new logic here; if this were a non-partitionwise join, we'd similarly >> need to use the correct

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Apr 20, 2017 at 10:42 AM, Robert Haas wrote: > On Tue, Apr 18, 2017 at 6:55 AM, Ashutosh Bapat > wrote: >> When we merge partition bounds from two relations with different >> partition key types, the merged partition bounds need to

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Apr 20, 2017 at 11:32 AM, Tom Lane wrote: > > BTW, I remain totally mystified as to what people think the semantics of > partitioning ought to be. Child columns can have a different type from > parent columns? Really? Why is this even under discussion? We don't >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

Robert Haas writes: > I don't understand why you think that partition-wise join needs any > new logic here; if this were a non-partitionwise join, we'd similarly > need to use the correct operator, but the existing code handles that > just fine. If the join is performed

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Apr 18, 2017 at 6:55 AM, Ashutosh Bapat wrote: > When we merge partition bounds from two relations with different > partition key types, the merged partition bounds need to have some > information abound the way those constants look like e.g. their >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Apr 5, 2017 at 8:39 AM, Robert Haas wrote: > On Tue, Apr 4, 2017 at 10:22 AM, Ashutosh Bapat > wrote: >> Yes, I agree. For an inner join, the partition key types need to "shrink" >> and for outer join they need to be "widened". I

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Apr 6, 2017 at 6:37 AM, Robert Haas wrote: > On Wed, Apr 5, 2017 at 2:42 AM, Ashutosh Bapat > wrote: >> Only inner join conditions have equivalence classes associated with >> those. Outer join conditions create single element

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

Robert Haas writes: > ... But the underlying point here is that > the only thing you really know about the function is that it's got to > be a strategy-3 operator in some btree opclass; if that guarantees > strictness, then so be it -- but I wasn't able to find anything in

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Apr 5, 2017 at 2:42 AM, Ashutosh Bapat wrote: > Only inner join conditions have equivalence classes associated with > those. Outer join conditions create single element equivalence > classes. So, we can not associate equivalence classes as they are with >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Apr 5, 2017 at 8:39 AM, Robert Haas wrote: > On Tue, Apr 4, 2017 at 10:22 AM, Ashutosh Bapat > wrote: >> Yes, I agree. For an inner join, the partition key types need to "shrink" >> and for outer join they need to be "widened". I

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Apr 4, 2017 at 10:22 AM, Ashutosh Bapat wrote: > Yes, I agree. For an inner join, the partition key types need to "shrink" > and for outer join they need to be "widened". I don't know if there is a way > to know "wider" or "shorter" of two given types. We

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Mar 30, 2017 at 1:14 AM, Ashutosh Bapat wrote: > Done. Ashutosh and I spent several hours discussing this patch set today. I'm starting to become concerned about the fact that 0004 makes the partition bounds part of the PartitionScheme, because that means

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Mar 30, 2017 at 6:32 AM, Amit Langote wrote: > So, because we want to create an Append path for each partitioned table in > a tree separately, we'll need RelOptInfo for each one, which in turn > requires an AppendRelInfo. Hmm. It's no more desirable to

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On 2017/03/30 18:35, Ashutosh Bapat wrote: >> On Wed, Mar 29, 2017 at 8:39 AM, Robert Haas wrote: >> I don't think 0011 is likely to be acceptable in current form. I >> can't imagine that we just went to the trouble of getting rid of >> AppendRelInfos for child partitioned

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

> On Wed, Mar 29, 2017 at 8:39 AM, Robert Haas wrote: > I don't think 0011 is likely to be acceptable in current form. I > can't imagine that we just went to the trouble of getting rid of > AppendRelInfos for child partitioned rels only to turn around and put > them back

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Mar 28, 2017 at 10:24 PM, Robert Haas wrote: > On Mon, Mar 27, 2017 at 8:36 AM, Ashutosh Bapat > wrote: >> I have gone through the patch, and it looks good to me. Here's the set >> of patches with this patch included. Fixed the

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Mar 28, 2017 at 12:54 PM, Robert Haas wrote: > Regarding 0002, I think the parts that involve factoring out > find_param_path_info() are uncontroversial. Regarding the changes to > adjust_appendrel_attrs(), my main question is whether we wouldn't be > better off

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 27, 2017 at 8:36 AM, Ashutosh Bapat wrote: > I have gone through the patch, and it looks good to me. Here's the set > of patches with this patch included. Fixed the testcase failures. > Rebased the patchset on de4da168d57de812bb30d359394b7913635d21a9.

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

Hi Ashutosh, On 2017/03/23 21:48, Ashutosh Bapat wrote: >>> I have fixed all the issues reported till now. I've tried to fix your 0012 patch (Multi-level partitioned table expansion) considering your message earlier on this thread [1]. Especially the fact that no AppendRelInfo and RelOptInfo are

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

Hi Ashutosh, On 2017/03/23 21:48, Ashutosh Bapat wrote: >>> I have fixed all the issues reported till now. In patch 0007, the following code in have_partkey_equi_join() looks potentially unsafe: /* * If the clause refers to different partition keys from * both

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

> >> I don't think we will get away by supporting just scan paths, since >> the inner side of lateral join can be any paths not just scan path. Or >> you are suggesting that we disable partition-wise lateral join and >> support reparameterization of only scan paths? > > I think if you can do a

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Mar 22, 2017 at 8:46 AM, Ashutosh Bapat wrote: > Here's set of updated patches rebased on > 1148e22a82edc96172fc78855da392b6f0015c88. > > I have fixed all the issues reported till now. I don't understand why patch 0001 ends up changing every existing test

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Mar 22, 2017 at 9:59 AM, Ashutosh Bapat wrote: >> In my testing last week, reparameterize_path_by_child() was essential >> for nested loops to work properly, even without LATERAL. Without it, >> the parameterized path ends up containing vars that

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Mar 22, 2017 at 6:32 PM, Robert Haas wrote: > On Wed, Mar 22, 2017 at 8:46 AM, Ashutosh Bapat > wrote: >> I have also completed reparameterize_path_by_child() for all the >> required paths. There's no TODO there now. :) The function

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Mar 22, 2017 at 8:46 AM, Ashutosh Bapat wrote: > I have also completed reparameterize_path_by_child() for all the > required paths. There's no TODO there now. :) The function has grown > quite long now and might take some time to review. Given the size, I

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

> > I tested the applied patch, it is fixing the reported issue. Thanks for the confirmation Rafia. I have included the fix in the latest set of patches. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Wed, Mar 22, 2017 at 3:19 PM, Ashutosh Bapat wrote: >> In an attempt to test the geqo side of this patch, I reduced >> geqo_threshold to 6 and set enable_partitionwise_join to to true and >> tried following query, which crashed, >> >> explain select * from

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

>>> > In an attempt to test the geqo side of this patch, I reduced > geqo_threshold to 6 and set enable_partitionwise_join to to true and > tried following query, which crashed, > > explain select * from prt, prt2, prt3, prt32, prt4, prt42 where prt.a > = prt2.b and prt3.a = prt32.b and prt4.a =

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Mar 21, 2017 at 10:40 PM, Ashutosh Bapat wrote: > Here's the set of patches rebased on latest head, which also has a > commit to eliminate scans on partitioned tables. This change has > caused problems with multi-level partitioned tables, that I have not >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Tue, Mar 21, 2017 at 7:41 AM, Ashutosh Bapat wrote: > On Mon, Mar 20, 2017 at 10:17 PM, Ashutosh Bapat > wrote: >>> >>> On a further testing of this patch I find another case when it is >>> showing regression, the time taken

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

Thanks Rajkumar. Added those in the latest set of patches. On Tue, Mar 21, 2017 at 3:52 PM, Rajkumar Raghuwanshi wrote: >> On Mon, Mar 20, 2017 at 1:19 PM, Ashutosh Bapat >> wrote: > > I have created some test to cover

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

> On Mon, Mar 20, 2017 at 1:19 PM, Ashutosh Bapat > wrote: I have created some test to cover partition wise joins with postgres_fdw, also verified make check. patch attached. Thanks & Regards, Rajkumar Raghuwanshi QMG, EnterpriseDB Corporation diff --git

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 20, 2017 at 1:19 PM, Ashutosh Bapat wrote: >> That seems different than what I suggested and I'm not sure what the >> reason is for the difference? > > The patch adding macros IS_JOIN_REL() and IS_OTHER_REL() and changing > the code to use it will look

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 20, 2017 at 10:26 PM, Robert Haas wrote: > On Mon, Mar 20, 2017 at 12:52 PM, Ashutosh Bapat > wrote: >>> Hmm. I would kind of like to move the IS_JOIN_REL() and >>> IS_OTHER_REL() stuff to the front of the series. In other

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 20, 2017 at 12:52 PM, Ashutosh Bapat wrote: >> Hmm. I would kind of like to move the IS_JOIN_REL() and >> IS_OTHER_REL() stuff to the front of the series. In other words, I >> propose that we add those macros first, each testing for only the one >>

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

> > On a further testing of this patch I find another case when it is > showing regression, the time taken with patch is around 160 secs and > without it is 125 secs. > Another minor thing to note that is planning time is almost twice with > this patch, though I understand that this is for

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

> > Hmm. I would kind of like to move the IS_JOIN_REL() and > IS_OTHER_REL() stuff to the front of the series. In other words, I > propose that we add those macros first, each testing for only the one > kind of RelOptInfo that exists today, and change all the code to use > them. Then, when we

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 20, 2017 at 12:07 PM, Rafia Sabih wrote: > On a further testing of this patch I find another case when it is > showing regression, the time taken with patch is around 160 secs and > without it is 125 secs. This is basically the same problem as before;

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 20, 2017 at 9:44 AM, Ashutosh Bapat wrote: > Right. If we could use parent Vars to indicate parent Var or child Var > depending upon the context, a lot of memory issues would be solved; we > wouldn't need to translate a single expression. But I think

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 20, 2017 at 9:44 AM, Ashutosh Bapat wrote: >> I believe it would also be best to include 0011's changes to >> adjust_appendrel_attrs_multilevel in 0001. > > The function needs to repeat the "adjustment" process for every > "other" relation (join or

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Mon, Mar 20, 2017 at 8:21 AM, Robert Haas wrote: > On Fri, Mar 17, 2017 at 8:10 PM, Robert Haas wrote: >> While I was studying what you did with reparameterize_path_by_child(), >> I started to wonder whether reparameterize_path() doesn't need to

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Sat, Mar 18, 2017 at 5:40 AM, Robert Haas wrote: > On Fri, Mar 17, 2017 at 9:15 AM, Ashutosh Bapat > wrote: >> This set of patches fixes both of those things. > > 0001 changes the purpose of a function and then 0007 renames it. It >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Mar 17, 2017 at 8:10 PM, Robert Haas wrote: > While I was studying what you did with reparameterize_path_by_child(), > I started to wonder whether reparameterize_path() doesn't need to > start handling join paths. I think it only handles scan paths right > now

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Sun, Mar 19, 2017 at 12:15 AM, Rafia Sabih wrote: > I was trying to play around with this patch and came across following > case when without the patch query completes in 9 secs and with it in > 15 secs. Theoretically, I tried to capture the case when each >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Sat, Mar 18, 2017 at 5:40 AM, Robert Haas wrote: > On Fri, Mar 17, 2017 at 9:15 AM, Ashutosh Bapat > wrote: >> This set of patches fixes both of those things. > > 0001 changes the purpose of a function and then 0007 renames it. It >

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Fri, Mar 17, 2017 at 9:15 AM, Ashutosh Bapat wrote: > This set of patches fixes both of those things. 0001 changes the purpose of a function and then 0007 renames it. It would be better to include the renaming in 0001 so that you're not taking multiple whacks

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

On Thu, Mar 16, 2017 at 7:19 AM, Ashutosh Bapat wrote: > On Thu, Mar 16, 2017 at 7:10 AM, Robert Haas wrote: >> So I am looking at this part of 0008: >> >> + /* >> +* Do not copy parent_rinfo and child_rinfos because 1. they

<    1   2   3   >