Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q657Z-0003L4-RC for pgsql-general@arkaria.postgresql.org; Mon, 05 Jun 2023 07:59:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q657W-0001Is-Cp for pgsql-general@arkaria.postgresql.org; Mon, 05 Jun 2023 07:59:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q657W-0001Ii-03 for pgsql-general@lists.postgresql.org; Mon, 05 Jun 2023 07:59:18 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q657P-000Kr4-Oi for pgsql-general@lists.postgresql.org; Mon, 05 Jun 2023 07:59:17 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-ba1815e12efso3882435276.3 for ; Mon, 05 Jun 2023 00:59:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1685951950; x=1688543950; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=x3LUvcolLAoaUvppIvogPvgkV+fGZliIVPIOIpU4x14=; b=aekM3PPgPMqLZFKKaE+JAGJL0DSjRoTuklbx/rM7RsutU9x9gWVmrKuIjB7R2nNCXT mAGg7bVk6uxpcD1DJrffFOiW76CdAFXyo+Vu2F9t2PJc4B1JKyWufSVXXwA/iSpYLubV NCJ8HOB3NKHOkYSjTGiiTDPOEKXz4H0+nTWMWjfixp1c8TmnbXiRppkrENol2TlVRriU MjKZprR2mARWyQjmOE1QFT1/lZoss43AsipDBxsJp6dYSo6ToTPsU7VkdQtfKfM+H6gL NJ8GL1HLdZ0rC9xF4kfliKdniGqGaU5WgG37uS6cPhzX8CtKaiglh6gfO5R/MbrJZ/Fr OD7g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685951950; x=1688543950; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=x3LUvcolLAoaUvppIvogPvgkV+fGZliIVPIOIpU4x14=; b=KJPCDlcKyWOgIku06CAzoQfyevrFel4Lr+u0wBZ/gGKfBjk2R+4uaTWL8XjCZDLgi4 nNg8dE4gFwFXpuWwfeIUi0uuGluxYQJPD29CIy1j4h+rJIy1sz23uD2EsKZyzDAic/wi jLPslvf4y0rV1wPnzjtJAKf1CWYxQreYRI1Ly7rJhjAcBcfhaaCP2uLo5EHl1hoYnE5r 3x0CrVuZMbSoYz+WoPnSZFZZJ40LnAqxvlFfWLfbBy8GFb/fjYb3ma3KkI92guSoFYZq EoftWcygxf/0SduT+OSGefYV61bMnbl3qiMIpSjxmkwaExRR9vOuuLOMdRy3MhoHedyj +Ubw== X-Gm-Message-State: AC+VfDwJtaU+rzeI5JgIQOJTcsKM1T12AALIjuIcJVEUz81r3TEKu7EZ hg5YjybtNJF/44l3JA2pM0XW1LfWhAM= X-Google-Smtp-Source: ACHHUZ4PZOXAjfw2yWa0bJqi3dB1YaoBAImwFR5rdHn1I398BDdCL2TvFoAypyEsPgiSaUCOwG7R1w== X-Received: by 2002:a81:4f0e:0:b0:561:b43f:e54e with SMTP id d14-20020a814f0e000000b00561b43fe54emr7146244ywb.49.1685951949783; Mon, 05 Jun 2023 00:59:09 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:1cf:a1ef:84f7:c68a? ([2600:1700:bd2c:b810:1cf:a1ef:84f7:c68a]) by smtp.googlemail.com with ESMTPSA id h66-20020a0dde45000000b0055d7f00d4f7sm3095635ywe.22.2023.06.05.00.59.09 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 05 Jun 2023 00:59:09 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------0i9hIIyP7S8rmHE6rRKo1efH" Message-ID: <9b227a10-3769-7c8f-e95a-6023e18d5097@gmail.com> Date: Mon, 5 Jun 2023 02:59:09 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Is there any good optimization solution to improve the query efficiency? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <31b30205.4e00.1888a5803f1.Coremail.gzhcoder@126.com> From: Ron In-Reply-To: <31b30205.4e00.1888a5803f1.Coremail.gzhcoder@126.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0i9hIIyP7S8rmHE6rRKo1efH Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Indices on TBL_RES.CID, TBL_RES.COD and the "join columns"? Have you vacuumed and analyzed the tables lately? Oliver's comment about first optimizing the individual subselects is also SOP. On 6/5/23 01:56, gzh wrote: > > Hi everyone, > > I'm running into some performance issues with my SQL query. > The following SQL query is taking a long time to execute. > > Execution Plan: > explain analyse > select * from TBL_RES > left outer join(select T_CUST.RSNO RSNO2 , > T_CUST.KNO , > T_CUST.AGE , > T_CUST.GST > from TBL_CUST T_CUST , > (select T_CUST.RSNO , > T_CUST.KNO , > MIN(T_CUST.GSTSEQ) GSTSEQ > from TBL_CUST T_CUST , > TBL_POV T_POV , > TBL_RES T_RES > where T_CUST.STSFLG = 'T' > and T_CUST.DISPSEQ <> 9999 > AND T_CUST.KFIX = '0' > and T_POV.CRSNO = T_RES.CRSNO > and T_RES.RSNO = T_CUST.RSNO > group by T_CUST.RSNO , T_CUST.KNO) T_POV2 > where T_POV2.RSNO = T_CUST.RSNO > and T_POV2.KNO = T_CUST.KNO > and T_POV2.GSTSEQ = T_CUST.GSTSEQ) T_POV3 on TBL_RES.RSNO = T_POV3.RSNO2 > and TBL_RES.KNO = T_POV3.KNO > where TBL_RES.CID >= to_date('2022/07/01', 'YYYY/MM/DD') > and TBL_RES.CID <= to_date('2022/07/31', 'YYYY/MM/DD') > and TBL_RES.COD >= to_date('2022/07/01', 'YYYY/MM/DD') > and TBL_RES.COD <= to_date('2022/07/31', 'YYYY/MM/DD') > ----- Execution Plan ----- > Nested Loop Left Join  (cost=254388.44..452544.70 rows=473 width=3545) > (actual time=3077.312..996048.714 rows=15123 loops=1) >   Join Filter: ((TBL_RES.RSNO = T_CUST.RSNO) AND ((TBL_RES.KNO)::text = > (T_CUST.KNO)::text)) >   Rows Removed by Join Filter: 4992268642 >   ->  Gather  (cost=1000.00..58424.35 rows=473 width=3489) (actual > time=0.684..14.158 rows=15123 loops=1) >         Workers Planned: 2 >         Workers Launched: 2 >         ->  Parallel Seq Scan on TBL_RES (cost=0.00..57377.05 rows=197 > width=3489) (actual time=0.096..279.504 rows=5041 loops=3) >               Filter: ((CID >= to_date('2022/07/01'::text, > 'YYYY/MM/DD'::text)) AND (CID <= to_date('2022/07/31'::text, > 'YYYY/MM/DD'::text)) AND (COD >= to_date('2022/07/01'::text, > 'YYYY/MM/DD'::text)) AND (COD <= to_date('2022/07/31'::text, > 'YYYY/MM/DD'::text))) >               Rows Removed by Filter: 161714 >   ->  Materialize  (cost=253388.44..394112.08 rows=1 width=56) (actual > time=0.081..26.426 rows=330111 loops=15123) >         ->  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) >                     Buckets: 65536  Batches: 8  Memory Usage: 2773kB >                     ->  Finalize GroupAggregate (cost=205244.84..243606.02 > rows=262488 width=50) (actual time=788.552..1116.074 rows=330111 loops=1) >                           Group Key: T_CUST_1.RSNO, T_CUST_1.KNO >                           ->  Gather Merge (cost=205244.84..238964.80 > rows=268846 width=50) (actual time=788.547..982.479 rows=330111 loops=1) >                                 Workers Planned: 2 >                                 Workers Launched: 1 >                                 ->  Partial GroupAggregate  > (cost=204244.81..206933.27 rows=134423 width=50) (actual > time=784.032..900.979 rows=165056 loops=2) >                                       Group Key: T_CUST_1.RSNO, T_CUST_1.KNO >                                       ->  Sort (cost=204244.81..204580.87 > rows=134423 width=23) (actual time=784.019..833.791 rows=165061 loops=2) >                                             Sort Key: T_CUST_1.RSNO, > T_CUST_1.KNO >                                             Sort Method: external merge  > Disk: 5480kB >                                             Worker 0: Sort Method: > external merge  Disk: 5520kB >                                             -> Parallel Hash Join  > (cost=111758.80..190036.38 rows=134423 width=23) (actual > time=645.302..716.247 rows=165061 loops=2) >                                                   Hash Cond: > (T_CUST_1.RSNO = T_RES.RSNO) >                                                   -> Parallel Seq Scan on > TBL_CUST T_CUST_1  (cost=0.00..74013.63 rows=204760 width=23) (actual > time=0.018..264.390 rows=165058 loops=2) > Filter: ((dispseq <> '9999'::numeric) AND ((stsflg)::text = 'T'::text) AND > ((KFIX)::text = '0'::text)) > Rows Removed by Filter: 835318 >                                                   -> Parallel Hash  > (cost=109508.52..109508.52 rows=137142 width=8) (actual > time=343.593..343.896 rows=165058 loops=2) > Buckets: 131072  Batches: 8  Memory Usage: 3008kB > ->  Parallel Hash Join  (cost=51834.70..109508.52 rows=137142 width=8) > (actual time=256.732..314.368 rows=165058 loops=2) >     Hash Cond: ((T_RES.crsno)::text = (T_POV.crsno)::text) >     ->  Parallel Seq Scan on TBL_RES T_RES (cost=0.00..53199.02 > rows=208902 width=17) (actual time=0.007..100.510 rows=250132 loops=2) >     ->  Parallel Hash  (cost=49450.42..49450.42 rows=137142 width=9) > (actual time=122.308..122.309 rows=165054 loops=2) >           Buckets: 131072  Batches: 8  Memory Usage: 2976kB >           ->  Parallel Seq Scan on TBL_POV T_POV (cost=0.00..49450.42 > rows=137142 width=9) (actual time=0.037..89.470 rows=165054 loops=2) > Planning Time: 1.064 ms > Execution Time: 996062.382 ms > -------------------------------------------------------------------------------- > > The amount of data in the table is as follows. > TBL_RES    500265 > TBL_CUST   2000752 > TBL_POV    330109 > > Any suggestions for improving the performance of the query would be > greatly appreciated. > > Thanks in advance! > -- Born in Arizona, moved to Babylonia. --------------0i9hIIyP7S8rmHE6rRKo1efH Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Indices on TBL_RES.CID, TBL_RES.COD and the "join columns"?
Have you vacuumed and analyzed the tables lately?
Oliver's comment about first optimizing the individual subselects is also SOP.


