RE: Parallel INSERT (INTO ... SELECT ...) - Mailing list pgsql-hackers

> What are the results if disable the bitmap heap scan(Set enable_bitmapscan
> = off)? If that happens to be true, then we might also want to consider
> if in some way we can teach parallel insert to cost more in such cases.
> Another thing we can try is to integrate a parallel-insert patch with the
> patch on another thread [1] and see if that makes any difference but not
> sure if we want to go there at this stage unless it is simple to try that
> out?

If we diable bitmapscan, the performance degradation seems will not happen.

[Parallel]
postgres=# explain (ANALYZE, BUFFERS, VERBOSE, WAL)  insert into testscan select a from x where a<80000 or (a%2=0 and
a>199900000);
                                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..2090216.68 rows=81338 width=0) (actual time=0.226..5488.455 rows=0 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   Buffers: shared hit=393364 read=1079535 dirtied=984 written=1027
   WAL: records=260400 bytes=16549513
   ->  Insert on public.testscan  (cost=0.00..2081082.88 rows=0 width=0) (actual time=5483.113..5483.114 rows=0
loops=4)
         Buffers: shared hit=393364 read=1079535 dirtied=984 written=1027
         WAL: records=260400 bytes=16549513
         Worker 0:  actual time=5483.116..5483.117 rows=0 loops=1
           Buffers: shared hit=36306 read=264288 dirtied=100 written=49
           WAL: records=23895 bytes=1575860
         Worker 1:  actual time=5483.220..5483.222 rows=0 loops=1
           Buffers: shared hit=39750 read=280476 dirtied=101 written=106
           WAL: records=26141 bytes=1685083
         Worker 2:  actual time=5482.844..5482.845 rows=0 loops=1
           Buffers: shared hit=38660 read=263713 dirtied=105 written=250
           WAL: records=25318 bytes=1657396
         Worker 3:  actual time=5483.272..5483.274 rows=0 loops=1
           Buffers: shared hit=278648 read=271058 dirtied=678 written=622
           WAL: records=185046 bytes=11631174
         ->  Parallel Seq Scan on public.x  (cost=0.00..2081082.88 rows=20334 width=8) (actual time=4001.641..5287.248
rows=32500loops=4)
 
               Output: x.a, NULL::integer
               Filter: ((x.a < 80000) OR (((x.a % 2) = 0) AND (x.a > 199900000)))
               Rows Removed by Filter: 49967500
               Buffers: shared hit=1551 read=1079531
               Worker 0:  actual time=5335.456..5340.757 rows=11924 loops=1
                 Buffers: shared hit=281 read=264288
               Worker 1:  actual time=5335.559..5341.766 rows=13049 loops=1
                 Buffers: shared hit=281 read=280476
               Worker 2:  actual time=5335.534..5340.964 rows=12636 loops=1
                 Buffers: shared hit=278 read=263712
               Worker 3:  actual time=0.015..5125.503 rows=92390 loops=1
                 Buffers: shared hit=711 read=271055
 Planning:
   Buffers: shared hit=19
 Planning Time: 0.175 ms
 Execution Time: 5488.493 ms

[Serial]
postgres=# explain (ANALYZE, BUFFERS, VERBOSE, WAL)  insert into testscan select a from x where a<80000 or (a%2=0 and
a>199900000);
                                                        QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
 Insert on public.testscan  (cost=0.00..5081085.52 rows=0 width=0) (actual time=19311.642..19311.643 rows=0 loops=1)
   Buffers: shared hit=392485 read=1079694 dirtied=934 written=933
   WAL: records=260354 bytes=16259841
   ->  Seq Scan on public.x  (cost=0.00..5081085.52 rows=81338 width=8) (actual time=0.010..18997.317 rows=129999
loops=1)
         Output: x.a, NULL::integer
         Filter: ((x.a < 80000) OR (((x.a % 2) = 0) AND (x.a > 199900000)))
         Rows Removed by Filter: 199870001
         Buffers: shared hit=1391 read=1079691
 Planning:
   Buffers: shared hit=10
 Planning Time: 0.125 ms
 Execution Time: 19311.700 ms

Best regards,
houzj