partition wise aggregate wrong rows cost - Mailing list pgsql-hackers

From bucoo
Subject partition wise aggregate wrong rows cost
Date
Msg-id 007b01d86f1f$b3d01ee0$1b705ca0$@sohu.com
Whole thread Raw
Responses Re: partition wise aggregate wrong rows cost
List 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)




pgsql-hackers by date:

Previous
From: "Jonathan S. Katz"
Date:
Subject: Re: allow building trusted languages without the untrusted versions
Next
From: Tom Lane
Date:
Subject: Re: Patch: Don't set LoadedSSL unless secure_initialize succeeds