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

From: Hou, Zhijie/侯 志杰 <houzj.fnst@cn.fujitsu.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,
Worker3 does much of the work in the parallel sequential scan + parallel insert case, while the load is well balanced
inthe 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
specialpath 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=129999loops=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.787rows=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=32500loops=4)
 
...
 Execution Time: 5488.493 ms


Regards
Takayuki Tsunakawa