Re: Unique index prohibits partial aggregates

On Mon, 27 Jun 2022 at 23:49, Bos, Fred <fbos(at)huisman-nl(dot)com> wrote:
> "Settings: effective_cache_size = '128GB', force_parallel_mode = 'on', max_parallel_workers = '40',

You shouldn't be using force_parallel_mode. It does not do what you
think. See the documentation for that GUC, or read [1]

> I expected the query to become faster
> with a unique index or column, so why does the query planner decide on group
> aggregation instead of partial aggregation?

It just simply does not know how many groups are likely to exists on
your expression. Statistics are only gathered on bare columns. The
planner has no idea how many groups are likely to exist for
"t/(1000*3600*24)".

In PostgreSQL 14 and above you could create extended statistics for
the expression using:

create statistics t1_t_stats (ndistinct) on (t/(1000*3600*24)) from
bhload_nohyp_noin; -- pg14
analyze bhload_nohyp_noin;

for your version (pg13), you'd need to create an expression index.

create index on bhload_nohyp_noin using brin ((t/(1000*3600*24)));
analyze bhload_nohyp_noin;

I added "using brin" as a brin index is going to be a bit more
lightweight than a btree index. You only need the index to instruct
ANALYZE to gather statistics. You might also want to ramp up the pages
per range.

With that, the planner might then realise that parallel aggregate
might be worthwhile.

David

[1] https://www.enterprisedb.com/postgres-tutorials/using-forceparallelmode-correctly-postgresql