Postgresql / PPAS/Query tuning

[완료]/* jobQuesues.distr.getJobQueuesRcmInfoListForPage */

원샷원따봉 2022. 8. 29. 15:16
AS-IS
Total query runtime: 20Min ~
TO-BE
Total query runtime: 0.7 Sec
 
개선효과
99.9%

 

개선 포인트

AS-IS

WHERE C.CONTENTS_ID = D.ALBUM_ID

ORDER BY C.JOB_DATE DESC

 

TO-BE

위의 Order By를 해당 위치로 옮겨줌 추가적으로 PARTITION_NM 을 구할수 있으면 같이 넣어줌

AND A.JOB_DATE BETWEEN '20220817'||'0000000000' AND '20220824'||'2359599999'
AND A.partition_mm = '08'
  ORDER BY JOB_DATE DESC

 

* 원본 쿼리

SELECT /* jobQuesues.distr.getJobQue
uesRcmInfoListForPage */ BB.* FROM(
SELECT (ROW_NUMBER() OVER()) AS RNUM, COUNT(AA.CONTENTS_ID) OVER() AS TOTAL_CNT, AA.* FROM(
SELECT
C.*, D.ALBUM_NAME
 FROM (
SELECT
       TO_CHAR(TO_TIMESTAMP(SUBSTR(B.job_date, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as JOB_DATE,
B.JOB_DATE as REAL_JOB_DATE,
B.JOB_GUBUN,
B.JOB_DETAIL_GUBUN,
B.INSERT_ID,
case when B.JOB_DETAIL_GUBUN = 'H' then TO_CHAR(TO_TIMESTAMP(SUBSTR(B.JOB_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
else case when B.JOB_START_DATE = '00000000000000' and B.COMPLETE_YN = 'N' then '우선처리대기'
else TO_CHAR(TO_TIMESTAMP(substr(B.START_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
end
end as JOB_START_DATE,
case when B.JOB_DETAIL_GUBUN = 'H' then TO_CHAR(TO_TIMESTAMP(substr(B.JOB_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
else TO_CHAR(TO_TIMESTAMP(substr(B.END_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
end as JOB_END_DATE,
B.ALBUM_ID as CONTENTS_ID,
coalesce(B.RESULT, B.COMPLETE_YN) AS COMPLETE_YN,
B.ERROR_DESC AS RESULT_INFO,
B.COMPLETE_YN_A
FROM(
SELECT
    A.JOB_DATE,
A.JOB_GUBUN,
A.JOB_DETAIL_GUBUN,
A.JOB_START_DATE,
A.job_end_date,
A.COMPLETE_YN AS COMPLETE_YN_A,
A.screen_gubun,
A.result_info::jsonb ->> 'START_DATE' as start_date,
A.result_info::jsonb ->> 'END_DATE' as end_date,
jsonb_array_elements((A.result_info::json ->> 'RESULT_LIST')::jsonb) ->> 'ALBUM_ID' AS ALBUM_ID,
jsonb_array_elements((A.result_info::json ->> 'RESULT_LIST')::jsonb) ->> 'RESULT' AS RESULT,
jsonb_array_elements((A.result_info::json ->> 'RESULT_LIST')::jsonb) ->> 'DESC' AS error_desc,
complete_yn,
INSERT_ID
FROM IMCSUSER.PT_LW_JOB_QUEUES A
WHERE A.JOB_GUBUN = 'R'
AND (A.RESULT_INFO::JSON ->> 'FILE_NAME') IS NOT NULL
                         
 
AND A.JOB_DETAIL_GUBUN IN ('M','E','J','L','H')
 
 
AND A.JOB_DATE BETWEEN '20220817'||'0000000000' AND '20220824'||'2359599999'
    and A.partition_mm = '08'
 
) AS B
WHERE 1=1
 
 
AND COALESCE(NULLIF(B.RESULT,''),B.COMPLETE_YN_A) != 'X'
 
 
) C, IMCSUSER.PT_LW_ALBUM_INFO D
WHERE C.CONTENTS_ID = D.ALBUM_ID
 
 
 
 
ORDER BY C.JOB_DATE DESC
) AA
) BB
WHERE BB.RNUM BETWEEN 0+1 AND 0 + 30

 

* 튜닝 쿼리


SELECT /* jobQuesues.distr.getJobQueuesRcmInfoListForPage */ BB.* FROM(
SELECT (ROW_NUMBER() OVER()) AS RNUM, COUNT(AA.CONTENTS_ID) OVER() AS TOTAL_CNT, AA.* FROM(
SELECT
C.*, D.ALBUM_NAME
 FROM (
SELECT
       TO_CHAR(TO_TIMESTAMP(SUBSTR(B.job_date, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as JOB_DATE,
B.JOB_DATE as REAL_JOB_DATE,
B.JOB_GUBUN,
B.JOB_DETAIL_GUBUN,
B.INSERT_ID,
case when B.JOB_DETAIL_GUBUN = 'H' then TO_CHAR(TO_TIMESTAMP(SUBSTR(B.JOB_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
else case when B.JOB_START_DATE = '00000000000000' and B.COMPLETE_YN = 'N' then '우선처리대기'
else TO_CHAR(TO_TIMESTAMP(substr(B.START_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
end
end as JOB_START_DATE,
case when B.JOB_DETAIL_GUBUN = 'H' then TO_CHAR(TO_TIMESTAMP(substr(B.JOB_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
else TO_CHAR(TO_TIMESTAMP(substr(B.END_DATE, 1, 14), 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')
end as JOB_END_DATE,
B.ALBUM_ID as CONTENTS_ID,
coalesce(B.RESULT, B.COMPLETE_YN) AS COMPLETE_YN,
B.ERROR_DESC AS RESULT_INFO,
B.COMPLETE_YN_A
FROM(
SELECT
    A.JOB_DATE,
A.JOB_GUBUN,
A.JOB_DETAIL_GUBUN,
A.JOB_START_DATE,
A.job_end_date,
A.COMPLETE_YN AS COMPLETE_YN_A,
A.screen_gubun,
A.result_info::jsonb ->> 'START_DATE' as start_date,
A.result_info::jsonb ->> 'END_DATE' as end_date,
jsonb_array_elements((A.result_info::json ->> 'RESULT_LIST')::jsonb) ->> 'ALBUM_ID' AS ALBUM_ID,
jsonb_array_elements((A.result_info::json ->> 'RESULT_LIST')::jsonb) ->> 'RESULT' AS RESULT,
jsonb_array_elements((A.result_info::json ->> 'RESULT_LIST')::jsonb) ->> 'DESC' AS error_desc,
complete_yn,
INSERT_ID
FROM IMCSUSER.PT_LW_JOB_QUEUES A
WHERE A.JOB_GUBUN = 'R'
AND (A.RESULT_INFO::JSON ->> 'FILE_NAME') IS NOT NULL
                         
 
AND A.JOB_DETAIL_GUBUN IN ('M','E','J','L','H')
 
 
AND A.JOB_DATE BETWEEN '20220817'||'0000000000' AND '20220824'||'2359599999'
 
  ORDER BY JOB_DATE DESC  
) AS B
WHERE 1=1
 
 
AND COALESCE(NULLIF(B.RESULT,''),B.COMPLETE_YN_A) != 'X'

 
) C, IMCSUSER.PT_LW_ALBUM_INFO D
WHERE C.CONTENTS_ID = D.ALBUM_ID
 

) AA
) BB
WHERE BB.RNUM BETWEEN 0+1 AND 0 + 30 ;
저작자표시 비영리 변경금지 (새창열림)