Re: Parallel Inserts in CREATE TABLE AS

On Thu, May 27, 2021 at 7:12 AM houzj(dot)fnst(at)fujitsu(dot)com
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> I followed your above test steps and the below configuration, but my test results are a little different from yours.
> I am not sure the exact reason, maybe because of the hardware..
>
> Test INSERT 10000000 rows((2 bigint(of 8 bytes) 16 name(of 64 bytes each) columns):
> SERIAL: 22023.631 ms
> PARALLEL 2 WORKER [NOT SKIP FSM]: 21824.934 ms [SKIP FSM]: 19381.474 ms
> PARALLEL 4 WORKER [NOT SKIP FSM]: 20481.117 ms [SKIP FSM]: 18381.305 ms

I'm not sure why there's a huge difference in the execution time, on
your system it just takes ~20sec whereas on my system(with SSD) it
takes ~115 sec. I hope you didn't try creating the unlogged table in
CTAS right? Just for reference, the exact use case I tried is at [1].
The configure command I used to build the postgres source code is at
[2]. I don't know whether I'm missing something here.

[1] case 4 - 2 bigint(of 8 bytes each) columns, 16 name(of 64 bytes
each) columns, tuple size 1064 bytes, 10mn tuples
DROP TABLE tenk1;
CREATE UNLOGGED TABLE tenk1(c1 bigint, c2 bigint, c3 name, c4 name, c5
name, c6 name, c7 name, c8 name, c9 name, c10 name, c11 name, c12
name, c13 name, c14 name, c15 name, c16 name, c17 name, c18 name);
INSERT INTO tenk1 values(generate_series(1,10000000),
generate_series(1,10000000),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)),
upper(substring(md5(random()::varchar),2,8)));
explain analyze verbose create table test as select * from tenk1;

[2] ./configure --with-zlib --prefix=$PWD/inst/ --with-openssl
--with-readline --with-libxml > war.log && make -j 8 install >
war.log 2>&1 &

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com