Hi Bharath,
I'm trying to take some performance measurements on you patch v23.
But when I started, I found an issue about the tuples unbalance distribution
among workers(99% tuples read by one worker) under specified case which lead
the "parallel select" part makes no performance gain.
Then I find it's not introduced by your patch, because it's also happening in
master(HEAD). But I don't know how to deal with it , so I put it here to see if
anybody know what's going wrong with this or have good ideas to deal this issue.
Here are the conditions to produce the issue:
1. high CPU spec environment(say above 20 processors). In smaller CPU, it also
happen but not so obvious(40% tuples on one worker in my tests).
2. query plan is "serial insert + parallel select", I have reproduce this
behavior in (CTAS, Select into, insert into select).
3. select part needs to query large data size(e.g. query 100 million from 200
million).
According to above, IMHO, I guess it may be caused by the leader write rate
can't catch the worker read rate, then the tuples of one worker blocked in the
queue, become more and more.
Below is my test info:
1. test spec environment
CentOS 8.2, 128G RAM, 40 processors, disk SAS
2. test data prepare
create table x(a int, b int, c int);
create index on x(a);
insert into x select
generate_series(1,200000000),floor(random()*(10001-1)+1),floor(random()*(10001-1)+1);
3. test execute results
*Patched CTAS*: please look at worker 2, 99% tuples read by it.
explain analyze verbose create table test(a,b,c) as select
a,floor(random()*(10001-1)+1),c from x where b%2=0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1942082.77 rows=1000001 width=16) (actual
time=0.203..24023.686 rows=100006268 loops=1)
Output: a, floor(((random() * '10000'::double precision) + '1'::double
precision)), c
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on public.x (cost=0.00..1831082.66 rows=250000
width=8) (actual time=0.016..4367.035 rows=20001254 loops=5)
Output: a, c
Filter: ((x.b % 2) = 0)
Rows Removed by Filter: 19998746
Worker 0: actual time=0.016..19.265 rows=94592 loops=1
Worker 1: actual time=0.027..31.422 rows=94574 loops=1
Worker 2: actual time=0.014..21744.549 rows=99627749 loops=1
Worker 3: actual time=0.015..19.347 rows=94586 loops=1 Planning
Time: 0.098 ms Execution Time: 91054.828 ms
*Non-patched CTAS*: please look at worker 0, also 99% tuples read by it.
explain analyze verbose create table test(a,b,c) as select
a,floor(random()*(10001-1)+1),c from x where b%2=0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1942082.77 rows=1000001 width=16) (actual
time=0.283..19216.157 rows=100003148 loops=1)
Output: a, floor(((random() * '10000'::double precision) + '1'::double
precision)), c
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on public.x (cost=0.00..1831082.66 rows=250000
width=8) (actual time=0.020..4380.360 rows=20000630 loops=5)
Output: a, c
Filter: ((x.b % 2) = 0)
Rows Removed by Filter: 19999370
Worker 0: actual time=0.013..21805.647 rows=99624833 loops=1
Worker 1: actual time=0.016..19.790 rows=94398 loops=1
Worker 2: actual time=0.013..35.340 rows=94423 loops=1
Worker 3: actual time=0.035..19.849 rows=94679 loops=1 Planning
Time: 0.083 ms Execution Time: 91151.097 ms
I'm still working on the performance tests on your patch, if I make some
progress, I will post my results here.
Regards,
Tang
