Re:Re: Is there any good optimization solution to improve the query efficiency?

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(at)gmail(dot)com> wrote:
>On Mon, 5 Jun 2023 at 18:56, gzh <gzhcoder(at)126(dot)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