From: gzh <gzhcoder@126.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re:Re: Is there any good optimization solution to improve the query efficiency?
Date: Mon, 5 Jun 2023 17:38:55 +0800 (CST)
Message-ID: <1a6cd283.79df.1888aec8088.Coremail.gzhcoder@126.com> (raw)
In-Reply-To: <CAApHDvro3ggJSxMOa_=T0=jDOMpVqs5gfDa6JmWegKSBkJ3g=w@mail.gmail.com>
References: <31b30205.4e00.1888a5803f1.Coremail.gzhcoder@126.com>
<CAApHDvro3ggJSxMOa_=T0=jDOMpVqs5gfDa6JmWegKSBkJ3g=w@mail.gmail.com>
Hi, David
>The above join's selectivity estimation seems to be causing an upper
>join to resort to performing a Nested Loop join because the planner
>thinks the join will only produce 1 row.
>
>Unfortunately, extended statistics only help for base relation
>estimations and do nothing for join estimations, so your best bet
>might be to just:
>
>SET enable_nestloop TO off;
>
>for this query.
After making the adjustments as you suggested,
the SQL statement that previously took 16 minutes to query results can now be queried in less than 10 seconds.
Thank you very much for taking the time to reply to my question and providing a solution that solved my issue.
Your expertise and willingness to help are greatly appreciated, and I learned a lot from your answer.
Thanks again!
At 2023-06-05 16:21:19, "David Rowley" <dgrowleyml@gmail.com> wrote:
>On Mon, 5 Jun 2023 at 18:56, gzh <gzhcoder@126.com> wrote:
>> I'm running into some performance issues with my SQL query.
>> The following SQL query is taking a long time to execute.
>
>> -> Hash Join (cost=253388.44..394112.07 rows=1 width=56) (actual time=1197.484..2954.084 rows=330111 loops=1)
>> Hash Cond: ((T_CUST.RSNO = T_CUST_1.RSNO) AND ((T_CUST.KNO)::text = (T_CUST_1.KNO)::text) AND (T_CUST.gstseq = (min(T_CUST_1.gstseq))))
>> -> Seq Scan on TBL_CUST T_CUST (cost=0.00..79431.15 rows=2000315 width=61) (actual time=0.015..561.005 rows=2000752 loops=1)
>> -> Hash (cost=246230.90..246230.90 rows=262488 width=50) (actual time=1197.025..1209.957 rows=330111 loops=1)
>
>The above join's selectivity estimation seems to be causing an upper
>join to resort to performing a Nested Loop join because the planner
>thinks the join will only produce 1 row.
>
>Unfortunately, extended statistics only help for base relation
>estimations and do nothing for join estimations, so your best bet
>might be to just:
>
>SET enable_nestloop TO off;
>
>for this query.
>
>David
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: gzhcoder@126.com, dgrowleyml@gmail.com
Subject: Re: Is there any good optimization solution to improve the query efficiency?
In-Reply-To: <1a6cd283.79df.1888aec8088.Coremail.gzhcoder@126.com>
References: <31b30205.4e00.1888a5803f1.Coremail.gzhcoder@126.com> <CAApHDvro3ggJSxMOa_=T0=jDOMpVqs5gfDa6JmWegKSBkJ3g=w@mail.gmail.com> <1a6cd283.79df.1888aec8088.Coremail.gzhcoder@126.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox