Re: Partition-wise aggregation/grouping

Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>

From: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>

To: Jeevan Chalke <jeevan.chalke@enterprisedb.com>

Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>

Date: 2017-09-12T09:54:47Z

Lists: pgsql-hackers

On Fri, Sep 8, 2017 at 5:47 PM, Jeevan Chalke <
jeevan.chalke@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

Commits

postgres_fdw: Push down partition-wise aggregation.
- 7e0d64c7a57e 11.0 landed
Remove 'target' from GroupPathExtraData.
- c1de1a3a8b93 11.0 landed
Implement partition-wise grouping/aggregation.
- e2f1eb0ee30d 11.0 landed
Don't pass the grouping target around unnecessarily.
- 94150513ec12 11.0 landed
Determine grouping strategies in create_grouping_paths.
- b5996c2791f3 11.0 landed
Defer creation of partially-grouped relation until it's needed.
- 4f15e5d09de2 11.0 landed
Split create_grouping_paths into degenerate and non-degenerate cases.
- 1466bcfa4a83 11.0 landed
Pass additional arguments to a couple of grouping-related functions.
- 648a6c7bd815 11.0 landed
Fix logic error in add_paths_to_partial_grouping_rel.
- 3bfe957761ac 11.0 landed
Minor cleanup of code related to partially_grouped_rel.
- 5e6a63c0d102 11.0 landed
Add a new upper planner relation for partially-aggregated results.
- 3bf05e096b9f 11.0 landed
Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.
- 7d8ac9814bc9 11.0 landed
Rename enable_partition_wise_join to enable_partitionwise_join
- 2fb1abaeb016 11.0 cited
Factor some code out of create_grouping_paths.
- 9fd8b7d63257 11.0 landed
Pad XLogReaderState's main_data buffer more aggressively.
- 8735978e7aeb 11.0 cited
Prevent int128 from requiring more than MAXALIGN alignment.
- 7518049980be 11.0 cited
Fix DROP SUBSCRIPTION hang
- 8edacab20995 11.0 cited
Inject $(ICU_LIBS) regardless of platform.
- 66ed3829df95 11.0 cited
Some preliminary refactoring towards partitionwise join.
- c44c47a773bd 10.0 cited