RE: Parallel INSERT (INTO ... SELECT ...) - Mailing list pgsql-hackers
| From | Tang, Haiying |
|---|---|
| Subject | RE: Parallel INSERT (INTO ... SELECT ...) |
| Date | |
| Msg-id | b54f2e306780449093c311118cd8a04e@G08CNEXMBPEKD05.g08.fujitsu.local Whole thread Raw |
| In response to | Re: Parallel INSERT (INTO ... SELECT ...) (Amit Kapila <amit.kapila16@gmail.com>) |
| Responses |
Re: Parallel INSERT (INTO ... SELECT ...)
(Amit Kapila <amit.kapila16@gmail.com>)
|
| List | pgsql-hackers |
Hi Greg, Amit
Cc:hackers
> > > 4. Have you checked the overhead of this on the planner for
> > > different kinds of statements like inserts into tables having 100
> > > or 500 partitions? Similarly, it is good to check the overhead of
> > > domain related checks added in the patch.
> > >
> >
> > Checking that now and will post results soon.
> >
>I am seeing a fair bit of overhead in the planning for the INSERT
>parallel-safety checks (mind you, compared to the overall performance
>gain, it's not too bad).
Considering the 'real-world' use cases and extreme cases I can imagine, I took 3 kinds of measurements on partition
tablefor the latest patch(V11).
The measurement is mainly focus on small rows because this could be easier to evaluate check overhead among the
parallelismoptimization.
From current results, the overhead looks acceptable compared to the benefits as Greg said.
Test 1: overhead of parallel insert into thousands partitions and 1 rows per partition.
%reg=(patched-master)/master
all time= Execution Time+ Planning Time
| patched | master | %reg
|
-----------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
partitions |Execution Time(ms)| Planning Time(ms) | Execution Time(ms) | Planning Time(ms) | %reg(Excution Time) |
%reg(alltime) |
-----------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
1000 | 2281.291 | 25.983 | 9752.145 | 0.208 | -77% |
-76% |
2000 | 2303.229 | 50.427 | 9446.221 | 0.227 | -76% |
-75% |
4000 | 2303.207 | 100.946 | 9948.743 | 0.211 | -77% |
-76% |
6000 | 2411.877 | 152.212 | 9953.114 | 0.210 | -76% |
-74% |
10000 | 2467.235 | 260.751 | 10917.494 | 0.284 | -77% |
-75% |
Test 2: overhead of parallel insert into thousands partitions and 100 rows per partition.
| patched | master | %reg
|
-----------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
partitions |Execution Time(ms)| Planning Time(ms) | Execution Time(ms) | Planning Time(ms) | %reg(Excution Time) |
%reg(alltime) |
-----------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
1000 | 2366.620 | 25.787 | 14052.748 | 0.238 | -83% |
-83% |
2000 | 2325.171 | 48.780 | 10099.203 | 0.211 | -77% |
-76% |
4000 | 2599.344 | 110.978 | 10678.065 | 0.216 | -76% |
-75% |
6000 | 2764.070 | 152.929 | 10880.948 | 0.238 | -75% |
-73% |
10000 | 3043.658 | 265.297 | 11607.202 | 0.207 | -74% |
-71% |
Test 3: overhead of parallel insert into varying number of partitions and inserted rows.
| patched | master |
%reg |
-----------|-----------------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
partitions |total table rows |Execution Time(ms)| Planning Time(ms) | Execution Time(ms) | Planning Time(ms) |
%reg(ExcutionTime) | %reg(all time) |
-----------|-----------------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
100 | 10000000 | 11202.021 | 1.593 | 25668.560 | 0.212 | -56%
| -56% |
500 | 10000000 | 10290.368 | 12.722 | 25730.860 | 0.214 | -60%
| -60% |
1000 | 10000000 | 8946.627 | 24.851 | 26271.026 | 0.219 | -66%
| -66% |
2000 | 10000000 | 10615.643 | 50.111 | 25512.692 | 0.231 | -58%
| -58% |
4000 | 10000000 | 9056.334 | 105.644 | 26643.383 | 0.217 | -66%
| -66% |
------------------------------------------------------------------------------------------------------------------------------------------------------
100 | 1000000 | 2757.670 | 1.493 | 11136.357 | 0.208 | -75%
| -75% |
500 | 1000000 | 2810.980 | 12.696 | 11483.715 | 0.206 | -76%
| -75% |
1000 | 1000000 | 2773.342 | 24.746 | 13441.169 | 0.214 | -79%
| -79% |
2000 | 1000000 | 2856.915 | 51.737 | 10996.621 | 0.226 | -74%
| -74% |
4000 | 1000000 | 2942.478 | 100.235 | 11422.699 | 0.220 | -74%
| -73% |
------------------------------------------------------------------------------------------------------------------------------------------------------
100 | 100000 | 2257.134 | 1.682 | 9351.511 | 0.226 | -76%
| -76% |
500 | 100000 | 2197.570 | 12.452 | 9636.659 | 0.203 | -77%
| -77% |
1000 | 100000 | 2188.356 | 24.553 | 9647.583 | 0.202 | -77%
| -77% |
2000 | 100000 | 2293.287 | 49.167 | 9365.449 | 0.224 | -76%
| -75% |
4000 | 100000 | 2375.935 | 104.562 | 10125.190 | 0.219 | -77%
| -76% |
------------------------------------------------------------------------------------------------------------------------------------------------------
100 | 10000 | 2142.086 | 1.506 | 9500.491 | 0.206 | -77%
| -77% |
500 | 10000 | 2147.779 | 12.260 | 11746.766 | 0.202 | -82%
| -82% |
1000 | 10000 | 2153.286 | 23.900 | 9298.452 | 0.212 | -77%
| -77% |
2000 | 10000 | 2303.170 | 52.844 | 9772.971 | 0.217 | -76%
| -76% |
However, just like Amit and other hackers concerned, if we want to leave the overhead as it is, we should cover real
usecase as much as possible in case we find the overhead can't be ignored(then we should consider to reduce the
overhead).
So if anyone has some reality use cases(which I didn't include in my results above) need to test on this patch. Please
sharethe info with me, I'd like to do more tests on it.
Regards,
Tang
pgsql-hackers by date: