Re: Is there any good optimization solution to improve the query efficiency? - Mailing list pgsql-general

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.005rows=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