partition wise aggregate wrong rows cost

From: "bucoo" <bucoo(at)sohu(dot)com>
To: "'Robert Haas'" <robertmhaas(at)gmail(dot)com>, "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "'pgsql-hackers'" <pgsql-hackers(at)postgresql(dot)org>
Subject: partition wise aggregate wrong rows cost
Date: 2022-05-24 03:38:14
Message-ID: 007b01d86f1f$b3d01ee0$1b705ca0$@sohu.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-05-24 03:51:25 Re: Patch: Don't set LoadedSSL unless secure_initialize succeeds
Previous Message Jonathan S. Katz 2022-05-24 02:49:42 Re: allow building trusted languages without the untrusted versions