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