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

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 table for the latest patch(V11).
The measurement is mainly focus on small rows because this could be easier to evaluate check overhead among the parallelism optimization.
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(all time) |
-----------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
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(all time) |
-----------|------------------|--------------------|--------------------|-------------------|---------------------|-----------------|
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(Excution Time) | %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 use case 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 share the info with me, I'd like to do more tests on it.

Regards,
Tang