Normal aggregate and partition wise aggregate have a big difference rows cost:
begin;
create table t1(id integer, name text) partition by hash(id);
create table t1_0 partition of t1 for values with(modulus 3, remainder 0);
create table t1_1 partition of t1 for values with(modulus 3, remainder 1);
create table t1_2 partition of t1 for values with(modulus 3, remainder 2);
commit;
normal aggregate rows cost is 200.
explain (verbose)
select count(1) from t1 group by id;
HashAggregate (cost=106.20..108.20 rows=200 width=12) --here rows is 200
Output: count(1), t1.id
Group Key: t1.id
-> Append (cost=0.00..87.15 rows=3810 width=4)
-> Seq Scan on public.t1_0 t1_1 (cost=0.00..22.70 rows=1270 width=4)
Output: t1_1.id
-> Seq Scan on public.t1_1 t1_2 (cost=0.00..22.70 rows=1270 width=4)
Output: t1_2.id
-> Seq Scan on public.t1_2 t1_3 (cost=0.00..22.70 rows=1270 width=4)
Output: t1_3.id
And partition wise aggregate rows cost is 600
set enable_partitionwise_aggregate = on;
explain (verbose)
select count(1) from t1 group by id;
Append (cost=29.05..96.15 rows=600 width=12) --here rows is 600
-> HashAggregate (cost=29.05..31.05 rows=200 width=12) --this rows looks
like same as normal aggregate
Output: count(1), t1.id
Group Key: t1.id
-> Seq Scan on public.t1_0 t1 (cost=0.00..22.70 rows=1270 width=4)
Output: t1.id
-> HashAggregate (cost=29.05..31.05 rows=200 width=12)
Output: count(1), t1_1.id
Group Key: t1_1.id
-> Seq Scan on public.t1_1 (cost=0.00..22.70 rows=1270 width=4)
Output: t1_1.id
-> HashAggregate (cost=29.05..31.05 rows=200 width=12)
Output: count(1), t1_2.id
Group Key: t1_2.id
-> Seq Scan on public.t1_2 (cost=0.00..22.70 rows=1270 width=4)
Output: t1_2.id
Source code is 15beta1(7fdbdf204920ac279f280d0a8e96946fdaf41aef)
