On Tue, 24 May 2022 at 15:38, bucoo <bu...@sohu.com> wrote: > > Normal aggregate and partition wise aggregate have a big difference rows cost:
> explain (verbose) > select count(1) from t1 group by id; > HashAggregate (cost=106.20..108.20 rows=200 width=12) --here rows is 200 > 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 I wouldn't say this is a bug. Could you not say that they're both wrong given that your tables are empty? What's going on here is that estimate_num_groups() is just returning 200, which is what it returns when there are no statistics to give any indication of a better value. 200 is returned no matter if the estimate is for a single partition or the partitioned table. For the partition-wise aggregate case, the 3 individual 200 estimates are just summed up by the Append costing code to give 600. The only way we could really do anything different here would be to have estimate_num_groups() return a default value based on the number of input rows. However, that 200 default is pretty long standing. We'd need to consider quite a bit more than this case before we could realistically consider changing it. For tables that are being created and queried quickly after, we normally tell people to run ANALYZE on the given tables to prevent this sort of thing. David
