-
[hierarchy] query tuningPostgresql / PPAS/Query tuning 2022. 9. 15. 16:36
결과
AS-IS
실행 완료. 총 쿼리 실행시간: 6 secs 294 msec. 15 로우가 영향받았음.TO-BE실행 완료. 총 쿼리 실행시간: 211 msec. 15 로우가 영향받았음.- AS-IS PLAN
- 노드 별 병목 구간
- TO-BE PLAN
- 노드 별 병목 구간
- 원본 쿼리
WITH TMP_QD_POLICY AS ( SELECT '010101' AS TOT_GENRE UNION ALL SELECT '010103' AS TOT_GENRE UNION ALL SELECT '010107' AS TOT_GENRE UNION ALL SELECT '010117' AS TOT_GENRE UNION ALL SELECT '010130' AS TOT_GENRE UNION ALL SELECT '010131' AS TOT_GENRE UNION ALL SELECT '010132' AS TOT_GENRE UNION ALL SELECT '010201' AS TOT_GENRE UNION ALL SELECT '010202' AS TOT_GENRE UNION ALL SELECT '010203' AS TOT_GENRE UNION ALL SELECT '010204' AS TOT_GENRE UNION ALL SELECT '010207' AS TOT_GENRE UNION ALL SELECT '010217' AS TOT_GENRE UNION ALL SELECT '010301' AS TOT_GENRE UNION ALL SELECT '010302' AS TOT_GENRE UNION ALL SELECT '010303' AS TOT_GENRE UNION ALL SELECT '010307' AS TOT_GENRE UNION ALL SELECT '010317' AS TOT_GENRE UNION ALL SELECT '010402' AS TOT_GENRE UNION ALL SELECT '070201' AS TOT_GENRE ) ,TMP_QD_EXCEPT AS ( SELECT '010101' AS EX_GENRE_SMALL , '85118001' AS EX_SERIES UNION ALL SELECT '010107' AS EX_GENRE_SMALL , '85104101' AS EX_SERIES UNION ALL SELECT '010117' AS EX_GENRE_SMALL , '0126146601' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '11518201' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '13111201' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '6301301' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '6720101' AS EX_SERIES UNION ALL SELECT '010202' AS EX_GENRE_SMALL , '6265401' AS EX_SERIES UNION ALL SELECT '010203' AS EX_GENRE_SMALL , '42305301' AS EX_SERIES UNION ALL SELECT '010203' AS EX_GENRE_SMALL , '7149801' AS EX_SERIES UNION ALL SELECT '010205' AS EX_GENRE_SMALL , '6438701' AS EX_SERIES UNION ALL SELECT '010205' AS EX_GENRE_SMALL , '83019801' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0093008801' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0094448401' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0095144901' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0126146801' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0127023201' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '42389401' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '42485201' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '72010501' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '72011201' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '82004801' AS EX_SERIES UNION ALL SELECT '010301' AS EX_GENRE_SMALL , '42601201' AS EX_SERIES UNION ALL SELECT '010301' AS EX_GENRE_SMALL , '85135701' AS EX_SERIES UNION ALL SELECT '010303' AS EX_GENRE_SMALL , '12817901' AS EX_SERIES UNION ALL SELECT '010317' AS EX_GENRE_SMALL , '11573801' AS EX_SERIES ) SELECT /*genre.base.genreQdExcptInsertData*/ TOTAL_CNT, RNUM, SERIES_ID_IMCS, SERIES_NAME, GENRE1, GENRE2, GENRE3, GENRE_NAME, GENRE_NAME2, GENRE_NAME3 FROM ( SELECT COUNT(A.SERIES_ID_IMCS) OVER() AS TOTAL_CNT, A.RNUM, A.SERIES_ID_IMCS, A.SERIES_NAME, A.GENRE1, A.GENRE2, A.GENRE3, A.GENRE_NAME, A.GENRE_NAME2, A.GENRE_NAME3 FROM ( SELECT ROW_NUMBER() OVER (ORDER BY GEN.SERIES_ID_IMCS DESC) RNUM, GEN.SERIES_ID_IMCS, MAX(GEN.SERIES_NAME) SERIES_NAME, GEN.GENRE1, GEN.GENRE2, GEN.GENRE3, GEN.GENRE_NAME, GEN.GENRE_NAME2, GEN.GENRE_NAME3 FROM TMP_QD_POLICY TMP_QD, TMP_QD_EXCEPT TMP_EX, ( SELECT GEN.SERIES_ID_IMCS, GEN.SERIES_NAME, CASE WHEN LENGTH(GENRE_CD) >= 2 THEN SUBSTR(GENRE_CD, 1,2) END AS GENRE1, CASE WHEN LENGTH(GENRE_CD) >= 4 THEN SUBSTR(GENRE_CD, 1,4) END AS GENRE2, CASE WHEN LENGTH(GENRE_CD) = 6 THEN GENRE_CD END AS GENRE3, GEN.GENRE_NAME, GEN.GENRE_NAME2, GEN.GENRE_NAME3 FROM ( SELECT SMAP.ALBUM_GROUP_ID AS SERIES_ID_IMCS, SER_MST.ALBUM_GROUP_NM AS SERIES_NAME, ASB.GENRE_LARGE GENRE_NAME, ASB.GENRE_MID GENRE_NAME2, ASB.GENRE_SMALL GENRE_NAME3, ( SELECT GENRE_CD FROM ( WITH RECURSIVE CODE_LIST(GENRE_CD, GENRE_PARENT_CD, LEVEL, PATH, CYCLE) AS ( SELECT GENRE_CD , GENRE_PARENT_CD , 1 , ARRAY[GENRE_NAME::TEXT] , false FROM imcsuser.PT_CD_GENRE_CD WHERE GENRE_TYPE = 'L' UNION ALL SELECT A.GENRE_CD , A.GENRE_PARENT_CD , B.LEVEL + 1 , B.PATH || A.GENRE_NAME::TEXT , A.GENRE_CD = ANY (B.PATH) FROM imcsuser.PT_CD_GENRE_CD A , CODE_LIST B WHERE A.GENRE_PARENT_CD = B.GENRE_CD AND NOT CYCLE ) SELECT GENRE_CD , ARRAY_TO_STRING(PATH, ';') AS GENRE , LEVEL FROM CODE_LIST ) A WHERE GENRE = COALESCE(GENRE_LARGE, '') || CASE WHEN NULLIF(GENRE_MID,'') IS NULL THEN '' ELSE ';' || GENRE_MID END || CASE WHEN NULLIF(GENRE_SMALL,'') IS NULL THEN '' ELSE ';' || GENRE_SMALL END ) GENRE_CD FROM imcsuser.PT_LA_ALBUM_INFO AI , imcsuser.PT_LA_ALBUM_SUB ASB , imcsuser.PT_LA_ALBUM_GROUP SER_MST , imcsuser.PT_LA_ALBUM_RELATION SMAP WHERE 1 = 1 AND SER_MST.ALBUM_GROUP_NM LIKE ('%'||'시리즈'||'%') AND SMAP.ALBUM_ID = AI.ALBUM_ID AND AI.ALBUM_ID = ASB.ALBUM_ID AND SMAP.ALBUM_GROUP_ID = SER_MST.ALBUM_GROUP_ID AND SMAP.GROUP_TYPE = SER_MST.GROUP_TYPE AND SER_MST.GROUP_TYPE = 'S' )GEN )GEN WHERE 1 = 1 AND GEN.GENRE3 = TMP_QD.TOT_GENRE AND GEN.GENRE3||GEN.SERIES_ID_IMCS NOT IN (TMP_EX.EX_GENRE_SMALL||TMP_EX.EX_SERIES) GROUP BY GEN.SERIES_ID_IMCS, GEN.GENRE1, GEN.GENRE2, GEN.GENRE3, GEN.GENRE_NAME, GEN.GENRE_NAME2, GEN.GENRE_NAME3 ORDER BY GEN.SERIES_ID_IMCS DESC ) A )B WHERE B.RNUM BETWEEN 0+1 AND 0 + 50- 튜닝 쿼리
--explain (analyze on, buffers on, costs on, verbose on, settings on, timing on ) with recursive CODE_LIST(GENRE_CD, GENRE_PARENT_CD, LEVEL, PATH, CYCLE) AS ( SELECT GENRE_CD , GENRE_PARENT_CD , 1 , ARRAY[GENRE_NAME::TEXT] , false FROM imcsuser.PT_CD_GENRE_CD WHERE GENRE_TYPE = 'L' UNION ALL SELECT A.GENRE_CD , A.GENRE_PARENT_CD , B.LEVEL + 1 , B.PATH || A.GENRE_NAME::TEXT , A.GENRE_CD = ANY (B.PATH) FROM imcsuser.PT_CD_GENRE_CD A , CODE_LIST B WHERE A.GENRE_PARENT_CD = B.GENRE_CD AND NOT CYCLE ) , TMP_QD_POLICY AS ( SELECT '010101' AS TOT_GENRE UNION ALL SELECT '010103' AS TOT_GENRE UNION ALL SELECT '010107' AS TOT_GENRE UNION ALL SELECT '010117' AS TOT_GENRE UNION ALL SELECT '010130' AS TOT_GENRE UNION ALL SELECT '010131' AS TOT_GENRE UNION ALL SELECT '010132' AS TOT_GENRE UNION ALL SELECT '010201' AS TOT_GENRE UNION ALL SELECT '010202' AS TOT_GENRE UNION ALL SELECT '010203' AS TOT_GENRE UNION ALL SELECT '010204' AS TOT_GENRE UNION ALL SELECT '010207' AS TOT_GENRE UNION ALL SELECT '010217' AS TOT_GENRE UNION ALL SELECT '010301' AS TOT_GENRE UNION ALL SELECT '010302' AS TOT_GENRE UNION ALL SELECT '010303' AS TOT_GENRE UNION ALL SELECT '010307' AS TOT_GENRE UNION ALL SELECT '010317' AS TOT_GENRE UNION ALL SELECT '010402' AS TOT_GENRE UNION ALL SELECT '070201' AS TOT_GENRE ) ,TMP_QD_EXCEPT AS ( SELECT '010101' AS EX_GENRE_SMALL , '85118001' AS EX_SERIES UNION ALL SELECT '010107' AS EX_GENRE_SMALL , '85104101' AS EX_SERIES UNION ALL SELECT '010117' AS EX_GENRE_SMALL , '0126146601' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '11518201' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '13111201' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '6301301' AS EX_SERIES UNION ALL SELECT '010201' AS EX_GENRE_SMALL , '6720101' AS EX_SERIES UNION ALL SELECT '010202' AS EX_GENRE_SMALL , '6265401' AS EX_SERIES UNION ALL SELECT '010203' AS EX_GENRE_SMALL , '42305301' AS EX_SERIES UNION ALL SELECT '010203' AS EX_GENRE_SMALL , '7149801' AS EX_SERIES UNION ALL SELECT '010205' AS EX_GENRE_SMALL , '6438701' AS EX_SERIES UNION ALL SELECT '010205' AS EX_GENRE_SMALL , '83019801' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0093008801' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0094448401' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0095144901' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0126146801' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '0127023201' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '42389401' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '42485201' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '72010501' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '72011201' AS EX_SERIES UNION ALL SELECT '010217' AS EX_GENRE_SMALL , '82004801' AS EX_SERIES UNION ALL SELECT '010301' AS EX_GENRE_SMALL , '42601201' AS EX_SERIES UNION ALL SELECT '010301' AS EX_GENRE_SMALL , '85135701' AS EX_SERIES UNION ALL SELECT '010303' AS EX_GENRE_SMALL , '12817901' AS EX_SERIES UNION ALL SELECT '010317' AS EX_GENRE_SMALL , '11573801' AS EX_SERIES ) SELECT /*genre.base.genreQdExcptInsertData*/ TOTAL_CNT, RNUM, SERIES_ID_IMCS, SERIES_NAME, GENRE1, GENRE2, GENRE3, GENRE_NAME, GENRE_NAME2, GENRE_NAME3 FROM ( SELECT COUNT(A.SERIES_ID_IMCS) OVER() AS TOTAL_CNT, A.RNUM, A.SERIES_ID_IMCS, A.SERIES_NAME, A.GENRE1, A.GENRE2, A.GENRE3, A.GENRE_NAME, A.GENRE_NAME2, A.GENRE_NAME3 FROM ( SELECT ROW_NUMBER() OVER (ORDER BY GEN.SERIES_ID_IMCS DESC) RNUM, GEN.SERIES_ID_IMCS, MAX(GEN.SERIES_NAME) SERIES_NAME, GEN.GENRE1, GEN.GENRE2, GEN.GENRE3, GEN.GENRE_NAME, GEN.GENRE_NAME2, GEN.GENRE_NAME3 FROM TMP_QD_POLICY TMP_QD, TMP_QD_EXCEPT TMP_EX, ( SELECT GEN.SERIES_ID_IMCS, GEN.SERIES_NAME, CASE WHEN LENGTH(GENRE_CD) >= 2 THEN SUBSTR(GENRE_CD, 1,2) END AS GENRE1, CASE WHEN LENGTH(GENRE_CD) >= 4 THEN SUBSTR(GENRE_CD, 1,4) END AS GENRE2, CASE WHEN LENGTH(GENRE_CD) = 6 THEN GENRE_CD END AS GENRE3, GEN.GENRE_NAME, GEN.GENRE_NAME2, GEN.GENRE_NAME3 FROM ( SELECT SMAP.ALBUM_GROUP_ID AS SERIES_ID_IMCS, SER_MST.ALBUM_GROUP_NM AS SERIES_NAME, ASB.GENRE_LARGE GENRE_NAME, ASB.GENRE_MID GENRE_NAME2, ASB.GENRE_SMALL GENRE_NAME3, /*( SELECT GENRE_CD FROM ( WITH RECURSIVE CODE_LIST(GENRE_CD, GENRE_PARENT_CD, LEVEL, PATH, CYCLE) AS ( SELECT GENRE_CD , GENRE_PARENT_CD , 1 , ARRAY[GENRE_NAME::TEXT] , false FROM imcsuser.PT_CD_GENRE_CD WHERE GENRE_TYPE = 'L' UNION ALL SELECT A.GENRE_CD , A.GENRE_PARENT_CD , B.LEVEL + 1 , B.PATH || A.GENRE_NAME::TEXT , A.GENRE_CD = ANY (B.PATH) FROM imcsuser.PT_CD_GENRE_CD A , CODE_LIST B WHERE A.GENRE_PARENT_CD = B.GENRE_CD AND NOT CYCLE ) SELECT GENRE_CD , ARRAY_TO_STRING(PATH, ';') AS GENRE , LEVEL FROM CODE_LIST ) A WHERE GENRE = COALESCE(GENRE_LARGE, '') || CASE WHEN NULLIF(GENRE_MID,'') IS NULL THEN '' ELSE ';' || GENRE_MID END || CASE WHEN NULLIF(GENRE_SMALL,'') IS NULL THEN '' ELSE ';' || GENRE_SMALL END ) GENRE_CD*/ CODE.GENRE_CD FROM imcsuser.PT_LA_ALBUM_INFO AI , imcsuser.PT_LA_ALBUM_SUB ASB , imcsuser.PT_LA_ALBUM_GROUP SER_MST , imcsuser.PT_LA_ALBUM_RELATION SMAP ,CODE_LIST CODE WHERE 1 = 1 /*추가 START */ AND ARRAY_TO_STRING(PATH, ';') =COALESCE(ASB.GENRE_LARGE, '') || CASE WHEN NULLIF(ASB.GENRE_MID,'') IS NULL THEN '' ELSE ';' || ASB.GENRE_MID END || CASE WHEN NULLIF(ASB.GENRE_SMALL,'') IS NULL THEN '' ELSE ';' || ASB.GENRE_SMALL END /*추가 END */ AND SER_MST.ALBUM_GROUP_NM LIKE ('%'||'시리즈'||'%') AND SMAP.ALBUM_ID = AI.ALBUM_ID AND AI.ALBUM_ID = ASB.ALBUM_ID AND SMAP.ALBUM_GROUP_ID = SER_MST.ALBUM_GROUP_ID AND SMAP.GROUP_TYPE = SER_MST.GROUP_TYPE AND SER_MST.GROUP_TYPE = 'S' )GEN )GEN WHERE 1 = 1 AND GEN.GENRE3 = TMP_QD.TOT_GENRE AND GEN.GENRE3||GEN.SERIES_ID_IMCS NOT IN (TMP_EX.EX_GENRE_SMALL||TMP_EX.EX_SERIES) GROUP BY GEN.SERIES_ID_IMCS, GEN.GENRE1, GEN.GENRE2, GEN.GENRE3, GEN.GENRE_NAME, GEN.GENRE_NAME2, GEN.GENRE_NAME3 ORDER BY GEN.SERIES_ID_IMCS DESC ) A )B WHERE B.RNUM BETWEEN 0+1 AND 0 + 50'Postgresql / PPAS > Query tuning' 카테고리의 다른 글
[완료]/*stillCut.base.stillCutListPageData*/ (0) 2022.08.29 [완료]/* jobQuesues.distr.getJobQueuesRcmInfoListForPage */ (0) 2022.08.29 [완료]/* contentMonitoring.distr.selectAlbumInfoDistrList */ (0) 2022.08.29 [완료] /*modifySchedule.base.selectContentList */ (0) 2022.08.29 [완료] /* index.base.getNewRegIndexAlbumList */ (0) 2022.08.29