Re: Planner choosing nested loop in place of Hashjoin

[Samed YILDIRIM]

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 
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 
>
> 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
>
>
>

Re: INSERT statement going in IPC Wait_event

[Samed YILDIRIM]

Hello Aditya,

How many connections do you have on your PostgreSQL cluster? And, do your
webserver and database service run on the same machine/VM?

I would check system logs on the server on which PostgreSQL cluster run.

Best regards.
Samed YILDIRIM


On Wed, 1 Mar 2023 at 22:40, aditya desai  wrote:

> Hi All,
> Unfortunately I am unable to share a query  plan or query.
>
> I have a SQL which is getting called from a web service. At a certain
> point where it inserts data in the table . Process is going in a hung
> state. pg_stat_activity shows wait_even='IPC' ,
> wait_even_type=MessageQueueSend. In Webservice log we see I/O error
> occurred message.
>
> Surprisingly when I run it from PSQL or pgadmin it runs fine.
>
> Has anyone come across this issue? Could you please help?
>
> Regards,
> Aditya.
>
>
>

Huge Tables

[André Rodrigues]

Hello Guys

Regarding a particular performance + architecture situation with postgres
12, I have a table with 300 millions rows and then I ask you, which basic
approach like *parameters in postgres.conf*, suitable index type ,
partitions type,  would you suggest me knowing that we have Queries  using
bind with range id  ( primary Key )  +  1 or 2 columns ?


Best regards
Andre

Re: Planner choosing nested loop in place of Hashjoin

[Jeff Janes]

On Tue, Mar 7, 2023 at 7:14 AM Praneel Devisetty 
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 
>
>
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