From: | Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> |
---|---|
To: | Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Partition-wise aggregation/grouping |
Date: | 2017-09-12 09:54:47 |
Message-ID: | CAKcux6n3vbs5nWN311wKKFogi4vUa5uKZEe_VePZqryJvpA3HA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, Sep 8, 2017 at 5:47 PM, Jeevan Chalke <
jeevan(dot)chalke(at)enterprisedb(dot)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 look.
with the v2 patch, here if I change target list order, query is not picking
full partition-wise-aggregate.
SET enable_partition_wise_agg TO true;
SET partition_wise_agg_cost_factor TO 0.5;
SET enable_partition_wise_join TO true;
SET max_parallel_workers_per_gather TO 0;
CREATE TABLE pagg_tab (a int, b int, c int) PARTITION BY RANGE(a);
CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10);
CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20);
CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30);
INSERT INTO pagg_tab SELECT i % 30, i % 30, i % 50 FROM generate_series(0,
299) i;
ANALYZE pagg_tab;
postgres=# explain (verbose, costs off) select a,b,count(*) from pagg_tab
group by a,b order by 1,2;
QUERY PLAN
--------------------------------------------------------------
Sort
Output: pagg_tab_p1.a, pagg_tab_p1.b, (count(*))
Sort Key: pagg_tab_p1.a, pagg_tab_p1.b
-> Append
-> HashAggregate
Output: pagg_tab_p1.a, pagg_tab_p1.b, count(*)
Group Key: pagg_tab_p1.a, pagg_tab_p1.b
-> Seq Scan on public.pagg_tab_p1
Output: pagg_tab_p1.a, pagg_tab_p1.b
-> HashAggregate
Output: pagg_tab_p2.a, pagg_tab_p2.b, count(*)
Group Key: pagg_tab_p2.a, pagg_tab_p2.b
-> Seq Scan on public.pagg_tab_p2
Output: pagg_tab_p2.a, pagg_tab_p2.b
-> HashAggregate
Output: pagg_tab_p3.a, pagg_tab_p3.b, count(*)
Group Key: pagg_tab_p3.a, pagg_tab_p3.b
-> Seq Scan on public.pagg_tab_p3
Output: pagg_tab_p3.a, pagg_tab_p3.b
(19 rows)
-- changing target list order
-- picking partial partition-wise aggregation path
postgres=# explain (verbose, costs off) select b,a,count(*) from pagg_tab
group by a,b order by 1,2;
QUERY PLAN
----------------------------------------------------------------------------
Finalize GroupAggregate
Output: pagg_tab_p1.b, pagg_tab_p1.a, count(*)
Group Key: pagg_tab_p1.b, pagg_tab_p1.a
-> Sort
Output: pagg_tab_p1.b, pagg_tab_p1.a, (PARTIAL count(*))
Sort Key: pagg_tab_p1.b, pagg_tab_p1.a
-> Append
-> Partial HashAggregate
Output: pagg_tab_p1.b, pagg_tab_p1.a, PARTIAL count(*)
Group Key: pagg_tab_p1.b, pagg_tab_p1.a
-> Seq Scan on public.pagg_tab_p1
Output: pagg_tab_p1.b, pagg_tab_p1.a
-> Partial HashAggregate
Output: pagg_tab_p2.b, pagg_tab_p2.a, PARTIAL count(*)
Group Key: pagg_tab_p2.b, pagg_tab_p2.a
-> Seq Scan on public.pagg_tab_p2
Output: pagg_tab_p2.b, pagg_tab_p2.a
-> Partial HashAggregate
Output: pagg_tab_p3.b, pagg_tab_p3.a, PARTIAL count(*)
Group Key: pagg_tab_p3.b, pagg_tab_p3.a
-> Seq Scan on public.pagg_tab_p3
Output: pagg_tab_p3.b, pagg_tab_p3.a
(22 rows)
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksander Alekseev | 2017-09-12 10:03:20 | Re: Automatic testing of patches in commit fest |
Previous Message | Ashutosh Bapat | 2017-09-12 09:49:38 | Re: Partition-wise join for join between (declaratively) partitioned tables |