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

Hello,

On Wed, Mar 8, 2023 at 9:34 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Hello Watari-san, this patch does not currently apply. Could you please
> rebase?

Thank you for pointing it out. I have attached the rebased version to
this email. This version includes an additional change, v18-0005. The
change relates to the Bitmapset operations that David mentioned:

On Thu, Mar 9, 2023 at 6:23 AM David Rowley <dgrowleyml@gmail.com> wrote:
> Yes. I'm currently trying to make a few Bitmapset improvements which
> include the change made in this thread's 0001 patch over on [1].

As of v18-0005, the redundant loop to check if the result of
bms_intersect() is empty has been removed. This change is almost the
same as David's following idea in the [1] thread, but slightly
different.

On Fri, Mar 3, 2023 at 10:52 AM David Rowley <dgrowleyml@gmail.com> wrote:
> The patch also optimizes sub-optimal newly added code which calls
> bms_is_empty_internal() when we have other more optimal means to
> determine if the set is empty or not.

I conducted an experiment measuring the planning time of Query B [2].
In the experiment, I tested the next four versions:

* Master
* (A): v18-0001 + v18-0002 + v18-0003 + v18-0004 (= v17)
* (B): v18-0001 + v18-0002 + v18-0003 + v18-0004 + v18-0005
* (C): v18-0002 + v18-0003 + v18-0004 + David's patches in [1]
  --> Since [1] includes v18-0001, (C) does not contain v18-0001.

The following tables show the results. These show that when the number
of partitions is large, (B) is faster than (A). This result indicates
that the change in v18-0005 is effective on this workload. In
addition, the patches in [1] slowed down the performance compared to
(A) and (B). I am not sure of the cause of this degradation. I will
investigate this issue further. I hope these results will help the
discussion of [1].

Table 1: Planning time of Query B (ms)
----------------------------------------------
   n |   Master |     (A) |     (B) |     (C)
----------------------------------------------
   1 |   37.780 |  38.836 |  38.354 |  38.187
   2 |   36.222 |  37.067 |  37.416 |  37.068
   4 |   38.001 |  38.410 |  37.980 |  38.005
   8 |   42.384 |  41.159 |  41.601 |  42.218
  16 |   53.906 |  47.277 |  47.080 |  59.466
  32 |   88.271 |  58.842 |  58.762 |  69.474
  64 |  229.445 |  91.675 |  91.194 | 115.348
 128 |  896.418 | 166.251 | 161.182 | 335.121
 256 | 4220.514 | 371.369 | 350.723 | 923.272
----------------------------------------------

Table 2: Planning time speedup of Query B (higher is better)
--------------------------------------------------------------------------
   n | Master / (A) | Master / (B) | Master / (C) | (A) / (B) | (A) / (C)
--------------------------------------------------------------------------
   1 |        97.3% |        98.5% |        98.9% |    101.3% |    101.7%
   2 |        97.7% |        96.8% |        97.7% |     99.1% |    100.0%
   4 |        98.9% |       100.1% |       100.0% |    101.1% |    101.1%
   8 |       103.0% |       101.9% |       100.4% |     98.9% |     97.5%
  16 |       114.0% |       114.5% |        90.7% |    100.4% |     79.5%
  32 |       150.0% |       150.2% |       127.1% |    100.1% |     84.7%
  64 |       250.3% |       251.6% |       198.9% |    100.5% |     79.5%
 128 |       539.2% |       556.2% |       267.5% |    103.1% |     49.6%
 256 |      1136.5% |      1203.4% |       457.1% |    105.9% |     40.2%
--------------------------------------------------------------------------

On Thu, Mar 9, 2023 at 6:23 AM David Rowley <dgrowleyml@gmail.com> wrote:
> For the main patch, I've been starting to wonder if it should work
> completely differently.  Instead of adding members for partitioned and
> inheritance children, we could just translate the Vars from child to
> top-level parent and find the member that way. I wondered if this
> method might be even faster as it would forego
> add_child_rel_equivalences(). I think we'd still need em_is_child for
> UNION ALL children.  So far, I've not looked into this in detail. I
> was hoping to find an idea that would allow some means to have the
> planner realise that a LIST partition which allows a single Datum
> could skip pushing base quals which are constantly true. i.e:
>
> create table lp (a int) partition by list(a);
> create table lp1 partition of lp for values in(1);
> explain select * from lp where a = 1;
>
>  Seq Scan on lp1 lp  (cost=0.00..41.88 rows=13 width=4)
>    Filter: (a = 1)

Thank you for considering this issue. I will look into this as well.

[1] https://postgr.es/m/CAApHDvq9eq0W_aFUGrb6ba28ieuQN4zM5Uwqxy7+LMZjJc+VGg@mail.gmail.com
[2] https://postgr.es/m/CAJ2pMka2PBXNNzUfe0-ksFsxVN%2BgmfKq7aGQ5v35TcpjFG3Ggg%40mail.gmail.com

-- 
Best regards,
Yuya Watari