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

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