Re: partition wise aggregate wrong rows cost

On Tue, 24 May 2022 at 15:38, bucoo <bucoo(at)sohu(dot)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