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

From: "Tang, Haiying" <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com>
To: Greg Nancarrow <gregn4422(at)gmail(dot)com>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Antonin Houska <ah(at)cybertec(dot)at>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, "Bharath Rupireddy" <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)fujitsu(dot)com>, "Hou, Zhijie" <houzj(dot)fnst(at)cn(dot)fujitsu(dot)com>
Subject: RE: Parallel INSERT (INTO ... SELECT ...)
Date: 2021-02-01 08:19:56
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi Greg,

Recently, I was keeping evaluating performance of this patch(1/28 V13).
Here I find a regression test case which is parallel insert with bitmap heap scan.
when the target table has primary key or index, then the patched performance will have a 7%-19% declines than unpatched.

Could you please have a look about this?

I tried max_parallel_workers_per_gather=2/4/8, and I didn't tune other parameters(like GUCs or other enforce parallel parameters).

1. max_parallel_workers_per_gather=2(default)
target_table patched master %reg
without_PK_index 83.683 142.183 -41%
with_PK 382.824 321.101 19%
with_index 372.682 324.246 15%

2. max_parallel_workers_per_gather=4
target_table patched master %reg
without_PK_index 73.189 141.879 -48%
with_PK 362.104 329.759 10%
with_index 372.237 333.718 12%

3. max_parallel_workers_per_gather=8 (also set max_parallel_workers=16, max_worker_processes = 16)
target_table patched master %reg
without_PK_index 75.072 146.100 -49%
with_PK 365.312 324.339 13%
with_index 362.636 338.366 7%

Attached test_bitmap.sql which includes my test data and sql if you want to have a look.


Attachment Content-Type Size
test_bitmap.sql application/octet-stream 1.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2021-02-01 08:39:31 Re: [HACKERS] [PATCH] Generic type subscripting
Previous Message Dilip Kumar 2021-02-01 08:11:55 Re: Is Recovery actually paused?