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

On Sat, 5 Apr 2025 at 02:54, Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)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