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

On Thu, Mar 16, 2017 at 6:48 AM, Ashutosh Bapat wrote: >> I thought the whole point here was that NOT doing that caused the >> memory usage for partitionwise join to get out of control. Am I >> confused? > > We took a few steps to reduce the memory footprint of

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

On Thu, Mar 16, 2017 at 12:35 AM, Robert Haas wrote: > On Wed, Mar 15, 2017 at 8:55 AM, Ashutosh Bapat > wrote: >> Sorry. That was added by my patch to refactor >> set_append_rel_pathlist(). I have added a patch in the series to >> remove

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

On Thu, Mar 16, 2017 at 12:30 AM, Robert Haas wrote: > On Wed, Mar 15, 2017 at 8:49 AM, Ashutosh Bapat > wrote: >>> Of course, that supposes that 0009 can manage to postpone creating >>> non-sampled child joinrels until

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

So I am looking at this part of 0008: + /* +* Do not copy parent_rinfo and child_rinfos because 1. they create a +* circular dependency between child and parent RestrictInfo 2. dropping +* those links just means that we loose some memory optimizations. 3. There +

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

On Wed, Mar 15, 2017 at 8:55 AM, Ashutosh Bapat wrote: > Sorry. That was added by my patch to refactor > set_append_rel_pathlist(). I have added a patch in the series to > remove that line. It's not worth an extra commit just to change what isn't broken. Let's

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

On Wed, Mar 15, 2017 at 8:49 AM, Ashutosh Bapat wrote: >> Of course, that supposes that 0009 can manage to postpone creating >> non-sampled child joinrels until create_partition_join_plan(), which >> it currently doesn't. > > Right. We need the child-join's

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

>> >> There are some differences in what geqo does and what partition-wise >> needs to do. geqo tries many joining orders each one in a separate >> temporary context. The way geqo slices the work, every slice produces >> a full plan. For partition-wise join I do not see a way to slice the >> work

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

On Tue, Mar 14, 2017 at 8:33 PM, Robert Haas wrote: > Of course, that supposes that 0009 can manage to postpone creating > non-sampled child joinrels until create_partition_join_plan(), which > it currently doesn't. In fact, unless I'm missing something, 0009 > hasn't been

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

On Tue, Mar 14, 2017 at 8:04 AM, Ashutosh Bapat wrote: >> In 0001, you've removed a comment about how GEQO needs special >> handling, but it doesn't look as if you've made any compensating >> change elsewhere. That seems unlikely to be correct. If GEQO needs >>

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

Thanks for the review. > > Some very high-level thoughts based on a look through these patches: > > In 0001, you've removed a comment about how GEQO needs special > handling, but it doesn't look as if you've made any compensating > change elsewhere. That seems unlikely to be correct. If GEQO

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

On Tue, Mar 14, 2017 at 6:28 AM, Amit Langote wrote: > On 2017/03/14 9:17, Robert Haas wrote: >> On Mon, Mar 13, 2017 at 3:24 PM, Robert Haas wrote: >>> Haven't looked at 0007 yet. >> >> Overall this one looks pretty good and straightforward.

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

On 2017/03/14 9:17, Robert Haas wrote: > On Mon, Mar 13, 2017 at 3:24 PM, Robert Haas wrote: >> Haven't looked at 0007 yet. > > Overall this one looks pretty good and straightforward. In the following code of find_partition_scheme(): + /* Did not find matching

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

On Mon, Mar 13, 2017 at 3:24 PM, Robert Haas wrote: > Haven't looked at 0007 yet. + if (rel->part_scheme) + { + int cnt_parts; + + for (cnt_parts = 0; cnt_parts < nparts; cnt_parts++) +

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

On Fri, Mar 10, 2017 at 5:43 AM, Ashutosh Bapat wrote: > PFA the zip containing all the patches rebased on > 56018bf26eec1a0b4bf20303c98065a8eb1b0c5d and contain the patch to free > memory consumed by paths using a separate path context. Some very high-level

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

On Wed, Mar 1, 2017 at 3:56 AM, Ashutosh Bapat wrote: >> 2. If the PartitionJoinPath emerges as the best path, we create paths >> for each of the remaining child-joins. Then we collect paths with >> properties same as the given PartitionJoinPath, one from each >>

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

Updated 0001 patch with some more comments. Attaching all the patches for quick access. On Wed, Mar 1, 2017 at 2:26 PM, Ashutosh Bapat wrote: >> >> 2. If the PartitionJoinPath emerges as the best path, we create paths >> for each of the remaining child-joins.

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

> > 2. If the PartitionJoinPath emerges as the best path, we create paths > for each of the remaining child-joins. Then we collect paths with > properties same as the given PartitionJoinPath, one from each > child-join. These paths are converted into plans and a Merge/Append > plan is created

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

On Mon, Feb 6, 2017 at 3:34 PM, Ashutosh Bapat wrote: > PartitionScheme is shared across multiple relations, join or base, > partitioned similarly. Obviously it can't and does not need to point > partition bound informations (which should all be same) of all those

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

sent the previous mail before completing my reply. Sorry for that. Here's the rest of the reply. >> >> + if (bms_num_members(outer_relids) > 1) >> >> Seems like bms_get_singleton_member could be used. >> >> +* Partitioning scheme in join relation indicates a possibilty that

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

On Thu, Feb 2, 2017 at 2:41 AM, Robert Haas wrote: > On Mon, Jan 2, 2017 at 7:32 AM, Ashutosh Bapat > wrote: >> PFA the patch (pg_dp_join_v6.patch) with some bugs fixed and rebased >> on the latest code. > > Maybe not surprisingly given how

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

On Mon, Jan 2, 2017 at 7:32 AM, Ashutosh Bapat wrote: > PFA the patch (pg_dp_join_v6.patch) with some bugs fixed and rebased > on the latest code. Maybe not surprisingly given how fast things are moving around here these days, this needs a rebase. Apart from

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

On Mon, Nov 14, 2016 at 9:57 AM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Nov 4, 2016 at 6:52 AM, Ashutosh Bapat >> wrote: >>> Costing PartitionJoinPath needs more thought so that we don't end up >>> with bad

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

Robert Haas writes: > On Fri, Nov 4, 2016 at 6:52 AM, Ashutosh Bapat > wrote: >> Costing PartitionJoinPath needs more thought so that we don't end up >> with bad overall plans. Here's an idea. Partition-wise joins are >> better compared to

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

On Fri, Nov 4, 2016 at 6:52 AM, Ashutosh Bapat wrote: > Costing PartitionJoinPath needs more thought so that we don't end up > with bad overall plans. Here's an idea. Partition-wise joins are > better compared to the unpartitioned ones, because of the smaller >

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

> > So, I am thinking about your approach of creating PartitionJoinPaths > without actually creating child paths and then at a later stage > actually plan the child joins. Here's rough sketch of how that may be > done. > > At the time of creating regular paths, we identify the join orders > which

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

On Mon, Oct 31, 2016 at 6:37 PM, Robert Haas wrote: > On Fri, Oct 28, 2016 at 3:09 AM, Ashutosh Bapat > wrote: >> I think there are going to be two kinds of partitioning use-cases. >> First, carefully hand-crafted by DBAs so that every

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

On Fri, Oct 28, 2016 at 3:09 AM, Ashutosh Bapat wrote: > I think there are going to be two kinds of partitioning use-cases. > First, carefully hand-crafted by DBAs so that every partition is > different from other and so is every join between two partitions. >

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

On Tue, Oct 18, 2016 at 9:09 PM, Robert Haas wrote: > On Fri, Oct 14, 2016 at 12:37 AM, Ashutosh Bapat > wrote: >>> Have you tested the effect of this patch on planner memory consumption >>> with multi-way joins between tables with many

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

On Fri, Oct 14, 2016 at 12:37 AM, Ashutosh Bapat wrote: >> Have you tested the effect of this patch on planner memory consumption >> with multi-way joins between tables with many partitions? If you >> haven't, you probably should. (Testing runtime would be good,

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

On Thu, Sep 22, 2016 at 6:41 AM, Ashutosh Bapat wrote: > [ new patch ] This should probably get updated since Rajkumar reported a crash. Meanwhile, here are some comments from an initial read-through: + * Multiple relations may be partitioned in the same way.

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

On Tue, Sep 20, 2016 at 4:26 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > PFA patch which takes care of some of the TODOs mentioned in my > previous mail. The patch is based on the set of patches supporting > declarative partitioning by Amit Langoted posted on 26th August. I

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

Hi Rajkumar, On Fri, Sep 16, 2016 at 6:00 PM, Rajkumar Raghuwanshi wrote: > > On Fri, Sep 9, 2016 at 3:17 PM, Ashutosh Bapat > wrote: >> >> Hi All, >> >> PFA the patch to support partition-wise joins for partitioned

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

Hi, I got a server crash with partition_wise_join, steps to reproduce given below. postgres=# set enable_partition_wise_join=true; SET postgres=# CREATE TABLE tbl (a int,c text) PARTITION BY LIST(a); CREATE TABLE postgres=# CREATE TABLE tbl_p1 PARTITION OF tbl FOR VALUES IN (1, 2); CREATE TABLE

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

On Fri, Sep 16, 2016 at 6:00 PM, Rajkumar Raghuwanshi wrote: > > On Fri, Sep 9, 2016 at 3:17 PM, Ashutosh Bapat > wrote: >> >> Hi All, >> >> PFA the patch to support partition-wise joins for partitioned tables. The >> patch

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

On Fri, Sep 9, 2016 at 3:17 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > Hi All, > > PFA the patch to support partition-wise joins for partitioned tables. The > patch > is based on the declarative parition support patches provided by Amit > Langote > on 26th August 2016. > I

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

On 2016/09/09 18:47, Ashutosh Bapat wrote: > A related change is renaming RangeBound structure in Amit > Langote's patches to PartitionRangeBound to avoid name conflict with > rangetypes.h. That change too should vanish once we decide where to keep > that structure and its final name. This change

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

On Fri, Sep 9, 2016 at 3:17 PM, Ashutosh Bapat wrote: > > 4. Remove bms_to_char(): I have added this function to print Relids in the > debugger. I have found it very useful to quickly examine Relids in debugger, > which otherwise wasn't so easy. If others find it

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

Sorry forgot to mention: this patch applies on top of the v7 patches posted by Amit Langote on 27th June ( https://www.postgresql.org/message-id/81371428-bb4b-1e33-5ad6-8c5c51b52cb7%40lab.ntt.co.jp ). On Tue, Jul 19, 2016 at 7:41 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > > >

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

On Wed, Jun 15, 2016 at 3:25 AM, Ashutosh Bapat wrote: > Amit Langote is working on supporting declarative partitioning in PostgreSQL > [1]. I have started working on supporting partition-wise join. This mail > describes very high level design and some insight

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

Amit Langote is working on supporting declarative partitioning in PostgreSQL [1]. I have started working on supporting partition-wise join. This mail describes very high level design and some insight into the performance improvements. An equi-join between two partitioned tables can be broken down

<    1   2   3