Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning - Mailing list pgsql-hackers
| From | Ashutosh Bapat |
|---|---|
| Subject | Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning |
| Date | |
| Msg-id | CAExHW5vrMvQzM-XF2TV_CV-LFJXanL8hw7+5Dfu3dr7fJLOOzg@mail.gmail.com Whole thread Raw |
| In response to | Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>) |
| Responses |
Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning
|
| List | pgsql-hackers |
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
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
pgsql-hackers by date: