Exploring enable_partitionwise_aggregate

enable_partitionwise_aggregate.sql

create table t (id serial, created_at timestamptz) partition by range (created_at);
create table t_202309 partition of t for values from ('2023-09-01') TO ('2023-10-01');
create table t_202310 partition of t for values from ('2023-10-01') TO ('2023-11-01');


postgres@[local]:5432 testing# \d t
                                 Partitioned table "public.t"
   Column   |           Type           | Collation | Nullable |            Default
------------+--------------------------+-----------+----------+-------------------------------
 id         | integer                  |           | not null | nextval('t_id_seq'::regclass)
 created_at | timestamp with time zone |           |          |
Partition key: RANGE (created_at)
Number of partitions: 2 (Use \d+ to list them.)

show enable_partitionwise_aggregate;
 enable_partitionwise_aggregate
--------------------------------
 off

explain analyze select sum(id) from t group by id;


-- seq scan on each partition table
-- Append node
-- Group key above append node

set enable_partitionwise_aggregate = on;

explain analyze select sum(id) from t group by id;

-- seq scan on each partition table
-- Batch, Group Key
-- Partial HashAggregate on each partition table
-- Append node
-- Grouping above append node