-
[완료]/* categoryForm.base.getAlbumNameSearchList */Postgresql / PPAS/Query tuning 2022. 8. 29. 13:37
AS-IS
Total query runtime: 6 secs 719 msec
TO-BE
Total query runtime: 4 secs 813 msec
개선효과 : 28%- Costs 높은 문제의 NODE
-> Index Scan using pk_pt_la_album_info on imcsuser.pt_la_album_info alb_info_1 (cost=0.42..48673.03 rows=100 width=57) (actual time=2.095..500.890 rows=5143 loops=1)"
" Output: alb_info_1.album_id, alb_info_1.album_name, alb_info_1.album_date, alb_info_1.series_id, alb_info_1.series_name, alb_info_1.series_no, alb_info_1.onair_date, alb_info_1.onair_date_display, alb_info_1.release_date, alb_info_1.watch_pctv_yn, alb_info_1.watch_2ndtv_yn, alb_info_1.encoding_format, alb_info_1.high_quality_type, alb_info_1.pre_mapping_yn, alb_info_1.update_date, alb_info_1.event_yn, alb_info_1.vod_type, alb_info_1.content_type"
" Filter: (upper((alb_info_1.album_name)::text) ~~ '%테스트%'::text)"
" Rows Removed by Filter: 993571"
" Buffers: shared hit=64061"* GIN bigm index 생성 후 NODE
-> BitmapAnd (cost=8.45..8.45 rows=1 width=0) (actual time=0.987..0.988 rows=0 loops=1)"
" Buffers: shared hit=39"
" -> Bitmap Index Scan on idx_pt_la_album_group_01 (cost=0.00..3.16 rows=249 width=0) (actual time=0.869..0.869 rows=34348 loops=1)"
" Index Cond: (ser_mst.group_type = 'S'::bpchar)"
" Buffers: shared hit=32"
" -> Bitmap Index Scan on idx_pt_la_album_group_02 (cost=0.00..5.04 rows=5 width=0) (actual time=0.068..0.068 rows=422 loops=1)"
" Index Cond: (upper((ser_mst.album_group_nm)::text) ~~ '%테스트%'::text)"
" Buffers: shared hit=7"자연어 검색 GIN BIGM INDEX 생성 하여 COSTS 가 높은 NODE 의 비용을 줄였음.
CREATE INDEX idx_pt_la_album_group_02 ON imcsuser.PT_LA_ALBUM_GROUP USING gin (upper((album_group_nm)::text) public.gin_bigm_ops);
CREATE INDEX idx_pt_la_album_info_03 ON imcsuser.pt_la_album_info USING gin (upper((album_name)::text) public.gin_bigm_ops);원본 쿼리 ( 1년치 검색 )
SELECT /* categoryForm.base.getAlbumNameSearchList */
A.ALB_SER_ID,
A.ALB_SER_NAME,
A.ALBUM_NO,
A.ALBUM_ID,
A.PRE_MAPPING_YN,
A.CREATE_DATE,
A.GENRE,
A.VIEWING_FLAG,
A.QD_FLAG,
CASE
WHEN POSITION('I20' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS I20_YN,
CASE
WHEN POSITION('I30' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS I30_YN,
CASE
WHEN POSITION('NSC' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS NSC_YN,
CASE
WHEN ( ( POSITION('I20' IN A.CATEGORY_GB) > 0
OR POSITION('NSC' IN A.CATEGORY_GB) > 0 )
AND POSITION('UFX' IN A.NSC_GB) > 0 ) THEN 'Y'
ELSE 'N'
END AS UFLIX_YN,
A.REG_CNT,
A.ALB_SER_TYPE,
A.TERR_YN,
A.TERR_PERIOD,
A.TERR_ST_DATE,
A.TERR_ED_DATE,
A.LICENSING_WINDOW_START,
A.LICENSING_WINDOW_END,
A.SUPER_ID
FROM
(
SELECT
SER_MAP.ALBUM_GROUP_ID AS ALB_SER_ID,
SER_MST.ALBUM_GROUP_NM AS ALB_SER_NAME,
'' AS ALBUM_NO,
AST_INFO.ALBUM_ID AS ALBUM_ID,
'' AS PRE_MAPPING_YN,
MAX(TO_CHAR(TO_DATE(AST_INFO.CREATE_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS CREATE_DATE,
MAX( COALESCE(NULLIF(ALB_SUB.GENRE_LARGE,''),'')
|| '/'
|| COALESCE(NULLIF(ALB_SUB.GENRE_MID,''),'')
|| '/'
|| COALESCE(NULLIF(ALB_SUB.GENRE_SMALL,''),'') ) AS GENRE,
MAX(NVL(CAT_MAP.VOD_VIEWING_FLAG, 'N')) AS VIEWING_FLAG,
MAX(CAT_MAP.VOD_QD_FLAG) AS QD_FLAG,
REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_CATEGORY_GB ORDER BY CAT.VOD_CATEGORY_GB), ','), 2), '([^,]+)(,\1)+', '\1') AS CATEGORY_GB,
REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_NSC_GB ORDER BY CAT.VOD_NSC_GB), ','), 2), '([^,]+)(,\1)+', '\1') AS NSC_GB,
(
SELECT
COUNT(ALBUM_ID)
FROM
imcsuser.PT_LA_ALBUM_RELATION A
WHERE A.ALBUM_GROUP_ID = SER_MAP.ALBUM_GROUP_ID
AND A.GROUP_TYPE = 'S'
) AS REG_CNT,
'S' AS ALB_SER_TYPE,
'' AS TERR_YN,
'' AS TERR_PERIOD,
'' AS TERR_ST_DATE,
'' AS TERR_ED_DATE,
'' AS LICENSING_WINDOW_START,
'' AS LICENSING_WINDOW_END,
MAX(ALB_SU.ALBUM_GROUP_ID) SUPER_ID
FROM
imcsuser.PT_LA_ALBUM_INFO ALB_INFO JOIN imcsuser.PT_LA_ALBUM_RELATION SER_MAP ON (ALB_INFO.ALBUM_ID = SER_MAP.ALBUM_ID AND SER_MAP.GROUP_TYPE = 'S')
JOIN imcsuser.PT_LA_ALBUM_GROUP SER_MST ON (SER_MST.ALBUM_GROUP_ID = SER_MAP.ALBUM_GROUP_ID
AND SER_MST.GROUP_TYPE = SER_MAP.GROUP_TYPE
AND SER_MST.GROUP_TYPE = 'S')
JOIN imcsuser.PT_LA_ASSET_INFO AST_INFO ON (ALB_INFO.ALBUM_ID = AST_INFO.ALBUM_ID
AND AST_INFO.SCREEN_TYPE = 'N'
)
JOIN imcsuser.PT_LA_ALBUM_SUB ALB_SUB ON (ALB_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID)
JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALB_PLF ON (ALB_INFO.ALBUM_ID = ALB_PLF.ALBUM_ID
AND ALB_PLF.screen_type = 'N'
)
LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION ALB_SU ON (SER_MAP.ALBUM_GROUP_ID = ALB_SU.ALBUM_ID
AND ALB_SU.GROUP_TYPE = 'C')
LEFT OUTER JOIN VODUSER.PT_VO_CATEGORY_MAP_UNITED CAT_MAP ON (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID)
LEFT OUTER JOIN VODUSER.PT_VO_CATEGORY_UNITED CAT ON (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID
AND CAT_MAP.VOD_CATEGORY_ID = CAT.VOD_CATEGORY_ID
AND CAT.VOD_CATEGORY_GB IN ('I20', 'I30', 'NSC'))
WHERE 1=1
AND AST_INFO.CREATE_DATE BETWEEN '20200101' AND '20201231'
GROUP BY SER_MAP.ALBUM_GROUP_ID, SER_MST.ALBUM_GROUP_NM, AST_INFO.ALBUM_ID
) A
WHERE 1=1
AND UPPER(A.ALB_SER_NAME) LIKE '%'||UPPER('테스트')||'%'
UNION ALL
SELECT A.ALB_SER_ID,
A.ALB_SER_NAME,
A.ALBUM_NO,
A.ALBUM_ID,
A.PRE_MAPPING_YN,
A.CREATE_DATE,
A.GENRE,
A.VIEWING_FLAG,
A.QD_FLAG,
CASE
WHEN POSITION('I20' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS I20_YN,
CASE
WHEN POSITION('I30' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS I30_YN,
CASE
WHEN POSITION('NSC' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS NSC_YN,
CASE
WHEN ( ( POSITION('I20' IN A.CATEGORY_GB) > 0
OR POSITION('NSC' IN A.CATEGORY_GB) > 0 )
AND POSITION('UFX' IN A.NSC_GB) > 0 ) THEN 'Y'
ELSE 'N'
END AS UFLIX_YN,
A.REG_CNT,
A.ALB_SER_TYPE,
A.TERR_YN,
A.TERR_PERIOD,
A.TERR_ST_DATE,
A.TERR_ED_DATE,
A.LICENSING_WINDOW_START,
A.LICENSING_WINDOW_END,
A.SUPER_ID
FROM
(SELECT
ALB_INFO.ALBUM_ID AS ALB_SER_ID,
ALB_INFO.ALBUM_NAME AS ALB_SER_NAME,
ALB_INFO.SERIES_NO AS ALBUM_NO,
ALB_INFO.ALBUM_ID AS ALBUM_ID,
COALESCE(NULLIF(ALB_INFO.PRE_MAPPING_YN,''), 'N') AS PRE_MAPPING_YN,
MAX(TO_CHAR(TO_DATE(AST_INFO.CREATE_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS CREATE_DATE,
MAX( COALESCE(NULLIF(ALB_SUB.GENRE_LARGE,''),'')
|| '/'
|| COALESCE(NULLIF(ALB_SUB.GENRE_MID,''),'')
|| '/'
|| COALESCE(NULLIF(ALB_SUB.GENRE_SMALL,''),'') ) AS GENRE,
MAX(COALESCE(NULLIF(CAT_MAP.VOD_VIEWING_FLAG,''), 'N')) AS VIEWING_FLAG,
MAX(CAT_MAP.VOD_QD_FLAG ) AS QD_FLAG,
REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_CATEGORY_GB ORDER BY CAT.VOD_CATEGORY_GB), ','), 2), '([^,]+)(,\1)+', '\1') AS CATEGORY_GB,
REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_NSC_GB ORDER BY CAT.VOD_NSC_GB), ','), 2), '([^,]+)(,\1)+', '\1') AS NSC_GB,
1 AS REG_CNT,
'CON' AS ALB_SER_TYPE,
ALB_PLF.TERR_YN,
ALB_PLF.TERR_PERIOD,
ALB_PLF.TERR_ST_DATE,
ALB_PLF.TERR_ED_DATE,
ALB_PLF.LICENSING_WINDOW_START AS LICENSING_WINDOW_START,
ALB_PLF.LICENSING_WINDOW_END AS LICENSING_WINDOW_END,
MAX(ALB_SU.ALBUM_GROUP_ID) SUPER_ID
FROM imcsuser.PT_LA_ALBUM_INFO ALB_INFO LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION SER_MAP ON (ALB_INFO.ALBUM_ID = SER_MAP.ALBUM_ID
AND SER_MAP.GROUP_TYPE = 'S')
JOIN imcsuser.PT_LA_ASSET_INFO AST_INFO ON (ALB_INFO.ALBUM_ID = AST_INFO.ALBUM_ID
AND AST_INFO.screen_type = 'N'
)
JOIN imcsuser.PT_LA_ALBUM_SUB ALB_SUB ON (ALB_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID)
JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALB_PLF ON (ALB_INFO.ALBUM_ID = ALB_PLF.ALBUM_ID
AND ALB_PLF.screen_type = 'N'
)
LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION ALB_SU ON (ALB_INFO.ALBUM_ID = ALB_SU.ALBUM_ID
AND ALB_SU.GROUP_TYPE IN ('A', 'B'))
LEFT OUTER JOIN VODUSER.PT_VO_CATEGORY_MAP_UNITED CAT_MAP ON (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID)
LEFT OUTER JOIN VODUSER.PT_VO_CATEGORY_UNITED CAT ON (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID
AND CAT_MAP.VOD_CATEGORY_ID = CAT.VOD_CATEGORY_ID
AND CAT.VOD_CATEGORY_GB IN ('I20', 'I30', 'NSC'))
WHERE 1=1
AND AST_INFO.CREATE_DATE BETWEEN '20200101' AND '20201231'
GROUP BY
ALB_INFO.ALBUM_ID, ALB_INFO.ALBUM_NAME, ALB_INFO.SERIES_NO, ALB_INFO.ALBUM_ID, NVL(ALB_INFO.PRE_MAPPING_YN, 'N'), ALB_PLF.TERR_YN, ALB_PLF.TERR_PERIOD, ALB_PLF.TERR_ST_DATE,
ALB_PLF.TERR_ED_DATE, ALB_PLF.LICENSING_WINDOW_START, ALB_PLF.LICENSING_WINDOW_END
) A
WHERE 1=1
AND UPPER(A.ALB_SER_NAME) LIKE '%'||UPPER('테스트')||'%'
UNION ALL
SELECT
A.ALB_SER_ID,
A.ALB_SER_NAME,
A.ALBUM_NO,
A.ALBUM_ID,
A.PRE_MAPPING_YN,
A.CREATE_DATE,
A.GENRE,
A.VIEWING_FLAG,
A.QD_FLAG,
CASE
WHEN POSITION('I20' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS I20_YN,
CASE
WHEN POSITION('I30' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS I30_YN,
CASE
WHEN POSITION('NSC' IN A.CATEGORY_GB) > 0 THEN 'Y'
ELSE 'N'
END AS NSC_YN,
CASE
WHEN ( ( POSITION('I20' IN A.CATEGORY_GB) > 0
OR POSITION('NSC' IN A.CATEGORY_GB) > 0 )
AND POSITION('UFX' IN A.NSC_GB) > 0 ) THEN 'Y'
ELSE 'N'
END AS UFLIX_YN,
A.REG_CNT,
A.ALB_SER_TYPE,
A.TERR_YN,
A.TERR_PERIOD,
A.TERR_ST_DATE,
A.TERR_ED_DATE,
A.LICENSING_WINDOW_START,
A.LICENSING_WINDOW_END,
'' SUPER_ID
FROM
(
SELECT
PD.PRODUCT_ID AS ALB_SER_ID,
PP.IMCS_PRODUCT_NAME AS ALB_SER_NAME,
'' AS ALBUM_NO,
AST_INFO.ALBUM_ID AS ALBUM_ID,
'' AS PRE_MAPPING_YN,
MAX(TO_CHAR(TO_DATE(AST_INFO.CREATE_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS CREATE_DATE,
MAX( COALESCE(NULLIF(ALB_SUB.GENRE_LARGE,''),'')
|| '/'
|| COALESCE(NULLIF(ALB_SUB.GENRE_MID,''),'')
|| '/'
|| COALESCE(NULLIF(ALB_SUB.GENRE_SMALL,''),'') ) AS GENRE,
MAX(NVL(CAT_MAP.VOD_VIEWING_FLAG, 'N')) AS VIEWING_FLAG,
MAX( CAT_MAP.VOD_QD_FLAG ) AS QD_FLAG,
REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_CATEGORY_GB ORDER BY CAT.VOD_CATEGORY_GB), ','), 2), '([^,]+)(,\1)+', '\1') AS CATEGORY_GB,
REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_NSC_GB ORDER BY CAT.VOD_NSC_GB), ','), 2), '([^,]+)(,\1)+', '\1') AS NSC_GB,
1 AS REG_CNT,
'PPS' AS ALB_SER_TYPE,
'' AS TERR_YN,
'' AS TERR_PERIOD,
'' AS TERR_ST_DATE,
'' AS TERR_ED_DATE,
ALB_PLF.LICENSING_WINDOW_START AS LICENSING_WINDOW_START,
ALB_PLF.LICENSING_WINDOW_END AS LICENSING_WINDOW_END
FROM imcsuser.PT_LA_ALBUM_INFO ALB_INFO LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION SER_MAP ON (ALB_INFO.ALBUM_ID = SER_MAP.ALBUM_ID
AND SER_MAP.GROUP_TYPE = 'S')
JOIN imcsuser.PT_LA_ASSET_INFO AST_INFO ON (ALB_INFO.ALBUM_ID = AST_INFO.ALBUM_ID
AND AST_INFO.screen_type = 'N'
)
JOIN imcsuser.PT_LA_ALBUM_SUB ALB_SUB ON (ALB_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID)
JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALB_PLF ON (ALB_INFO.ALBUM_ID = ALB_PLF.ALBUM_ID
AND ALB_PLF.screen_type = 'N'
)
JOIN imcsuser.PT_PD_PACKAGE_DETAIL PD ON (AST_INFO.ASSET_ID = PD.CONTENTS_ID)
JOIN imcsuser.PT_PD_PACKAGE_UNITED PP ON (PD.PRODUCT_ID = PP.IMCS_PRODUCT_ID
AND PP.IMCS_PRODUCT_TYPE = '2'
AND COALESCE(NULLIF(PP.IMCS_NSC_PROD_KIND,''), '0') = '0')
LEFT OUTER JOIN VODUSER.PT_VO_CATEGORY_MAP_UNITED CAT_MAP ON (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID)
LEFT OUTER JOIN VODUSER.PT_VO_CATEGORY_UNITED CAT ON (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID
AND CAT_MAP.VOD_CATEGORY_ID = CAT.VOD_CATEGORY_ID
AND CAT.VOD_CATEGORY_GB IN ('I20', 'I30', 'NSC'))
WHERE 1=1
AND AST_INFO.CREATE_DATE BETWEEN '20200101' AND '20201231'
GROUP BY ALB_INFO.ALBUM_ID, PD.PRODUCT_ID, PP.IMCS_PRODUCT_NAME, ALB_INFO.SERIES_NO, ALB_INFO.ALBUM_ID, NVL(ALB_INFO.PRE_MAPPING_YN, 'N'), ALB_PLF.TERR_YN,
ALB_PLF.TERR_PERIOD, ALB_PLF.TERR_ST_DATE, ALB_PLF.TERR_ED_DATE, ALB_PLF.LICENSING_WINDOW_START, ALB_PLF.LICENSING_WINDOW_END, AST_INFO.ALBUM_ID
) A
WHERE 1=1
AND UPPER(A.ALB_SER_NAME) LIKE '%'||UPPER('테스트')||'%''Postgresql / PPAS > Query tuning' 카테고리의 다른 글
[완료]/*contentExpand.distr.selectExposureNewScheduleList*/ (0) 2022.08.29 [완료]/*album.base.deleteMultiSeriesCategoryMap*/ (0) 2022.08.29 [완료]/*distribute.distr.distributeListStatusPageData*/ (0) 2022.08.29 [ 완료]/*new4dSchedule.distr.new4dScheduleListPageData*/ (0) 2022.08.29 [완료]/*abtest.categoryForm.base.getAlbumNameInSeriesAlbum*/ (0) 2022.08.29