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

On Mon, Feb 1, 2021 at 7:20 PM Tang, Haiying <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com> wrote:
>
> 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.
>

Hi,

Did it actually use a parallel plan in your testing?
When I ran these tests with the Parallel INSERT patch applied, it did
not naturally choose a parallel plan for any of these cases.
So we can hardly blame the parallel insert with bitmap heap scan for
having worse performance, when based on costings, it doesn't actually
choose to use a parallel plan in this case.

Regards,
Greg Nancarrow
Fujitsu Australia