Postgresql / PPAS/Query tuning
-
[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 '01013..
-
[완료]/*stillCut.base.stillCutListPageData*/Postgresql / PPAS/Query tuning 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..
-
[완료]/* jobQuesues.distr.getJobQueuesRcmInfoListForPage */Postgresql / PPAS/Query tuning 2022. 8. 29. 15:16
AS-IS Total query runtime: 20Min ~ TO-BE Total query runtime: 0.7 Sec 개선효과 99.9% 개선 포인트 AS-IS WHERE C.CONTENTS_ID = D.ALBUM_ID ORDER BY C.JOB_DATE DESC TO-BE 위의 Order By를 해당 위치로 옮겨줌 추가적으로 PARTITION_NM 을 구할수 있으면 같이 넣어줌 AND A.JOB_DATE BETWEEN '20220817'||'0000000000' AND '20220824'||'2359599999' AND A.partition_mm = '08' ORDER BY JOB_DATE DESC * 원본 쿼리 SELECT /* jobQuesues.distr.getJobQue uesRcmInf..
-
[완료]/* contentMonitoring.distr.selectAlbumInfoDistrList */Postgresql / PPAS/Query tuning 2022. 8. 29. 15:09
AS-IS Total query runtime: 3 secs 66 msec. TO-BE Total query runtime: 1 secs 992 msec. 개선효과 45.15% Tuning Parameter set work_mem=300000 개선 포인트 AS-IS -> Sort (cost=10000097295.57..10000098308.15 rows=405034 width=151) (actual time=307.620..372.198 rows=324506 loops=3)" " Output: a_1.insert_date, a_1.album_id, a_1.album_name, a_1.genre_large, a_1.genre_mid, a_1.genre_small, a_1.series_id_imcs, a_1..
-
[완료] /*modifySchedule.base.selectContentList */Postgresql / PPAS/Query tuning 2022. 8. 29. 14:41
AS-IS Total query runtime: 3 secs 65 msec. TO-BE Total query runtime: 465 msec. 개선효과 92.15% AS-IS Plan NODE -> Index Scan using pk_pt_la_album_info on imcsuser.pt_la_album_info aif (cost=0.42..0.52 rows=1 width=111) (actual time=0.003..0.003 rows=0 loops=519621)" " Output: aif.album_id, aif.album_name, aif.album_date, aif.series_id, aif.series_name, aif.series_no, aif.onair_date, aif.onair_date_..
-
[완료] /* index.base.getNewRegIndexAlbumList */Postgresql / PPAS/Query tuning 2022. 8. 29. 14:31
AS-IS 2.5 sec TO-BE 1 sec 개선 포인트 api에서 set WORK_MEM=102400 CREATE INDEX idx_pt_la_asset_info_05 ON imcsuser.pt_la_asset_info USING btree (album_id,screen_type); 생성으로 1 Sec * 원본 쿼리 /* index.base.getNewRegIndexAlbumList */ /*CREATE OR REPLACE FUNCTION pg_temp.FN_GETSTRPOS(STR TEXT) RETURNS INT AS $RETVAL$ DECLARE RETVAL INT; BEGIN SELECT STRPOS(STR, (REGEXP_MATCHES(STR,'[^0-9]'))[1]) INTO RETVAL; ..
-
[완료]/*VoteRelation.base.selectAlbumList*/Postgresql / PPAS/Query tuning 2022. 8. 29. 14:27
AS-IS 20 sec TO-BE 17 ms 개선 효과 : 99.91% * 원본 쿼리 SELECT /*VoteRelation.base.selectAlbumList*/ A.TOTAL_CNT , A.RNUM , A.ALBUM_ID , A.ALBUM_NAME , TO_CHAR(TO_TIMESTAMP(A.ALBUM_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') ALBUM_DATE FROM ( SELECT /*+ LEADING(AI) INDEX(AI PK_PT_LA_ALBUM_INFO) USE_NL(AI S)*/ AI.ALBUM_ID , AI.ALBUM_NAME , AI.ALBUM_DATE , COUNT(AI.ALBUM_ID) OVER() AS TOTAL_CNT , ROW_NUMBER(..
-
[완료]/*contentExpand.distr.selectExposureNewScheduleList*/Postgresql / PPAS/Query tuning 2022. 8. 29. 14:17
[튜닝 결과] 튜닝 전 : 2.7초 튜닝 후 : 0.3초 개선효과 : 88% [튜닝 내용] 튜닝 후 sql 줄번호 209 ~ 216 참고 조건을 초기 필터 조건으로 추가하여 GROUP BY 대상을 줄임. 218663 건을 70건으로 줄임 * 원본 쿼리 SELECT /*contentExpand.distr.selectExposureNewScheduleList*/ * FROM( SELECT AA.SCHEDULE_ID, AA.SCHEDULE_TYPE, AA.SCHEDULE_TITLE, AA.SCREEN_TYPE_LIST, CASE WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 1, 1) = '1' THEN 'I' WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND..