On Thu, 14 Sept 2023 at 21:04, PG Bug reporting form
<noreply@postgresql.org> wrote:
> I've been looking at `enable_partitionwise_aggregate` which significantly
> increases the speed of the aggregation however, it seems that when I combine
> the select that normally launches the parallel aggregations it's forced to
> be sequential even if I wrap the aggregation query in a materialised cte.
(it does not really seem like you're reporting a bug here, so the
pgsql-general mailing list would have been a better place to ask these
questions)
It's confusing what this complaint is about. In the subject, you've
indicated that you can't get partitionwise aggregation working with
INSERT, but what you've written above seems to be about the lack of
parallel query for the INSERT operation. I'm assuming the subject
line is wrong as I see partition-wise aggregate on my local instance
in the EXPLAIN output of your INSERT query.
Going by https://www.postgresql.org/docs/15/when-can-parallel-query-be-used.html
the text there says:
"the planner will not generate them for a given query if any of the
following are true:
The query writes any data or locks any database rows"
So that should answer why you're not seeing a parallel plan in your
INSERT as INSERTs write data.
> two side questions here:
> 1. I can't seem to get the parallel aggregation to use all 16 available
> workers, is there some additional config I need to bump how many workers
> would be planned?
Unfortunately not. You're seeing 5 workers because of the following
code in add_paths_to_append_rel()
parallel_workers = Max(parallel_workers,
pg_leftmost_one_pos32(list_length(live_childrels)) + 1);
list_length(live_childrels) is 16 (one for each of your partitions).
> 2. The best scenario would be that the insert itself would be partitionwise
> since both the raw and aggregated tables will have the same keys in each
> partition but I'm not sure if that's possible without running the inserts in
> parallel manually on each partitioned table?
Running 16 separate INSERT ... SELECT ... GROUP BY queries sounds like
it would be the fastest way. We don't do parallel query for INSERTs
yet.
David