Re: [HACKERS] Partition-wise aggregation/grouping

2017-11-13 Thread Konstantin Knizhnik
On 11.11.2017 23:29, Konstantin Knizhnik wrote: On 10/27/2017 02:01 PM, Jeevan Chalke wrote: Hi, Attached new patch-set here. Changes include: 1. Added separate patch for costing Append node as discussed up-front in the patch-set. 2. Since we now cost Append node, we don't need

Re: [HACKERS] Partition-wise aggregation/grouping

2017-11-11 Thread Konstantin Knizhnik
On 10/27/2017 02:01 PM, Jeevan Chalke wrote: Hi, Attached new patch-set here. Changes include: 1. Added separate patch for costing Append node as discussed up-front in the patch-set. 2. Since we now cost Append node, we don't need partition_wise_agg_cost_factor GUC. So removed that. The

Re: [HACKERS] Partition-wise aggregation/grouping

2017-11-01 Thread Robert Haas
On Wed, Nov 1, 2017 at 6:20 PM, Jeevan Chalke wrote: > Yep. > But as David reported earlier, if we remove the first part i.e. adding > cpu_operator_cost per tuple, Merge Append will be preferred over an Append > node unlike before. And thus, I thought of better

Re: [HACKERS] Partition-wise aggregation/grouping

