Postgresql / PPAS/Query tuning
[완료]/*stillCut.base.stillCutListPageData*/
원샷원따봉
2022. 8. 29. 15:21
개선 효과
튜닝 전 : 5초 튜닝 후 : 0.1초 개선 효과 : 99.98%
|
* 원본 쿼리
SELECT /*stillCut.base.stillCutListPageData*/ TOTAL_CNT , RNUM , SCREEN_TYPE , ALBUM_ID , ALBUM_NAME , ONAIR_DATE , SERIES_NO , TO_CHAR(TO_TIMESTAMP(ALBUM_DATE,'YYYYMMDDhh24miss'), 'YYYY-MM-DD hh24:mi:ss') AS ALBUM_DATE , SERVICE_ICON , CATEGORY_YN , SUGGESTED_PRICE , STILL_YN FROM ( SELECT COUNT(AI.ALBUM_ID) OVER() AS TOTAL_CNT , ROW_NUMBER() OVER (ORDER BY AI.ALBUM_DATE DESC NULLS LAST, AI.ALBUM_ID, AST.SCREEN_TYPE) RNUM , AST.SCREEN_TYPE , AI.ALBUM_ID , AI.ALBUM_NAME , AI.ONAIR_DATE , AI.SERIES_NO , AI.ALBUM_DATE , MAX(AP.SERVICE_ICON) AS SERVICE_ICON -- KEEP ( DENSE_RANK FIRST ORDER BY AI.ALBUM_ID, AP.SCREEN_TYPE) AS SERVICE_ICON , (CASE WHEN (SELECT COUNT(AA.VOD_CONTENTS_ID) FROM VODUSER.PT_VO_CATEGORY_MAP_UNITED AA ,VODUSER.PT_VO_CATEGORY_UNITED BB WHERE AA.VOD_CATEGORY_ID = BB.VOD_CATEGORY_ID AND AA.VOD_CONTENTS_ID = AI.ALBUM_ID ) > 0 THEN 'Y'ELSE 'N' END ) AS CATEGORY_YN , AST.SUGGESTED_PRICE , CASE WHEN COALESCE(IMG.ADI_ALBUM_ID, '') = '' THEN 'N' ELSE 'Y' END AS STILL_YN FROM imcsuser.PT_LA_ALBUM_INFO AI INNER JOIN imcsuser.PT_LA_ALBUM_PLATFORM AP ON AI.ALBUM_ID = AP.ALBUM_ID INNER JOIN IMCSUSER.PT_LA_ASSET_INFO AST ON AI.ALBUM_ID = AST.ALBUM_ID AND AP.SCREEN_TYPE = AST.SCREEN_TYPE LEFT JOIN ( SELECT ADI_ALBUM_ID FROM imcsuser.PT_LA_ALBUM_IMG WHERE 1 = 1 ) IMG ON AI.ALBUM_ID = IMG.ADI_ALBUM_ID WHERE 1=1 AND AST.SCREEN_TYPE IN ('N', 'I') AND INSTR(UPPER(REPLACE(AI.ALBUM_NAME, ' ', '')) ,UPPER(REPLACE('시리즈_081022',' ',''))) > 0 GROUP BY AI.ALBUM_ID , AI.ALBUM_NAME , AI.ONAIR_DATE , AI.SERIES_NO , AI.ALBUM_DATE , AST.SCREEN_TYPE , AST.SUGGESTED_PRICE , IMG.ADI_ALBUM_ID )AA WHERE AA.RNUM BETWEEN 0+1 AND 0 + 30 |
* 튜닝 쿼리
SELECT /*stillCut.base.stillCutListPageData*/ TOTAL_CNT , RNUM , SCREEN_TYPE , ALBUM_ID , ALBUM_NAME , ONAIR_DATE , SERIES_NO , TO_CHAR(TO_TIMESTAMP(ALBUM_DATE,'YYYYMMDDhh24miss'), 'YYYY-MM-DD hh24:mi:ss') AS ALBUM_DATE , SERVICE_ICON , CATEGORY_YN , SUGGESTED_PRICE , STILL_YN FROM ( SELECT COUNT(AI.ALBUM_ID) OVER() AS TOTAL_CNT , ROW_NUMBER() OVER (ORDER BY AI.ALBUM_DATE DESC NULLS LAST, AI.ALBUM_ID, AST.SCREEN_TYPE) RNUM , AST.SCREEN_TYPE , AI.ALBUM_ID , AI.ALBUM_NAME , AI.ONAIR_DATE , AI.SERIES_NO , AI.ALBUM_DATE , MAX(AP.SERVICE_ICON) AS SERVICE_ICON -- KEEP ( DENSE_RANK FIRST ORDER BY AI.ALBUM_ID, AP.SCREEN_TYPE) AS SERVICE_ICON , (CASE WHEN (SELECT COUNT(AA.VOD_CONTENTS_ID) FROM VODUSER.PT_VO_CATEGORY_MAP_UNITED AA ,VODUSER.PT_VO_CATEGORY_UNITED BB WHERE AA.VOD_CATEGORY_ID = BB.VOD_CATEGORY_ID AND AA.VOD_CONTENTS_ID = AI.ALBUM_ID ) > 0 THEN 'Y'ELSE 'N' END ) AS CATEGORY_YN , AST.SUGGESTED_PRICE , CASE WHEN COALESCE(IMG.ADI_ALBUM_ID, '') = '' THEN 'N' ELSE 'Y' END AS STILL_YN FROM imcsuser.PT_LA_ALBUM_INFO AI INNER JOIN imcsuser.PT_LA_ALBUM_PLATFORM AP ON AI.ALBUM_ID = AP.ALBUM_ID INNER JOIN IMCSUSER.PT_LA_ASSET_INFO AST ON AI.ALBUM_ID = AST.ALBUM_ID AND AP.SCREEN_TYPE = AST.SCREEN_TYPE LEFT JOIN ( SELECT ADI_ALBUM_ID FROM imcsuser.PT_LA_ALBUM_IMG WHERE 1 = 1 ) IMG ON AI.ALBUM_ID = IMG.ADI_ALBUM_ID WHERE 1=1 AND AST.SCREEN_TYPE IN ('N', 'I') AND AI.ALBUM_NAME ilike '%시리즈_081022%' -- AND INSTR(UPPER(REPLACE(AI.ALBUM_NAME, ' ', '')) ,UPPER(REPLACE('시리즈_081022',' ',''))) > 0 GROUP BY AI.ALBUM_ID , AI.ALBUM_NAME , AI.ONAIR_DATE , AI.SERIES_NO , AI.ALBUM_DATE , AST.SCREEN_TYPE , AST.SUGGESTED_PRICE , IMG.ADI_ALBUM_ID )AA WHERE AA.RNUM BETWEEN 0+1 AND 0 + 30; |