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

From: Hou, Zhijie/侯 志杰 <houzj(dot)fnst(at)cn(dot)fujitsu(dot)com>
> If we diable bitmapscan, the performance degradation seems will not happen.

Yes, but that's because the hundreds of times slower sequential scan hides the insert time. Furthermore, as an aside, Worker 3 does much of the work in the parallel sequential scan + parallel insert case, while the load is well balanced in the parallel bitmap scan + parallel insert case.

Oracle and SQL Server executes parallel DML by holding an exclusive lock on the target table. They might use some special path for parallel DML to mitigate contention.

[serial bitmap scan + serial insert]
-> Bitmap Heap Scan on public.x (cost=3272.20..3652841.26 rows=79918 width=8) (actual time=8.096..41.005 rows=129999 loops=1)
...
Execution Time: 360.547 ms

[parallel bitmap scan + parallel insert]
-> Parallel Bitmap Heap Scan on public.x (cost=3272.20..1260119.35 rows=19980 width=8) (actual time=5.832..14.787 rows=26000 loops=5)
...
Execution Time: 382.776 ms

[serial sequential scan + serial insert]
-> Seq Scan on public.x (cost=0.00..5081085.52 rows=81338 width=8) (actual time=0.010..18997.317 rows=129999 loops=1)
...
Execution Time: 19311.700 ms

[parallel sequential scan + parallel insert]
-> Parallel Seq Scan on public.x (cost=0.00..2081082.88 rows=20334 width=8) (actual time=4001.641..5287.248 rows=32500 loops=4)
...
Execution Time: 5488.493 ms

Regards
Takayuki Tsunakawa