RE: Parallel INSERT (INTO ... SELECT ...)

From: Hou, Zhijie/侯 志杰 <houzj(dot)fnst(at)cn(dot)fujitsu(dot)com>
> > What would the result look like if you turn off
> > parallel_leader_participation? If the leader is freed from
> > reading/writing the table and index, the index page splits and
> > internal lock contention may decrease enough to recover part of the loss.
> >
> > https://www.postgresql.org/docs/devel/parallel-plans.html
> >
> > "In a parallel bitmap heap scan, one process is chosen as the leader.
> > That process performs a scan of one or more indexes and builds a
> > bitmap indicating which table blocks need to be visited. These blocks
> > are then divided among the cooperating processes as in a parallel
> > sequential scan. In other words, the heap scan is performed in
> > parallel, but the underlying index scan is not."
>
> If I disable parallel_leader_participation.
>
> For max_parallel_workers_per_gather = 4, It still have performance
> degradation.
>
> For max_parallel_workers_per_gather = 2, the performance degradation will
> not happen in most of the case.
> There is sometimes a noise(performance degradation), but most of
> result(about 80%) is good.

Thank you. The results indicate that it depends on the degree of parallelism whether the gain from parallelism outweighs the loss of parallel insert operations, at least in the bitmap scan case.

But can we conclude that this is limited to bitmap scan? Even if that's the case, the planner does not have information about insert operation to choose other plans like serial execution or parallel sequential scan. Should we encourage the user in the manual to tune parameters and find the fastest plan?

Regards
Takayuki Tsunakawa