From: Hou, Zhijie/侯 志杰 <[email protected]>
> 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
