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

Hello,

On Wed, Mar 8, 2023 at 9:34 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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