Re: [HACKERS] Partition-wise aggregation/grouping - Mailing list pgsql-hackers

From Rajkumar Raghuwanshi
Subject Re: [HACKERS] Partition-wise aggregation/grouping
Date
Msg-id CAKcux6kBz0zibCXZFrBqDK39qo1q=_6Srq=NTSgRwJEC4i2yBA@mail.gmail.com
Whole thread Raw
In response to [HACKERS] Partition-wise aggregation/grouping  (Jeevan Chalke <jeevan.chalke@enterprisedb.com>)
Responses Re: [HACKERS] Partition-wise aggregation/grouping
List pgsql-hackers
On Mon, Sep 18, 2017 at 12:37 PM, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:

On Tue, Sep 12, 2017 at 6:21 PM, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:


On Tue, Sep 12, 2017 at 3:24 PM, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> wrote:

Hi Jeevan,

I have started testing partition-wise-aggregate and got one observation, please take a look.
with the v2 patch, here if I change target list order, query is not picking full partition-wise-aggregate.

Thanks Rajkumar for reporting this.

I am looking into this issue and will post updated patch with the fix.

Logic for checking whether partition keys lead group by keys needs to be
updated here. The group by expressions can appear in any order without
affecting the final result. And thus, the need for partition keys should
be leading the group by keys to have full aggregation is not mandatory.
Instead we must ensure that the partition keys are part of the group by
keys to compute full aggregation on a partition.

Attached, revised patch-set with above fix.

Also, in test-cases, I have removed DROP/ANALYZE commands on child
relations and also removed VERBOSE from the EXPLAIN.

Notes:
HEAD: 8edacab209957520423770851351ab4013cb0167
Partition-wise Join patch-set version: v32

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) PARTITION BY LIST(c);
CREATE TABLE lp1 PARTITION OF lp FOR VALUES IN (10,20);
CREATE TABLE lp2 PARTITION OF lp FOR VALUES IN (30,40);

INSERT INTO lp VALUES ('a1',800, 20);
INSERT INTO lp VALUES ('a2',1250,30);
INSERT INTO lp VALUES ('a3',2975,20);
INSERT INTO lp VALUES ('a3',2850,30);

postgres=# SET enable_partition_wise_agg TO true;
SET
postgres=# SET partition_wise_agg_cost_factor TO 0.5;
SET
postgres=#
postgres=# SELECT MAX(b), AVG(b) FROM lp GROUP BY a HAVING a = 'a3' ORDER BY 1,2;
 max  |  avg  
------+--------
 2975 | 2912.5
(1 row)

postgres=# RESET partition_wise_agg_cost_factor;
RESET
postgres=#
postgres=# SELECT MAX(b), AVG(b) FROM lp GROUP BY a HAVING a = 'a3' ORDER BY 1,2;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

pgsql-hackers by date:

Previous
From: Rafia Sabih
Date:
Subject: Re: [HACKERS] Effect of changing the value for PARALLEL_TUPLE_QUEUE_SIZE
Next
From: Andrew Dunstan
Date:
Subject: Re: [HACKERS] Windows warnings from VS 2017