Thread

1. Planner choosing nested loop in place of Hashjoin

   Praneel Devisetty <devisettypraneel@gmail.com> — 2023-03-07T12:14:08Z

   Hi,
   
   I have a query which is taking roughly 10mins to complete and the query
   planner is choosing a nested loop.
   
   query and query plan with analyze,verbose,buffers
   qsEn | explain.depesz.com <https://explain.depesz.com/s/qsEn#html>
   
   Disabling the nested loop on session is allowing the query planner to
   choose a better plan and complete it in 2mins.Stats are up to date and
   analyze was performed a few hours ago.
   
   Any suggestions on what is causing the planner to choose a nested loop in
   place of hash and how can we get the query to choose a better plan without
   disabling the enable_nestloopenable_nestloopenable_nestloop enable_nestloop
   enable_nestloop?
   
   Thanks
   Praneel
   

2. Re: Planner choosing nested loop in place of Hashjoin

   Samed YILDIRIM <samed@reddoc.net> — 2023-03-11T10:31:04Z

   Hi Praneel,
   
   It is hard to propose a solution without seeing the actual query and
   knowing details of the tables. If I were you, I would try to increase
   statistics target for the columns used in joins. Default value is 100. You
   need to analyze those tables again after updating the statistics targets.
   
   ALTER TABLE table ALTER COLUMN column SET STATISTICS 300;
   
   https://www.postgresql.org/docs/14/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET
   
   Best regards.
   Samed YILDIRIM
   
   
   On Tue, 7 Mar 2023 at 14:14, Praneel Devisetty <devisettypraneel@gmail.com>
   wrote:
   
   > Hi,
   >
   > I have a query which is taking roughly 10mins to complete and the query
   > planner is choosing a nested loop.
   >
   > query and query plan with analyze,verbose,buffers
   > qsEn | explain.depesz.com <https://explain.depesz.com/s/qsEn#html>
   >
   > Disabling the nested loop on session is allowing the query planner to
   > choose a better plan and complete it in 2mins.Stats are up to date and
   > analyze was performed a few hours ago.
   >
   > Any suggestions on what is causing the planner to choose a nested loop in
   > place of hash and how can we get the query to choose a better plan without
   > disabling the enable_nestloopenable_nestloopenable_nestloop
   > enable_nestloopenable_nestloop?
   >
   > Thanks
   > Praneel
   >
   >
   >
   

3. Re: Planner choosing nested loop in place of Hashjoin

   Jeff Janes <jeff.janes@gmail.com> — 2023-03-11T22:13:59Z

   On Tue, Mar 7, 2023 at 7:14 AM Praneel Devisetty <devisettypraneel@gmail.com>
   wrote:
   
   > Hi,
   >
   > I have a query which is taking roughly 10mins to complete and the query
   > planner is choosing a nested loop.
   >
   > query and query plan with analyze,verbose,buffers
   > qsEn | explain.depesz.com <https://explain.depesz.com/s/qsEn#html>
   >
   >
   What version is this?  Any chance you can share this without
   anonymization?  Not knowing the actual names makes it a lot harder to
   understand.  In particular, what is the actual function golf_romeo()? And
   five_two()?  And what is the regexp pattern that is bastardized into
   'oscar_mike'::text ?
   
   
   > Disabling the nested loop on session is allowing the query planner to
   > choose a better plan and complete it in 2mins.Stats are up to date and
   > analyze was performed a few hours ago.
   >
   
   A lot can change in a few hours, do another analyze immediately before
   gathering the execution plan.  Your row estimates are dreadful, but we
   can't really tell why with the info provided.
   
   Cheers,
   
   Jeff