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

Hi Tsunakawa-san

> From: Tang, Haiying <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com>
> > (does this patch make some optimizes in serial insert? I'm a little
> > confused here, Because the patched execution time is less than
> > unpatched, but I didn't find information in commit messages about it.
> > If I missed something, please kindly let me know.)
>
> I haven't thought of anything yet. Could you show us the output of
> EXPLAIN (ANALYZE, BUFFERS, VERBOSE) of 1,000 partitions case for the
> patched and unpatched? If it doesn't show any difference, the output
> of perf may be necessary next.

Execute EXPLAIN on Patched:
postgres=# explain (ANALYZE, BUFFERS, VERBOSE) insert into test_part select * from test_data1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Insert on public.test_part (cost=0.00..15.00 rows=0 width=0) (actual time=44.139..44.140 rows=0 loops=1)
Buffers: shared hit=1005 read=1000 dirtied=3000 written=2000
-> Seq Scan on public.test_data1 (cost=0.00..15.00 rows=1000 width=8) (actual time=0.007..0.201 rows=1000 loops=1)
Output: test_data1.a, test_data1.b
Buffers: shared hit=5
Planning:
Buffers: shared hit=27011
Planning Time: 24.526 ms
Execution Time: 44.981 ms

Execute EXPLAIN on non-Patched:
postgres=# explain (ANALYZE, BUFFERS, VERBOSE) insert into test_part select * from test_data1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Insert on public.test_part (cost=0.00..15.00 rows=0 width=0) (actual time=72.656..72.657 rows=0 loops=1)
Buffers: shared hit=22075 read=1000 dirtied=3000 written=2000
-> Seq Scan on public.test_data1 (cost=0.00..15.00 rows=1000 width=8) (actual time=0.010..0.175 rows=1000 loops=1)
Output: test_data1.a, test_data1.b
Buffers: shared hit=5
Planning:
Buffers: shared hit=72
Planning Time: 0.135 ms
Execution Time: 79.058 ms

> (BTW, were all the 1,000 rows stored in the target table?)

Yes, I checked all rows stored in target table.
postgres=# select count(*) from test_part; count
-------
1000

Regards,
Tang