2017-11-01 Thread Jeevan Chalke
On Sat, Oct 28, 2017 at 3:07 PM, Robert Haas wrote: > On Fri, Oct 27, 2017 at 1:01 PM, Jeevan Chalke > wrote: > > 1. Added separate patch for costing Append node as discussed up-front in > the > > patch-set. > > 2. Since we now cost Append

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-28 Thread Robert Haas
On Fri, Oct 27, 2017 at 1:01 PM, Jeevan Chalke wrote: > 1. Added separate patch for costing Append node as discussed up-front in the > patch-set. > 2. Since we now cost Append node, we don't need > partition_wise_agg_cost_factor > GUC. So removed that. The

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-27 Thread Jeevan Chalke
Hi, Attached new patch-set here. Changes include: 1. Added separate patch for costing Append node as discussed up-front in the patch-set. 2. Since we now cost Append node, we don't need partition_wise_agg_cost_factor GUC. So removed that. The remaining patch hence merged into main implementation

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-17 Thread Dilip Kumar
On Tue, Oct 17, 2017 at 10:44 PM, Jeevan Chalke wrote: > > I didn't get what you mean by regression here. Can you please explain? > > I see that PWA plan is selected over regular plan when enabled on the basis > of costing. > Regular planning need a Result node

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-17 Thread Jeevan Chalke
On Tue, Oct 17, 2017 at 7:13 PM, Dilip Kumar wrote: > On Fri, Oct 13, 2017 at 12:06 PM, Jeevan Chalke > wrote: > > > While playing around with the patch I have noticed one regression with > the partial partition-wise aggregate. > > I am

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-17 Thread Dilip Kumar
On Fri, Oct 13, 2017 at 12:06 PM, Jeevan Chalke wrote: > While playing around with the patch I have noticed one regression with the partial partition-wise aggregate. I am consistently able to reproduce this on my local machine. Scenario: Group by on non-key

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-16 Thread Jeevan Chalke
On Fri, Oct 13, 2017 at 1:13 PM, David Rowley wrote: > > I looked over the patch and saw this: > > @@ -1800,6 +1827,9 @@ cost_merge_append(Path *path, PlannerInfo *root, > */ > run_cost += cpu_operator_cost * tuples; > > + /* Add MergeAppend node overhead like

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-13 Thread David Rowley
On 13 October 2017 at 19:36, Jeevan Chalke wrote: > I have tried exactly same tests to get to this factor on my local developer > machine. And with parallelism enabled I got this number as 7.9. However, if > I disable the parallelism (and I believe David too

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-13 Thread Jeevan Chalke
On Tue, Oct 10, 2017 at 1:31 PM, David Rowley wrote: > On 10 October 2017 at 17:57, Ashutosh Bapat > wrote: > > Append node just returns the result of ExecProcNode(). Charging > > cpu_tuple_cost may make it too expensive. In other

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-11 Thread Robert Haas
On Tue, Oct 10, 2017 at 6:00 AM, Ashutosh Bapat wrote: > This looks good to me. I think it should be a separate, yet very small patch. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-10 Thread Ashutosh Bapat
On Tue, Oct 10, 2017 at 1:31 PM, David Rowley wrote: > > I don't think there's any need to invent any new GUC. You could just > divide cpu_tuple_cost by something. > > I did a quick benchmark on my laptop to see how much Append really > costs, and with the standard

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-10 Thread David Rowley
On 10 October 2017 at 17:57, Ashutosh Bapat wrote: > Append node just returns the result of ExecProcNode(). Charging > cpu_tuple_cost may make it too expensive. In other places where we > charge cpu_tuple_cost there's some processing done to the tuple like >

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-10 Thread Jeevan Chalke
On Tue, Oct 10, 2017 at 10:27 AM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > On Tue, Oct 10, 2017 at 3:15 AM, David Rowley > wrote: > > On 10 October 2017 at 01:10, Jeevan Chalke > > wrote: > >> Attached new patch set

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-09 Thread Ashutosh Bapat
On Tue, Oct 10, 2017 at 3:15 AM, David Rowley wrote: > On 10 October 2017 at 01:10, Jeevan Chalke > wrote: >> Attached new patch set having HEAD at 84ad4b0 with all these review points >> fixed. Let me know if I missed any thanks. > >

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-09 Thread David Rowley
On 10 October 2017 at 01:10, Jeevan Chalke wrote: > Attached new patch set having HEAD at 84ad4b0 with all these review points > fixed. Let me know if I missed any thanks. I've only really skimmed over this thread and only opened the code enough to extract the

Re: [HACKERS] Partition-wise aggregation/grouping

2017-10-09 Thread Jeevan Chalke
On Thu, Sep 28, 2017 at 3:12 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > > Here are comments on 0004 from last patch set. But most the comments > still apply. > Thank you, Ashutosh for reviewing. > > Patch 0001 adds functions create_hash_agg_path() and >

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-28 Thread Ashutosh Bapat
On Wed, Sep 27, 2017 at 3:42 PM, Jeevan Chalke wrote: > Thanks Ashutosh for reviewing. > > Attached new patch-set with following changes: > > 1. Removed earlier 0007 and 0008 patches which were PoC for supporting > partial aggregation over fdw. I removed them as it

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-27 Thread Jeevan Chalke
Thanks Ashutosh for reviewing. Attached new patch-set with following changes: 1. Removed earlier 0007 and 0008 patches which were PoC for supporting partial aggregation over fdw. I removed them as it will be a different issue altogether and hence I will tackle them separately once this is done.

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-26 Thread Ashutosh Bapat
Hi Jeevan, I have started reviewing these patches. 0001 looks fine. There might be some changes that will be needed, but those will be clear when I review the patch that uses this refactoring. 0002 + * + * If targetlist is provided, we use it else use targetlist from the root. */ static

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-22 Thread Jeevan Chalke
On Thu, Sep 21, 2017 at 5:00 PM, Rajkumar Raghuwanshi < rajkumar.raghuwan...@enterprisedb.com> wrote: > Hi Jeevan, > > while testing latest v3 patches, I am getting a server crash if I reset > partition_wise_agg_cost_factor, please take a look. > > CREATE TABLE lp (a TEXT, b FLOAT, c INT)

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-21 Thread Rajkumar Raghuwanshi
On Mon, Sep 18, 2017 at 12:37 PM, Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > > On Tue, Sep 12, 2017 at 6:21 PM, Jeevan Chalke < > jeevan.cha...@enterprisedb.com> wrote: > >> >> >> On Tue, Sep 12, 2017 at 3:24 PM, Rajkumar Raghuwanshi < >> rajkumar.raghuwan...@enterprisedb.com>

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-18 Thread Rajkumar Raghuwanshi
On Mon, Sep 18, 2017 at 12:37 PM, Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > > > On Tue, Sep 12, 2017 at 6:21 PM, Jeevan Chalke < > jeevan.cha...@enterprisedb.com> wrote: > >> >> >> On Tue, Sep 12, 2017 at 3:24 PM, Rajkumar Raghuwanshi < >> rajkumar.raghuwan...@enterprisedb.com>

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-18 Thread Jeevan Chalke
On Tue, Sep 12, 2017 at 6:21 PM, Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > > > On Tue, Sep 12, 2017 at 3:24 PM, Rajkumar Raghuwanshi < > rajkumar.raghuwan...@enterprisedb.com> wrote: > >> >> Hi Jeevan, >> >> I have started testing partition-wise-aggregate and got one observation,

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-12 Thread Jeevan Chalke
On Tue, Sep 12, 2017 at 3:24 PM, Rajkumar Raghuwanshi < rajkumar.raghuwan...@enterprisedb.com> wrote: > > On Fri, Sep 8, 2017 at 5:47 PM, Jeevan Chalke com> wrote: > >> Here are the new patch-set re-based on HEAD (f0a0c17) and >> latest partition-wise join (v29)

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-12 Thread Rajkumar Raghuwanshi
On Fri, Sep 8, 2017 at 5:47 PM, Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > Here are the new patch-set re-based on HEAD (f0a0c17) and > latest partition-wise join (v29) patches. > Hi Jeevan, I have started testing partition-wise-aggregate and got one observation, please take a

Re: [HACKERS] Partition-wise aggregation/grouping

2017-09-08 Thread Jeevan Chalke
On Wed, Aug 23, 2017 at 4:43 PM, Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > Hi, > > Attached is the patch to implement partition-wise aggregation/grouping. > > As explained earlier, we produce a full aggregation for each partition when > partition keys are leading group by clauses

Re: [HACKERS] Partition-wise aggregation/grouping

2017-08-23 Thread Jeevan Chalke
Hi, Attached is the patch to implement partition-wise aggregation/grouping. As explained earlier, we produce a full aggregation for each partition when partition keys are leading group by clauses and then append is performed. Else we do a partial aggregation on each partition, append them and

Re: [HACKERS] Partition-wise aggregation/grouping

2017-05-01 Thread Robert Haas
On Fri, Apr 28, 2017 at 3:03 AM, Antonin Houska wrote: > I think this is not generic enough because the result of the Append plan can > be joined to another relation. As such a join can duplicate the > already-aggregated values, the aggregates should not be finalized below the >

Re: [HACKERS] Partition-wise aggregation/grouping

2017-04-28 Thread Antonin Houska
Jeevan Chalke wrote: > On Thu, Apr 27, 2017 at 4:53 PM, Antonin Houska wrote: > > > Robert Haas wrote: > > > Well, I'm confused. I see that there's a relationship between what > > > Antonin is trying to do and what Jeevan

Re: [HACKERS] Partition-wise aggregation/grouping

2017-04-27 Thread Jeevan Chalke
On Thu, Apr 27, 2017 at 4:53 PM, Antonin Houska wrote: > Robert Haas wrote: > > > On Wed, Apr 26, 2017 at 6:28 AM, Antonin Houska wrote: > > > Attached is a diff that contains both patches merged. This is just to > prove my > > >

Re: [HACKERS] Partition-wise aggregation/grouping

2017-04-26 Thread Robert Haas
On Wed, Apr 26, 2017 at 6:28 AM, Antonin Houska wrote: > Attached is a diff that contains both patches merged. This is just to prove my > assumption, details to be elaborated later. The scripts attached produce the > following plan in my environment: > >QUERY

Re: [HACKERS] Partition-wise aggregation/grouping

2017-04-05 Thread Antonin Houska
Antonin Houska wrote: > > Jeevan Chalke wrote: > > > Our work will overlap when we are pushing down the aggregate on partitioned > > base relation to its children/partitions. > > > > I think you should continue working on pushing down

Re: [HACKERS] Partition-wise aggregation/grouping

2017-04-05 Thread Antonin Houska
The promised new version of my patch is here: https://www.postgresql.org/message-id/9666.1491295317%40localhost Jeevan Chalke wrote: > On Tue, Mar 21, 2017 at 1:47 PM, Antonin Houska wrote: > > Jeevan Chalke

Re: [HACKERS] Partition-wise aggregation/grouping

2017-03-23 Thread Jeevan Chalke
On Tue, Mar 21, 2017 at 1:47 PM, Antonin Houska wrote: > Jeevan Chalke wrote: > > > Declarative partitioning is supported in PostgreSQL 10 and work is > already in > > progress to support partition-wise joins. Here is a proposal for >

Re: [HACKERS] Partition-wise aggregation/grouping

2017-03-21 Thread Antonin Houska
Jeevan Chalke wrote: > Declarative partitioning is supported in PostgreSQL 10 and work is already in > progress to support partition-wise joins. Here is a proposal for > partition-wise > aggregation/grouping. Our initial performance measurement has shown 7 times

[HACKERS] Partition-wise aggregation/grouping

2017-03-21 Thread Jeevan Chalke
Hi all, Declarative partitioning is supported in PostgreSQL 10 and work is already in progress to support partition-wise joins. Here is a proposal for partition-wise aggregation/grouping. Our initial performance measurement has shown 7 times performance when partitions are on foreign servers and