On Mon, 27 Jun 2022 at 23:49, Bos, Fred <fbos@xxxxxxxxxxxxxx> 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
