Re: [PoC] Reducing planning time when tables have many partitions

Hello,

Thank you for creating the v10 patches.

On Sun, Dec 4, 2022 at 9:34 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> Overall, I'm not quite sure if this is any faster than your v9 patch.
> I think more performance testing needs to be done. I think the
> v10-0001 + v10-0002 is faster than v9-0001, but perhaps the changes
> you've made in v9-0002 and v9-0003 are worth redoing. I didn't test. I
> was hoping to keep the logic about which method to use to find the
> members in the iterator code and not litter it around the tree.

I tested the performance of v9, v10, and v10 + v9-0002 + v9-0003. The
last one is v10 with v9-0002 and v9-0003 applied.

1. Join Order Benchmark

I ran the Join Order Benchmark [1] and measured its planning times.
The result is shown in Table 1.

Table 1: Speedup of Join Order Benchmark (higher is better)
(n = the number of partitions)
-------------------------------------------------
n | v9 | v10 | v10 + v9-0002 + v9-0003
-------------------------------------------------
2 | 97.2% | 95.7% | 97.5%
4 | 98.0% | 96.7% | 97.3%
8 | 101.2% | 99.6% | 100.3%
16 | 107.0% | 106.7% | 107.5%
32 | 123.1% | 122.0% | 123.7%
64 | 161.9% | 162.0% | 162.6%
128 | 307.0% | 311.7% | 313.4%
256 | 780.1% | 805.5% | 816.4%
-------------------------------------------------

This result indicates that v10 degraded slightly more for the smaller
number of partitions. The performances of v9 and v10 + v9-0002 +
v9-0003 were almost the same, but the latter was faster when the
number of partitions was large.

2. Query A (The query mentioned in [2])

I also ran Query A, which I shared in [2] and you used in
./partbench.sh. The attached figure illustrates the planning times of
Query A. Our patches might have had some degradations, but they were
not so significant.

3. Query B (The query mentioned in [3])

The following tables show the results of Query B. The results are
close to the one of the Join Order Benchmark; v9 and v10 + v9-0002 +
v9-0003 had fewer degradations than v10.

Table 2: Planning Time of Query B (ms)
--------------------------------------------------------------
n | Master | v9 | v10 | v10 + v9-0002 + v9-0003
--------------------------------------------------------------
1 | 36.056 | 37.730 | 38.546 | 37.782
2 | 35.035 | 37.190 | 37.472 | 36.393
4 | 36.860 | 37.478 | 38.312 | 37.388
8 | 41.099 | 40.152 | 40.705 | 40.268
16 | 52.852 | 44.926 | 45.956 | 45.211
32 | 87.042 | 54.919 | 55.287 | 55.125
64 | 224.750 | 82.125 | 81.323 | 80.567
128 | 901.226 | 136.631 | 136.632 | 132.840
256 | 4166.045 | 263.913 | 260.295 | 258.453
--------------------------------------------------------------

Table 3: Speedup of Query B (higher is better)
---------------------------------------------------
n | v9 | v10 | v10 + v9-0002 + v9-0003
---------------------------------------------------
1 | 95.6% | 93.5% | 95.4%
2 | 94.2% | 93.5% | 96.3%
4 | 98.4% | 96.2% | 98.6%
8 | 102.4% | 101.0% | 102.1%
16 | 117.6% | 115.0% | 116.9%
32 | 158.5% | 157.4% | 157.9%
64 | 273.7% | 276.4% | 279.0%
128 | 659.6% | 659.6% | 678.4%
256 | 1578.6% | 1600.5% | 1611.9%
---------------------------------------------------

======

The above results show that the reverts I have made in v9-0002 and
v9-0003 are very important in avoiding degradation. I think we should
apply these changes again. It is unclear whether v9 or v10 + v9-0002 +
v9-0003 is better, but the latter performed better in my experiments.

[1] https://github.com/winkyao/join-order-benchmark
[2] https://postgr.es/m/CAJ2pMkZNCgoUKSE%2B_5LthD%2BKbXKvq6h2hQN8Esxpxd%2Bcxmgomg%40mail.gmail.com
[3] https://postgr.es/m/CAJ2pMka2PBXNNzUfe0-ksFsxVN%2BgmfKq7aGQ5v35TcpjFG3Ggg%40mail.gmail.com

--
Best regards,
Yuya Watari