Postgresql / PPAS/Query tuning

[완료]/*stillCut.base.stillCutListPageData*/

원샷원따봉 2022. 8. 29. 15:21

개선 효과

튜닝 전 : 5초
튜닝 후 : 0.1초
개선 효과 : 99.98%
  • 문자열 like 검색에 대해 GIN 인덱스 생성
  • 문자열 검색 부분 sql 변경
  • CREATE INDEX idx_pt_la_album_info_04 ON imcsuser.pt_la_album_info USING GIN(album_name imcsuser.gin_trgm_ops);

 

* 원본 쿼리

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;