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

I made some slight changes to the SQL you provided, but the optimization approach remained the same. 

I was surprised that the results were retrieved in less than one second. It's really impressive!

Below is the execution plan. Thank you very much for providing the optimization method, I learned a lot from it.

explain analyse

with t_res as

(select RSNO, KNO, CRSNO

    from tbl_res

   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')),

t_pov2 as

(select t_cust.RSNO, t_cust.KNO, MIN(t_cust.GSTSEQ) GSTSEQ

    from t_res -- this is tbl_res already filter by date

   inner join tbl_cust t_cust

      on t_res.RSNO = t_cust.RSNO

   inner join tbl_pov t_pov

      on t_pov.CRSNO = t_res.CRSNO -- why you use this table? it doesn't seem to be used to extract data. Are you trying to extract data from t_res that have at least a record in t_pov? in this case could work better move this join in the first with (using distinct or group by to ensure there will be just a record for RSNO and KNO)

   where t_cust.STSFLG = 'T'

     and t_cust.DISPSEQ <> 9999

     AND t_cust.KFIX = '0'

   group by t_cust.RSNO, t_cust.KNO),

t_pov3 as

(select t_cust.RSNO RSNO2, t_cust.KNO, t_cust.AGE, t_cust.GST

    from tbl_cust t_cust

   inner join t_pov2

      on t_pov2.RSNO = t_cust.RSNO

     and t_pov2.KNO = t_cust.KNO

     and t_pov2.GSTSEQ = t_cust.GSTSEQ)

select *

  from t_res

  left outer join t_pov3

    on t_res.RSNO = t_pov3.RSNO2

   and t_res.KNO = t_pov3.KNO

----- execution plan -----

Hash Right Join  (cost=125923.21..132076.05 rows=472 width=164) (actual time=408.252..410.342 rows=15123 loops=1)

  Hash Cond: ((t_cust.RSNO = t_res.RSNO) AND ((t_cust.KNO)::text = (t_res.KNO)::text))

  CTE t_res

    ->  Gather  (cost=1000.00..58410.51 rows=472 width=27) (actual time=55.587..207.684 rows=15123 loops=1)

          Workers Planned: 2

          Workers Launched: 2

          ->  Parallel Seq Scan on tbl_res  (cost=0.00..57363.31 rows=197 width=27) (actual time=49.850..204.235 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

  ->  Nested Loop  (cost=67496.18..73648.88 rows=1 width=56) (actual time=191.880..191.924 rows=11 loops=1)

        ->  GroupAggregate  (cost=67495.75..67510.49 rows=737 width=50) (actual time=191.869..191.878 rows=11 loops=1)

              Group Key: t_cust_1.RSNO, t_cust_1.KNO

              ->  Sort  (cost=67495.75..67497.59 rows=737 width=23) (actual time=191.859..191.862 rows=13 loops=1)

                    Sort Key: t_cust_1.RSNO, t_cust_1.KNO

                    Sort Method: quicksort  Memory: 26kB

                    ->  Nested Loop  (cost=57118.88..67460.65 rows=737 width=23) (actual time=172.185..191.837 rows=13 loops=1)

                          ->  Hash Join  (cost=57118.45..58758.38 rows=472 width=14) (actual time=172.154..191.647 rows=13 loops=1)

                                Hash Cond: ((t_res_1.crsno)::text = (t_pov.crsno)::text)

                                ->  CTE Scan on t_res t_res_1  (cost=0.00..9.44 rows=472 width=72) (actual time=0.003..1.445 rows=15123 loops=1)

                                ->  Hash  (cost=51380.09..51380.09 rows=330109 width=9) (actual time=170.350..170.350 rows=330109 loops=1)

                                      Buckets: 131072  Batches: 8  Memory Usage: 2707kB

                                      ->  Seq Scan on tbl_pov t_pov  (cost=0.00..51380.09 rows=330109 width=9) (actual time=0.029..124.632 rows=330109 loops=1)

                          ->  Index Scan using tbl_cust_pk on tbl_cust t_cust_1  (cost=0.43..18.42 rows=2 width=23) (actual time=0.011..0.012 rows=1 loops=13)

                                Index Cond: (RSNO = t_res_1.RSNO)

                                Filter: ((dispseq <> '9999'::numeric) AND ((stsflg)::text = 'T'::text) AND ((KFIX)::text = '0'::text))

                                Rows Removed by Filter: 2

        ->  Index Scan using tbl_cust_pk on tbl_cust t_cust  (cost=0.43..8.31 rows=1 width=61) (actual time=0.003..0.003 rows=1 loops=11)

              Index Cond: ((RSNO = t_cust_1.RSNO) AND (gstseq = (min(t_cust_1.gstseq))))

              Filter: ((t_cust_1.KNO)::text = (KNO)::text)

  ->  Hash  (cost=9.44..9.44 rows=472 width=108) (actual time=216.361..216.361 rows=15123 loops=1)

        Buckets: 16384 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 882kB

        ->  CTE Scan on t_res  (cost=0.00..9.44 rows=472 width=108) (actual time=55.591..211.698 rows=15123 loops=1)

Planning Time: 1.417 ms

Execution Time: 411.019 ms

--------------------------------------------------------------------------------

At 2023-06-05 22:53:56, "Lorusso Domenico" <domenico.l76@gmail.com> wrote:

try this (there is some comment)

with t_res as (
select RSNO,  KNO
from TBL_RES
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')
), t_pov2 as (
select T_CUST.RSNO ,
T_CUST.KNO ,
MIN(T_CUST.GSTSEQ) GSTSEQ
from T_RES -- this is tbl_res already filter by date
inner join TBL_CUST T_CUST on T_RES.RSNO = T_CUST.RSNO
inner join TBL_POV T_POV on T_POV.CRSNO = T_RES.CRSNO -- why you use this table? it doesn't seem to be used to extract data. Are you trying to extract data from T_RES that have at least a record in T_POV? in this case could work better move this join in the first with (using distinct or group by to ensure there will be just a record for rsno and kno)
where T_CUST.STSFLG = 'T'
and T_CUST.DISPSEQ <> 9999
AND T_CUST.KFIX = '0'
group by T_CUST.RSNO , T_CUST.KNO
), t_pov3 as (
select T_CUST.RSNO RSNO2 ,
T_CUST.KNO ,
T_CUST.AGE ,
T_CUST.GST
from TBL_CUST T_CUST
inner join t_pov2 on T_POV2.RSNO = T_CUST.RSNO
and T_POV2.KNO = T_CUST.KNO
and T_POV2.GSTSEQ = T_CUST.GSTSEQ
)
select *
from TBL_RES
left outer join t_pov3 on TBL_RES.RSNO = T_POV3.RSNO2
and TBL_RES.KNO = T_POV3.KNO       

Il giorno lun 5 giu 2023 alle ore 12:06 gzh <gzhcoder@126.com> ha scritto:

Thank you very much for taking the time to reply to my question. 

I followed your suggestion and rewrote the SQL using Common Table Expression (CTE). 

Unfortunately, there was no significant improvement in performance.

At 2023-06-05 17:47:25, "Lorusso Domenico" <domenico.l76@gmail.com> wrote:

Hello,

In many case a formal writing and usage of with statement could solve the issue.

If you need join, use always join:
where T_POV2.RSNO = T_CUST.RSNO
and T_POV2.KNO = T_CUST.KNO
and T_POV2.GSTSEQ = T_CUST.GSTSEQ)

this is an inner join.

I mean something like this

with t_pov2 as (
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_pov3 as (
select T_CUST.RSNO RSNO2 ,
T_CUST.KNO ,
T_CUST.AGE ,
T_CUST.GST
from TBL_CUST T_CUST
inner join t_pov2 on T_POV2.RSNO = T_CUST.RSNO
and T_POV2.KNO = T_CUST.KNO
and T_POV2.GSTSEQ = T_CUST.GSTSEQ
)

select *
from TBL_RES
left outer join 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')

but if tbl_res contain lessere record a good idea is start from this table and use in join with other

Il giorno lun 5 giu 2023 alle ore 08:57 gzh <gzhcoder@126.com> ha scritto:

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!

--

Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

--

Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]