Postgres Pro
Downloads
Home   >   mailing lists

Re: Planner choosing nested loop in place of Hashjoin - Mailing list pgsql-performance

From Samed YILDIRIM
Subject Re: Planner choosing nested loop in place of Hashjoin
Date
Msg-id CAAo1mb=a9P2UQ4AZaNxVsmnDpqRYOwaopXM9c1zKCz1WVyCUug@mail.gmail.com
Whole thread Raw
In response to Planner choosing nested loop in place of Hashjoin  (Praneel Devisetty <devisettypraneel@gmail.com>)
List pgsql-performance
Tree view
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;


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

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


pgsql-performance by date:

Previous
From: Praneel Devisetty
Date:
Subject: Planner choosing nested loop in place of Hashjoin
Next
From: Samed YILDIRIM
Date:
Subject: Re: INSERT statement going in IPC Wait_event