Re: [PoC] Reducing planning time when tables have many partitions - Mailing list pgsql-hackers

Hello,

Thank you for creating the v10 patches.

On Sun, Dec 4, 2022 at 9:34 AM David Rowley <dgrowleyml@gmail.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