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

On Sat, 5 Apr 2025 at 02:54, Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> I haven't measured if the patches improve performance of simple scans
> with thousands of partitions. Have you tried measuring that?

I just tried 10k partitions on my Zen4 laptop.

create table lp (a int) partition by list(a);
select 'create table lp'||x||' partition of lp for values
in('||x||');' from generate_Series(1,10000)x;
\gexec

create index on lp(a);
explain (summary on) select * from lp order by a;

master:

Planning Time: 2296.227 ms
Planning Time: 2142.999 ms
Planning Time: 2089.924 ms
Memory: used=84701kB  allocated=85292kB

    59.34%  postgres          [.] bms_is_subset
    17.09%  postgres          [.] find_ec_member_matching_expr
    11.55%  postgres          [.] bms_equal
     3.41%  postgres          [.] get_eclass_for_sort_expr
     2.08%  postgres          [.] add_child_rel_equivalences
     0.59%  postgres          [.] SearchCatCacheInternal
     0.52%  postgres          [.] hash_search_with_hash_value
     0.45%  libc.so.6         [.] __memmove_avx512_unaligned_erms
     0.23%  postgres          [.] AllocSetAlloc
     0.16%  postgres          [.] ResourceOwnerForget
     0.13%  postgres          [.] add_paths_to_append_rel
     0.12%  postgres          [.] RelationIdGetRelation
     0.11%  postgres          [.] create_scan_plan
     0.11%  libc.so.6         [.] __memset_avx512_unaligned_erms
     0.10%  postgres          [.] uint32_hash
     0.10%  libc.so.6         [.] __memcmp_evex_movbe
     0.10%  postgres          [.] lappend

patched:

Planning Time: 118.346 ms
Planning Time: 122.706 ms
Planning Time: 120.424 ms
Memory: used=77677kB  allocated=84752kB

     9.58%  postgres          [.] hash_search_with_hash_value
     7.58%  libc.so.6         [.] __memmove_avx512_unaligned_erms
     6.41%  postgres          [.] SearchCatCacheInternal
     3.35%  postgres          [.] AllocSetAlloc
     3.15%  postgres          [.] bms_next_member
     2.79%  postgres          [.] ResourceOwnerForget
     2.07%  postgres          [.] RelationIdGetRelation
     1.86%  libc.so.6         [.] __memcmp_evex_movbe
     1.78%  postgres          [.] add_paths_to_append_rel
     1.57%  postgres          [.] LockAcquireExtended
     1.35%  postgres          [.] uint32_hash
     1.29%  libc.so.6         [.] __memset_avx512_unaligned_erms

David