Re: BUG #17871: JIT during postgresql_fdw remote_estimates EXPLAIN have very negatively effect on planning time - Mailing list pgsql-bugs

Hi Tom,

100+ms on FDW result of two factors

1)FDW call explain 3 times in my case and two of them surely fire JIT

2)locally single EXPLAIN work 35ms on 100 partitions on almost idle server (after multiple runs)

negotiation_chat_archive=# explain select * from interview_review_info_archive;

...

 JIT:
   Functions: 200
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(106 rows)

Time: 34.076 ms
negotiation_chat_archive=# explain select * from interview_review_info_archive;
Time: 34.437 ms
negotiation_chat_archive=# explain select * from interview_review_info_archive;
Time: 34.015 ms

pretty stable... not even close to the 7ms that you show in your test.

Table consists of 2 bigint + 3 timestamptz field + single dropped boolean.

I run

explain select * from interview_review_info_archive

\watch 1

for few minutes with corresponding perf record -p recording

and there is perf report result:  

   4.04%  postgres  libLLVM-10.so.1      [.] llvm::FoldingSetNodeID::AddString
   2.94%  postgres  libc-2.31.so         [.] malloc
   2.58%  postgres  libLLVM-10.so.1      [.] llvm::AttributeImpl::operator<
   2.44%  postgres  libLLVM-10.so.1      [.] llvm::AttributeSetNode::get
   2.18%  postgres  libLLVM-10.so.1      [.] llvm::BasicBlock::dropAllReferences
   1.87%  postgres  libLLVM-10.so.1      [.] llvm::Value::setValueName
   1.80%  postgres  libLLVM-10.so.1      [.] llvm::StringMapImpl::LookupBucketFor
   1.67%  postgres  libLLVM-10.so.1      [.] llvm::FoldingSetBase::FindNodeOrInsertPos
   1.50%  postgres  libLLVM-10.so.1      [.] llvm::FoldingSetNodeID::AddPointer
   1.49%  postgres  libLLVM-10.so.1      [.] llvm::StringMapImpl::RemoveKey
   1.48%  postgres  libLLVM-10.so.1      [.] llvm::BasicBlock::~BasicBlock
   1.46%  postgres  libLLVM-10.so.1      [.] llvm::AttrBuilder::addAttribute
   1.30%  postgres  libLLVM-10.so.1      [.] std::_Rb_tree<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::pair<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > c
   1.23%  postgres  libLLVM-10.so.1      [.] llvm::Value::setNameImpl
   1.17%  postgres  libstdc++.so.6.0.28  [.] std::_Rb_tree_insert_and_rebalance
   1.13%  postgres  libc-2.31.so         [.] cfree
   1.07%  postgres  libLLVM-10.so.1      [.] llvm::Value::destroyValueName
   1.06%  postgres  libLLVM-10.so.1      [.] llvm::User::operator new
   1.04%  postgres  postgres             [.] hash_search_with_hash_value
   0.97%  postgres  libLLVM-10.so.1      [.] std::_Rb_tree<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::pair<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > c
   0.95%  postgres  libLLVM-10.so.1      [.] llvm::hashing::detail::hash_combine_range_impl<unsigned int const>
   0.91%  postgres  libLLVM-10.so.1      [.] llvm::ConstantInt::get
   0.84%  postgres  libLLVM-10.so.1      [.] std::_Rb_tree<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::pair<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > c
   0.80%  postgres  libLLVM-10.so.1      [.] std::__sort<llvm::Attribute*, __gnu_cxx::__ops::_Iter_less_iter>
   0.73%  postgres  libLLVM-10.so.1      [.] llvm::Value::getValueName
   0.73%  postgres  libc-2.31.so         [.] memcpy
   0.69%  postgres  postgres             [.] dopr.constprop.0
   0.63%  postgres  libLLVM-10.so.1      [.] llvm::AttributeList::addAttributes
   0.63%  postgres  libLLVM-10.so.1      [.] llvm::AttrBuilder::addAttribute
   0.62%  postgres  libLLVM-10.so.1      [.] llvm::SymbolTableListTraits<llvm::Instruction>::setSymTabObject<llvm::Function*>
   0.60%  postgres  libstdc++.so.6.0.28  [.] std::_Rb_tree_increment

Maybe it provides some clues.

PS: I run all tests on the postgresql 13 version, maybe there are differences between JIT implementation/performance between 13 version and 15/head.

Regards,

Maxim