ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • [완료]/* abtest.content.base.popupAbtestFormRouteListPageDataSeries */
      Postgresql / PPAS/Query tuning 2022. 8. 29. 11:52

       

      * 개선 포인트

      AS-IS 수행 시간 : 10초 내외
      TO-BE 수행 시간 : -
      분석 결과 : 2개의 recursive 쿼리 중 하단에 있는 recursive 를 제외하고 수행할 경우 0.2초 내외로 수행됨.
      하단 recursive 영역에 대한 변경이 필요
      하단 recursive 쿼리는 아래와 같다.
      • 원본 쿼리
      SELECT
      /* abtest.content.base.popupAbtestFormRouteListPageDataSeries /
      BB.RNUM,
      BB.TEST_ID,
      BB.VARIATION_ID,
      BB.CONTENTS_ID,
      BB.CONTENTS_NAME,
      BB.CATEGORY_NAME,
      BB.CATEGORY_ID,
      BB.PARENT_CATEGORY_ID,
      BB.CREATE_DATE,
      BB.CATEGORY_GB,
      BB.VIEWING_FLAG,
      BB.FORM_ROUTE,
      BB.PKG_YN,
      BB.SERIES_YN,
      BB.CLOSE_YN,
      BB.PPS_ONLY_FLAG,
      BB.NSC_GB,
      BB.IS_HD,
      BB.SERIES_ID,
      BB.TEST_SBC,
      BB.PR_INFO
      FROM(
      SELECT
      ROW_NUMBER() OVER (
      ORDER BY
      (
      CASE
      WHEN (
      (
      AA.CATEGORY_GB = 'NSC'
      OR AA.CATEGORY_GB = 'I20'
      )
      AND AA.NSC_GB = 'UFX'
      ) THEN 4
      ELSE CASE
      WHEN AA.CATEGORY_GB = 'NSC' THEN 3
      WHEN AA.CATEGORY_GB = 'I30' THEN 2
      WHEN AA.CATEGORY_GB = 'I20' THEN 1
      END
      END
      ) ASC,
      AA.CATEGORY_ID ASC
      ) RNUM,
      AA.TEST_ID,
      AA.VARIATION_ID,
      AA.CONTENTS_ID,
      AA.CONTENTS_NAME,
      AA.CATEGORY_NAME,
      AA.CATEGORY_ID,
      AA.PARENT_CATEGORY_ID,
      AA.CREATE_DATE,
      AA.CATEGORY_GB,
      AA.VIEWING_FLAG,
      AA.FORM_ROUTE,
      AA.PKG_YN,
      AA.SERIES_YN,
      AA.CLOSE_YN,
      AA.PPS_ONLY_FLAG,
      AA.NSC_GB,
      AA.IS_HD,
      AA.SERIES_ID,
      AA.TEST_SBC,
      AA.PR_INFO
      FROM(
      SELECT
      '' AS TEST_ID,
      '' AS VARIATION_ID,
      CONTENTS_ID,
      CONTENTS_NAME,
      CATEGORY_NAME,
      CATEGORY_ID,
      PARENT_CATEGORY_ID,
      CREATE_DATE,
      CATEGORY_GB,
      VIEWING_FLAG,
      FORM_ROUTE,
      PKG_YN,
      SERIES_YN,
      CLOSE_YN,
      PPS_ONLY_FLAG,
      NSC_GB,
      IS_HD,
      SERIES_ID,
      TEST_SBC,
      PR_INFO
      FROM(
      SELECT
      CONTENTS_ID,
      MAX(CONTENTS_NAME) AS CONTENTS_NAME,
      MAX(CATEGORY_NAME) AS CATEGORY_NAME,
      CATEGORY_ID,
      MAX(PARENT_CATEGORY_ID) AS PARENT_CATEGORY_ID,
      MAX(CREATE_DATE) AS CREATE_DATE,
      MAX(CATEGORY_GB) AS CATEGORY_GB,
      MAX(VIEWING_FLAG) AS VIEWING_FLAG,
      MAX(FORM_ROUTE) AS FORM_ROUTE,
      PKG_YN,
      SERIES_YN,
      CLOSE_YN,
      PPS_ONLY_FLAG,
      NSC_GB,
      IS_HD,
      SERIES_ID,
      TEST_SBC,
      PR_INFO
      FROM
      (
      SELECT
      CASE
      WHEN COALESCE(NULLIF(CT.VOD_SERIES_ID, ''), '') = '' THEN CM.VOD_CONTENTS_ID
      ELSE CT.VOD_SERIES_ID
      END CONTENTS_ID,
      CASE
      WHEN COALESCE(NULLIF(CT.VOD_SERIES_ID, ''), '') = '' THEN CM.VOD_CONTENTS_NAME
      ELSE CT.VOD_CATEGORY_NAME
      END CONTENTS_NAME,
      CT.VOD_CATEGORY_NAME AS CATEGORY_NAME,
      CT.VOD_CATEGORY_ID AS CATEGORY_ID,
      CT.VOD_PARENT_CATEGORY_ID AS PARENT_CATEGORY_ID,
      TO_CHAR(
      TO_DATE(CT.VOD_CREATE_DATE, 'YYYYMMDDHH24MISS'),
      'YYYYMMDD'
      ) AS CREATE_DATE,
      CT.VOD_CATEGORY_GB AS CATEGORY_GB,
      COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V') AS VIEWING_FLAG,
      CT.VOD_PKG_YN AS PKG_YN,
      CT.VOD_SERIES_YN AS SERIES_YN,
      CT.VOD_CLOSE_YN AS CLOSE_YN,
      CT.VOD_PPS_ONLY_FLAG AS PPS_ONLY_FLAG,
      CT.VOD_NSC_GB AS NSC_GB,
      CT.VOD_IS_HD IS_HD,
      CT.VOD_SERIES_ID AS SERIES_ID,
      COALESCE(NULLIF(CT.VOD_TEST_SBC, ''), 'Y') AS TEST_SBC,
      COALESCE(NULLIF(CT.VOD_PR_INFO, ''), '01') AS PR_INFO,
      (
      WITH RECURSIVE CTE_CONNECT_BY AS (
      SELECT
      1 AS LEVEL,
      S.*
      FROM
      voduser.PT_VO_CATEGORY_UNITED S
      WHERE
      VOD_CATEGORY_ID = CT.VOD_CATEGORY_ID
      UNION ALL
      SELECT
      LEVEL + 1 AS LEVEL,
      S.*
      FROM
      CTE_CONNECT_BY R
      INNER JOIN voduser.PT_VO_CATEGORY_UNITED S ON R.VOD_PARENT_CATEGORY_ID = S.VOD_CATEGORY_ID
      )
      SELECT
      REPLACE(
      REPLACE(
      TRANSLATE(
      ARRAY_TO_STRING(
      ARRAY_AGG(
      VOD_CATEGORY_NAME || '(' || VOD_CATEGORY_ID || ')'
      ORDER BY
      VOD_CATEGORY_LEVEL DESC
      ),
      ';'
      ),
      ';',
      '|'
      ),
      CHR(60),
      CHR(38) || 'lt;'
      ),
      CHR(62),
      CHR(38) || 'gt;'
      )
      FROM
      CTE_CONNECT_BY
      ) AS FORM_ROUTE
      FROM
      voduser.PT_VO_CATEGORY_UNITED CT,
      voduser.PT_VO_CATEGORY_MAP_UNITED CM
      WHERE
      CT.VOD_CATEGORY_ID = CM.VOD_CATEGORY_ID
      AND EXISTS (
      SELECT
      'X'
      FROM
      imcsuser.PT_LA_ALBUM_RELATION A
      WHERE
      A.GROUP_TYPE = 'S'
      AND A.ALBUM_GROUP_ID = '0154254501'
      AND A.ALBUM_ID = CM.VOD_CONTENTS_ID
      LIMIT
      1
      )
      ) R
      GROUP BY
      CONTENTS_ID,
      CATEGORY_ID,
      PKG_YN,
      SERIES_YN,
      CLOSE_YN,
      PPS_ONLY_FLAG,
      NSC_GB,
      IS_HD,
      SERIES_ID,
      TEST_SBC,
      PR_INFO
      ) T
      UNION
      SELECT
      TEST_ID,
      VARIATION_ID,
      CONTENTS_ID,
      CONTENTS_NAME,
      CATEGORY_NAME,
      CATEGORY_ID,
      PARENT_CATEGORY_ID,
      CREATE_DATE,
      CATEGORY_GB,
      VIEWING_FLAG,
      FORM_ROUTE,
      PKG_YN,
      SERIES_YN,
      CLOSE_YN,
      PPS_ONLY_FLAG,
      NSC_GB,
      IS_HD,
      SERIES_ID,
      TEST_SBC,
      PR_INFO
      FROM(
      SELECT
      TEST_ID,
      VARIATION_ID,
      CONTENTS_ID,
      MAX(CONTENTS_NAME) AS CONTENTS_NAME,
      MAX(CATEGORY_NAME) AS CATEGORY_NAME,
      CATEGORY_ID,
      MAX(PARENT_CATEGORY_ID) AS PARENT_CATEGORY_ID,
      MAX(CREATE_DATE) AS CREATE_DATE,
      MAX(CATEGORY_GB) AS CATEGORY_GB,
      MAX(VIEWING_FLAG) AS VIEWING_FLAG,
      MAX(FORM_ROUTE) AS FORM_ROUTE,
      PKG_YN,
      SERIES_YN,
      CLOSE_YN,
      PPS_ONLY_FLAG,
      NSC_GB,
      IS_HD,
      SERIES_ID,
      TEST_SBC,
      PR_INFO
      FROM
      (
      SELECT
      CT.TEST_ID,
      CT.VARIATION_ID,
      CASE
      WHEN COALESCE(NULLIF(CT.SERIES_ID, ''), '') = '' THEN CM.CONTENTS_ID
      ELSE CT.SERIES_ID
      END CONTENTS_ID,
      CASE
      WHEN COALESCE(NULLIF(CT.SERIES_ID, ''), '') = '' THEN CM.CONTENTS_NAME
      ELSE CT.CATEGORY_NAME
      END CONTENTS_NAME,
      CT.CATEGORY_NAME AS CATEGORY_NAME,
      CT.CATEGORY_ID AS CATEGORY_ID,
      CT.PARENT_CATEGORY_ID AS PARENT_CATEGORY_ID,
      TO_CHAR(
      TO_DATE(CT.CREATE_DATE, 'YYYYMMDDHH24MISS'),
      'YYYYMMDD'
      ) AS CREATE_DATE,
      CT.CATEGORY_GB AS CATEGORY_GB,
      COALESCE(NULLIF(CM.VIEWING_FLAG, ''), 'V') AS VIEWING_FLAG,
      CT.PKG_YN AS PKG_YN,
      CT.SERIES_YN AS SERIES_YN,
      CT.CLOSE_YN AS CLOSE_YN,
      CT.PPS_ONLY_FLAG AS PPS_ONLY_FLAG,
      CT.NSC_GB AS NSC_GB,
      CT.IS_HD,
      CT.SERIES_ID AS SERIES_ID,
      COALESCE(NULLIF(CT.TEST_SBC, ''), 'Y') AS TEST_SBC,
      COALESCE(NULLIF(CT.PR_INFO, ''), '01') AS PR_INFO,
      (
      SELECT
      REPLACE(
      REPLACE(
      TRANSLATE(
      ARRAY_TO_STRING(
      ARRAY_AGG(
      CATEGORY_NAME || '(' || CATEGORY_ID || ')'
      ORDER BY
      CATEGORY_LEVEL DESC
      ),
      ';'
      ),
      ';',
      '|'
      ),
      CHR(60),
      CHR(38) || 'lt;'
      ),
      CHR(62),
      CHR(38) || 'gt;'
      )
      FROM
      (
      WITH RECURSIVE TMP_LIST AS (
      SELECT
      DISTINCT TEST_ID,
      VARIATION_ID,
      CATEGORY_ID,
      CATEGORY_NAME,
      CATEGORY_LEVEL,
      PARENT_CATEGORY_ID
      FROM(
      SELECT
      '' AS TEST_ID,
      '' AS VARIATION_ID,
      VOD_CATEGORY_ID CATEGORY_ID,
      VOD_CATEGORY_NAME CATEGORY_NAME,
      VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
      VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
      '' AS STAT_FLAG
      FROM
      voduser.PT_VO_CATEGORY_UNITED
      UNION
      SELECT
      TEST_ID,
      VARIATION_ID,
      CATEGORY_ID,
      CATEGORY_NAME,
      CATEGORY_LEVEL,
      PARENT_CATEGORY_ID,
      STAT_FLAG
      FROM
      VODUSER.PT_AB_CATEGORY
      EXCEPT
      SELECT
      '' AS TEST_ID,
      '' AS VARIATION_ID,
      A.VOD_CATEGORY_ID CATEGORY_ID,
      A.VOD_CATEGORY_NAME CATEGORY_NAME,
      A.VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
      A.VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
      '' AS STAT_FLAG
      FROM
      voduser.PT_VO_CATEGORY_UNITED A,
      VODUSER.PT_AB_CATEGORY B
      WHERE
      A.VOD_CATEGORY_ID = B.CATEGORY_ID
      AND B.TEST_ID = CT.TEST_ID
      AND B.VARIATION_ID = CT.VARIATION_ID
      ) T
      WHERE
      COALESCE(NULLIF(STAT_FLAG, ''), 'U') <> 'D'
      ),
      CATEGORY_LIST AS(
      SELECT
      1 AS LEVEL,
      T.*
      FROM
      TMP_LIST T
      WHERE
      CATEGORY_ID = CT.CATEGORY_ID
      AND TEST_ID = CT.TEST_ID
      AND VARIATION_ID = CT.VARIATION_ID
      UNION ALL
      SELECT
      LEVEL + 1 AS LEVEL,
      T.*
      FROM
      CATEGORY_LIST R
      INNER JOIN TMP_LIST T ON R.PARENT_CATEGORY_ID = T.CATEGORY_ID
      AND CT.TEST_ID = COALESCE(NULLIF(T.TEST_ID, ''), CT.TEST_ID)
      AND CT.VARIATION_ID = COALESCE(NULLIF(T.VARIATION_ID, ''), CT.VARIATION_ID)
      )
      SELECT
      CATEGORY_ID,
      CATEGORY_NAME,
      CATEGORY_LEVEL
      FROM
      CATEGORY_LIST
      ) T
      ) AS FORM_ROUTE
      FROM
      VODUSER.PT_AB_CATEGORY CT,
      VODUSER.PT_AB_CATEGORY_MAP CM
      WHERE
      CT.CATEGORY_ID = CM.CATEGORY_ID
      AND CT.TEST_ID = CM.TEST_ID
      AND CT.VARIATION_ID = CM.VARIATION_ID
      AND EXISTS (
      SELECT
      'X'
      FROM
      imcsuser.PT_LA_ALBUM_RELATION A
      WHERE
      A.GROUP_TYPE = 'S'
      AND A.ALBUM_GROUP_ID = '0154254501'
      AND A.ALBUM_ID = CM.CONTENTS_ID
      LIMIT
      1
      )
      AND CT.STAT_FLAG <> 'D'
      AND CM.STAT_FLAG <> 'D'
      ) R
      GROUP BY
      TEST_ID,
      VARIATION_ID,
      CONTENTS_ID,
      CATEGORY_ID,
      PKG_YN,
      SERIES_YN,
      CLOSE_YN,
      PPS_ONLY_FLAG,
      NSC_GB,
      IS_HD,
      SERIES_ID,
      TEST_SBC,
      PR_INFO
      ) T
      ) AA
      ) BB
      ORDER BY
      TO_NUMBER(BB.RNUM);

       

      * 튜닝 쿼리

      (
      select
      replace(
      replace(
      translate(ARRAY_TO_STRING(ARRAY_AGG(CATEGORY_NAME || '(' || CATEGORY_ID || ')'order by CATEGORY_LEVEL desc),';'),';','|'),
      CHR(60),CHR(38) || 'lt;'),
      CHR(62),CHR(38) || 'gt;')
      from
      (
      with recursive TMP_LIST as (
      select
      distinct TEST_ID,
      VARIATION_ID,
      CATEGORY_ID,
      CATEGORY_NAME,
      CATEGORY_LEVEL,
      PARENT_CATEGORY_ID
      from
      (
      select
      '' as TEST_ID,
      '' as VARIATION_ID,
      VOD_CATEGORY_ID CATEGORY_ID,
      VOD_CATEGORY_NAME CATEGORY_NAME,
      VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
      VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
      '' as STAT_FLAG
      from
      voduser.PT_VO_CATEGORY_UNITED
      union
      select
      TEST_ID,
      VARIATION_ID,
      CATEGORY_ID,
      CATEGORY_NAME,
      CATEGORY_LEVEL,
      PARENT_CATEGORY_ID,
      STAT_FLAG
      from
      VODUSER.PT_AB_CATEGORY
      except
      select
      '' as TEST_ID,
      '' as VARIATION_ID,
      A.VOD_CATEGORY_ID CATEGORY_ID,
      A.VOD_CATEGORY_NAME CATEGORY_NAME,
      A.VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
      A.VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
      '' as STAT_FLAG
      from
      voduser.PT_VO_CATEGORY_UNITED A,
      VODUSER.PT_AB_CATEGORY B
      where
      A.VOD_CATEGORY_ID = B.CATEGORY_ID
      and B.TEST_ID = CT.TEST_ID
      and B.VARIATION_ID = CT.VARIATION_ID
      ) T
      where
      coalesce(nullif(STAT_FLAG, ''), 'U') <> 'D'),
      CATEGORY_LIST as(
      select
      1 as level,
      T.*
      from
      TMP_LIST T
      where
      CATEGORY_ID = CT.CATEGORY_ID
      and TEST_ID = CT.TEST_ID
      and VARIATION_ID = CT.VARIATION_ID
      union all
      select
      level + 1 as level,
      T.*
      from
      CATEGORY_LIST R
      inner join TMP_LIST T on
      R.PARENT_CATEGORY_ID = T.CATEGORY_ID
      and CT.TEST_ID = coalesce(nullif(T.TEST_ID, ''), CT.TEST_ID)
      and CT.VARIATION_ID = coalesce(nullif(T.VARIATION_ID, ''), CT.VARIATION_ID)
      )
      select
      CATEGORY_ID,
      CATEGORY_NAME,
      CATEGORY_LEVEL
      from
      CATEGORY_LIST
      ) T
      ) as FORM_ROUTE
      저작자표시 비영리 변경금지

      'Postgresql / PPAS > Query tuning' 카테고리의 다른 글

      [완료]/* cuesheet.base.getCueSheetItemDetailForLayer */  (0) 2022.08.29
      [완료] /* content.base.seriesListPageData */  (0) 2022.08.29
      [완료]schedule.distr.newScheduleListPageData  (0) 2022.08.29
      [완료] /* modifySchedule.base.selectContentListBySeriesIdDup */  (2) 2022.08.29
      [완료] /* modifySchedule.base.selectContentListBySeriesId */  (0) 2022.08.29

      관련글 관련글 더보기

    Designed by Tistory.

    티스토리툴바