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

On Fri, 4 Apr 2025 at 00:34, David Rowley <dgrowleyml@gmail.com> wrote:
> I've attached 2 patches, which I think addresses most of this, aside
> from the last point.
>
> These do need more work. I've just attached what I have so far before
> I head off for the day. I am planning on running some performance
> tests tomorrow and doing a round on the comments.

I've done some further work on this, mostly relating to the code
comments. I also removed the now-empty
dispose_eclass_member_iterator() function.

A couple of things which I'm still uncertain of:

1. How to handle the ec_childmembers array in _outEquivalenceClass().
There's no field to know the size of the array. Maybe I should add one
and then print out the non-empty lists.
2. When processing RELOPT_OTHER_JOINREL in add_child_eq_member(), I'm
adding the member to each List for all individual relid mentioned in
child_relids.  This will result in the member going on multiple Lists
and cause the iterator to possibly return the member multiple times.
That might matter in a few places, e.g.
generate_join_implied_equalities_normal() keeps some scoring based on
the number of members.

For #2, Yuya's Bitmapset approach didn't suffer from this issue as the
Bitmapsets would be unioned to get the non-duplicative members. I
wondered about doing list_append_unique() instead of lappend() in
generate_join_implied_equalities_normal(). Unsure. The only other
thing I can think of is to do something else with members for
RELOPT_OTHER_JOINREL and store them elsewhere.

I also did some benchmarking using the attached script. I've attached
the results of running that on my AMD Zen2 machine. See the end of the
script for the CREATE TABLE statement for loading that into postgres.

The results look pretty good. v37 came out slightly faster than v36,
either noise or because of dispose_eclass_member_iterator() removal.

-- overall plan time.
select testname,sum(plan_time)::int as plan_ms from bench_results
group by 1 order by 2;
     testname     | plan_ms
------------------+---------
 v37_patch        |    6806
 v36_patch        |    6891
 v35_patch        |    6917
 master_1aff1dc8d |   21113

-- plan time by number of joins for 1024 parts
select testname,joins,sum(plan_time)::int as "plan_ms" from
bench_results where parts=1024 group by 1,2 order by 2,1;
     testname     | joins | plan_ms
------------------+-------+---------
 master_1aff1dc8d |     0 |     239
 v35_patch        |     0 |     120
 v36_patch        |     0 |     120
 v37_patch        |     0 |     119
 master_1aff1dc8d |     1 |     485
 v35_patch        |     1 |     181
 v36_patch        |     1 |     184
 v37_patch        |     1 |     180
 master_1aff1dc8d |     2 |     832
 v35_patch        |     2 |     252
 v36_patch        |     2 |     253
 v37_patch        |     2 |     249
 master_1aff1dc8d |     3 |    1284
 v35_patch        |     3 |     342
 v36_patch        |     3 |     338
 v37_patch        |     3 |     337
 master_1aff1dc8d |     4 |    1909
 v35_patch        |     4 |     427
 v36_patch        |     4 |     435
 v37_patch        |     4 |     435
 master_1aff1dc8d |     5 |    2830
 v35_patch        |     5 |     530
 v36_patch        |     5 |     540
 v37_patch        |     5 |     535
 master_1aff1dc8d |     6 |    4759
 v35_patch        |     6 |     685
 v36_patch        |     6 |     691
 v37_patch        |     6 |     681

-- The memory used is about the same as before:
select testname,joins,sum(mem_alloc)::int as mem_alloc from
bench_results group by 1,2 order by 2,1;
     testname     | joins | mem_alloc
------------------+-------+-----------
 master_1aff1dc8d |     0 |    231110
 v35_patch        |     0 |    233662
 v36_patch        |     0 |    233662
 v37_patch        |     0 |    233662
 master_1aff1dc8d |     1 |    432685
 v35_patch        |     1 |    435369
 v36_patch        |     1 |    435369
 v37_patch        |     1 |    435369
 master_1aff1dc8d |     2 |    476916
 v35_patch        |     2 |    476300
 v36_patch        |     2 |    476300
 v37_patch        |     2 |    476300
 master_1aff1dc8d |     3 |    801834
 v35_patch        |     3 |    801372
 v36_patch        |     3 |    801372
 v37_patch        |     3 |    801372
 master_1aff1dc8d |     4 |    917312
 v35_patch        |     4 |    917015
 v36_patch        |     4 |    917015
 v37_patch        |     4 |    917015
 master_1aff1dc8d |     5 |   1460833
 v35_patch        |     5 |   1460701
 v36_patch        |     5 |   1460701
 v37_patch        |     5 |   1460701
 master_1aff1dc8d |     6 |   2550570
 v35_patch        |     6 |   2639395
 v36_patch        |     6 |   2639395
 v37_patch        |     6 |   2639395

David