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 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