Hi,
In earlier benchmark runs we saw that some combinations of lower number of
joins and lower number of partitions showed planning time higher with patch
compared to without patch. Those coincided with higher deviation in
measurement. In an offline chat David Rowley suggested taking an average of
many runs for those cases. Here are results with a lesser number of
partitions, where average is taken over thousands of runs. Planning time of
a query was sampled as many times as the number of runs that fit in 30s and
then averages were calculated. This exercise was repeated twice.
Each cell is a triple (s, md, pd) where s is improvement in planning
time using the patches in % as compared to the master (higher the
better), md = standard deviation as % of the average planning time on
master, pd = is standard deviation as % of the average planning time
with patches.
Exercise 1
num_parts | 2 | 3 |
4 | 5
-----------+----------------------------+----------------------------+----------------------------+----------------------------
0 | s=-0.96% md=7.63% pd=9.43% | s=-0.68% md=5.94% pd=5.65% |
s=-0.29% md=5.15% pd=5.67% | s=0.19% md=6.25% pd=5.21%
16 | s=-3.07% md=6.57% pd=3.56% | s=-1.52% md=1.29% pd=1.21% |
s=-0.58% md=1.78% pd=1.47% | s=0.41% md=1.84% pd=1.51%
32 | s=-1.40% md=2.58% pd=2.77% | s=-0.23% md=3.21% pd=1.16% |
s=2.86% md=1.83% pd=1.78% | s=4.16% md=1.23% pd=1.62%
64 | s=-0.09% md=2.10% pd=2.11% | s=0.22% md=1.75% pd=4.23% |
s=5.86% md=1.87% pd=1.38% | s=10.86% md=1.06% pd=0.84%
128 | s=-1.15% md=2.79% pd=2.48% | s=3.45% md=1.67% pd=2.08% |
s=9.63% md=0.88% pd=1.98% | s=17.33% md=1.46% pd=1.59%
256 | s=-2.88% md=2.79% pd=2.53% | s=5.87% md=0.79% pd=1.80% |
s=13.63% md=1.07% pd=1.58% | s=23.57% md=0.78% pd=0.69%
planning time improvement with PWJ=on
num_parts | 2 | 3 |
4 | 5
-----------+----------------------------+----------------------------+----------------------------+----------------------------
0 | s=-0.35% md=7.98% pd=7.32% | s=-0.84% md=6.40% pd=6.36% |
s=-0.18% md=5.68% pd=5.62% | s=-1.13% md=4.64% pd=6.16%
16 | s=-1.61% md=2.62% pd=2.26% | s=-0.42% md=1.27% pd=1.18% |
s=-0.27% md=2.17% pd=2.23% | s=-0.86% md=1.46% pd=1.44%
32 | s=-1.73% md=6.91% pd=6.56% | s=0.64% md=2.75% pd=2.12% |
s=1.03% md=2.23% pd=1.44% | s=0.80% md=2.63% pd=1.71%
64 | s=-1.12% md=4.13% pd=4.25% | s=1.81% md=5.22% pd=2.20% |
s=0.99% md=2.14% pd=2.20% | s=1.64% md=1.24% pd=1.57%
128 | s=-0.94% md=2.68% pd=3.27% | s=-2.82% md=4.00% pd=4.73% |
s=2.43% md=2.28% pd=2.11% | s=5.96% md=2.01% pd=1.23%
256 | s=0.31% md=3.21% pd=2.58% | s=3.74% md=3.48% pd=1.89% |
s=4.18% md=1.36% pd=1.36% | s=5.49% md=0.52% pd=1.24%
Exercise 2
planning time improvement with PWJ=off
num_parts | 2 | 3 |
4 | 5
-----------+-----------------------------+----------------------------+----------------------------+----------------------------
0 | s=1.26% md=16.39% pd=16.52% | s=-0.68% md=4.66% pd=5.15% |
s=-1.62% md=4.38% pd=4.21% | s=-0.25% md=4.10% pd=4.29%
16 | s=-4.40% md=8.67% pd=7.18% | s=-1.08% md=1.44% pd=1.83% |
s=1.12% md=0.75% pd=1.50% | s=0.14% md=0.56% pd=0.46%
32 | s=-1.17% md=5.86% pd=5.86% | s=-0.39% md=1.22% pd=0.75% |
s=3.33% md=0.40% pd=0.84% | s=5.89% md=0.35% pd=0.65%
64 | s=0.41% md=4.36% pd=4.77% | s=0.93% md=0.29% pd=0.30% |
s=3.38% md=0.51% pd=0.28% | s=9.63% md=0.19% pd=1.08%
128 | s=-0.50% md=3.40% pd=3.06% | s=1.54% md=0.31% pd=0.58% |
s=12.47% md=0.36% pd=0.62% | s=19.30% md=0.18% pd=0.24%
256 | s=-3.42% md=2.93% pd=2.80% | s=2.94% md=0.29% pd=0.36% |
s=12.50% md=0.22% pd=0.27% | s=23.14% md=0.30% pd=0.55%
planning time improvement with PWJ=on
num_parts | 2 | 3 |
4 | 5
-----------+----------------------------+----------------------------+----------------------------+----------------------------
0 | s=1.47% md=6.05% pd=6.20% | s=-0.72% md=5.14% pd=5.09% |
s=0.53% md=4.17% pd=4.39% | s=-0.13% md=4.03% pd=3.58%
16 | s=-0.94% md=3.91% pd=3.51% | s=-1.46% md=2.24% pd=2.47% |
s=1.12% md=0.67% pd=1.50% | s=-0.64% md=0.76% pd=0.70%
32 | s=-0.50% md=6.46% pd=6.11% | s=1.73% md=2.78% pd=1.30% |
s=3.47% md=0.77% pd=0.53% | s=3.13% md=0.59% pd=0.31%
64 | s=-1.19% md=3.52% pd=4.08% | s=-0.47% md=1.49% pd=0.48% |
s=-1.04% md=0.67% pd=0.61% | s=5.87% md=2.50% pd=0.54%
128 | s=-1.67% md=2.22% pd=2.45% | s=1.54% md=1.16% pd=1.21% |
s=4.26% md=0.43% pd=1.24% | s=4.31% md=0.82% pd=0.51%
256 | s=-2.10% md=1.66% pd=2.85% | s=0.96% md=1.04% pd=0.46% |
s=3.18% md=0.55% pd=0.53% | s=7.41% md=1.08% pd=0.30%
The averages are now more stable than the previous exercise. Some
regressions seen with the first exercise are not seen with the other and
some improvements seens with the first exercise are not seen with the
second and vice versa. The regressions present in both the exercises will
not be seen, if I repeat the exercise a few more times. So I think those
regressions or improvements seen with lower number of joins and lower
number of partitions aren't real and they are mostly within noise level.
However, the improvements seen with higher numbers of joins and partitions
are always there irrespective of the number of times I repeat the exercise.
Please note that we have only tried partitions upto 256. Previous
measurements have seen stable improvements in case of higher number of
partitions.
--
Best Wishes,
Ashutosh Bapat
