Re: Planning time is time-consuming

On Tue, 12 Sept 2023 at 02:27, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > I'm not sure if you're asking for help here because you need planning
> > to be faster than it currently is, or if it's because you believe that
> > planning should always be faster than execution. If you think the
> > latter, then you're mistaken.
>
> Yeah. I don't see anything particularly troubling here. Taking
> circa three-quarters of a millisecond (on typical current hardware)
> to plan a four-way join on large tables is not unreasonable.

I took a few minutes to reverse engineer the tables in question (with
assistance from an AI bot) and ran the query in question.
Unsurprisingly, I also see planning as slower than execution, but with
a ratio of about planning being 12x slower than execution vs the
reported ~18x.

Planning Time: 0.581 ms
Execution Time: 0.048 ms

Nothing alarming in perf top of executing the query in pgbench with -M
simple. I think this confirms the problem is just with expectations.

5.09% postgres [.] AllocSetAlloc
2.99% postgres [.] SearchCatCacheInternal
2.52% postgres [.] palloc
2.38% postgres [.] expression_tree_walker_impl
1.82% postgres [.] add_path_precheck
1.78% postgres [.] add_path
1.73% postgres [.] MemoryContextAllocZeroAligned
1.63% postgres [.] base_yyparse
1.61% postgres [.] CatalogCacheComputeHashValue
1.38% postgres [.] try_nestloop_path
1.36% postgres [.] stack_is_too_deep
1.33% postgres [.] add_paths_to_joinrel
1.19% postgres [.] core_yylex
1.18% postgres [.] lappend
1.15% postgres [.] initial_cost_nestloop
1.13% postgres [.] hash_search_with_hash_value
1.01% postgres [.] palloc0
0.95% postgres [.] get_memoize_path
0.90% postgres [.] equal
0.88% postgres [.] get_eclass_for_sort_expr
0.81% postgres [.] compare_pathkeys
0.80% postgres [.] bms_is_subset
0.77% postgres [.] ResourceArrayRemove
0.77% postgres [.] check_stack_depth
0.77% libc.so.6 [.] __memmove_avx_unaligned_erms
0.74% libc.so.6 [.] __memset_avx2_unaligned
0.73% postgres [.] AllocSetFree
0.71% postgres [.] final_cost_nestloop
0.69% postgres [.] compare_path_costs_fuzzily
0.68% postgres [.] initial_cost_mergejoin
0.64% libc.so.6 [.] __memset_avx2_unaligned_erms
0.61% postgres [.] create_nestloop_path
0.61% postgres [.] examine_variable
0.59% postgres [.] hash_bytes
0.56% postgres [.] truncate_useless_pathkeys
0.56% postgres [.] bms_overlap

David