Postgresql / PPAS/Query tuning
[hierarchy] 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