On 6/5/23 01:56, gzh wrote:

Hi everyone,

I'm running into some performance issues with my SQL query.
The following SQL query is taking a long time to execute.

Execution Plan:
explain analyse
select * from TBL_RES
left outer join(select T_CUST.RSNO RSNO2 ,
T_CUST.KNO ,
T_CUST.AGE ,
T_CUST.GST
from TBL_CUST T_CUST ,
(select T_CUST.RSNO ,
T_CUST.KNO ,
MIN(T_CUST.GSTSEQ) GSTSEQ
from TBL_CUST T_CUST ,
TBL_POV T_POV ,
TBL_RES T_RES
where T_CUST.STSFLG = 'T'
and T_CUST.DISPSEQ <> 9999
AND T_CUST.KFIX = '0'
and T_POV.CRSNO = T_RES.CRSNO
and T_RES.RSNO = T_CUST.RSNO
group by T_CUST.RSNO , T_CUST.KNO) T_POV2
where T_POV2.RSNO = T_CUST.RSNO
and T_POV2.KNO = T_CUST.KNO
and T_POV2.GSTSEQ = T_CUST.GSTSEQ) T_POV3 on TBL_RES.RSNO = T_POV3.RSNO2
and TBL_RES.KNO = T_POV3.KNO
where TBL_RES.CID >= to_date('2022/07/01', 'YYYY/MM/DD')
and TBL_RES.CID <= to_date('2022/07/31', 'YYYY/MM/DD')
and TBL_RES.COD >= to_date('2022/07/01', 'YYYY/MM/DD')
and TBL_RES.COD <= to_date('2022/07/31', 'YYYY/MM/DD')
----- Execution Plan -----
Nested Loop Left Join  (cost=254388.44..452544.70 rows=473 width=3545) (actual time=3077.312..996048.714 rows=15123 loops=1)
  Join Filter: ((TBL_RES.RSNO = T_CUST.RSNO) AND ((TBL_RES.KNO)::text = (T_CUST.KNO)::text))
  Rows Removed by Join Filter: 4992268642
  ->  Gather  (cost=1000.00..58424.35 rows=473 width=3489) (actual time=0.684..14.158 rows=15123 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Seq Scan on TBL_RES  (cost=0.00..57377.05 rows=197 width=3489) (actual time=0.096..279.504 rows=5041 loops=3)
              Filter: ((CID >= to_date('2022/07/01'::text, 'YYYY/MM/DD'::text)) AND (CID <= to_date('2022/07/31'::text, 'YYYY/MM/DD'::text)) AND (COD >= to_date('2022/07/01'::text, 'YYYY/MM/DD'::text)) AND (COD <= to_date('2022/07/31'::text, 'YYYY/MM/DD'::text)))
              Rows Removed by Filter: 161714
  ->  Materialize  (cost=253388.44..394112.08 rows=1 width=56) (actual time=0.081..26.426 rows=330111 loops=15123)
        ->  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)
                    Buckets: 65536  Batches: 8  Memory Usage: 2773kB
                    ->  Finalize GroupAggregate  (cost=205244.84..243606.02 rows=262488 width=50) (actual time=788.552..1116.074 rows=330111 loops=1)
                          Group Key: T_CUST_1.RSNO, T_CUST_1.KNO
                          ->  Gather Merge  (cost=205244.84..238964.80 rows=268846 width=50) (actual time=788.547..982.479 rows=330111 loops=1)
                                Workers Planned: 2
                                Workers Launched: 1
                                ->  Partial GroupAggregate  (cost=204244.81..206933.27 rows=134423 width=50) (actual time=784.032..900.979 rows=165056 loops=2)
                                      Group Key: T_CUST_1.RSNO, T_CUST_1.KNO
                                      ->  Sort  (cost=204244.81..204580.87 rows=134423 width=23) (actual time=784.019..833.791 rows=165061 loops=2)
                                            Sort Key: T_CUST_1.RSNO, T_CUST_1.KNO
                                            Sort Method: external merge  Disk: 5480kB
                                            Worker 0:  Sort Method: external merge  Disk: 5520kB
                                            ->  Parallel Hash Join  (cost=111758.80..190036.38 rows=134423 width=23) (actual time=645.302..716.247 rows=165061 loops=2)
                                                  Hash Cond: (T_CUST_1.RSNO = T_RES.RSNO)
                                                  ->  Parallel Seq Scan on TBL_CUST T_CUST_1  (cost=0.00..74013.63 rows=204760 width=23) (actual time=0.018..264.390 rows=165058 loops=2)
                                                        Filter: ((dispseq <> '9999'::numeric) AND ((stsflg)::text = 'T'::text) AND ((KFIX)::text = '0'::text))
                                                        Rows Removed by Filter: 835318
                                                  ->  Parallel Hash  (cost=109508.52..109508.52 rows=137142 width=8) (actual time=343.593..343.896 rows=165058 loops=2)
                                                        Buckets: 131072  Batches: 8  Memory Usage: 3008kB
                                                        ->  Parallel Hash Join  (cost=51834.70..109508.52 rows=137142 width=8) (actual time=256.732..314.368 rows=165058 loops=2)
                                                              Hash Cond: ((T_RES.crsno)::text = (T_POV.crsno)::text)
                                                              ->  Parallel Seq Scan on TBL_RES T_RES  (cost=0.00..53199.02 rows=208902 width=17) (actual time=0.007..100.510 rows=250132 loops=2)
                                                              ->  Parallel Hash  (cost=49450.42..49450.42 rows=137142 width=9) (actual time=122.308..122.309 rows=165054 loops=2)
                                                                    Buckets: 131072  Batches: 8  Memory Usage: 2976kB
                                                                    ->  Parallel Seq Scan on TBL_POV T_POV  (cost=0.00..49450.42 rows=137142 width=9) (actual time=0.037..89.470 rows=165054 loops=2)
Planning Time: 1.064 ms
Execution Time: 996062.382 ms
--------------------------------------------------------------------------------

The amount of data in the table is as follows.
TBL_RES    500265
TBL_CUST   2000752
TBL_POV    330109

Any suggestions for improving the performance of the query would be greatly appreciated.

Thanks in advance!


--
Born in Arizona, moved to Babylonia.
--------------0i9hIIyP7S8rmHE6rRKo1efH--