Postgresql / PPAS/Query tuning
-
[완료]schedule.distr.newScheduleListPageDataPostgresql / PPAS/Query tuning 2022. 8. 29. 11:48
검증계 AS-IS Successfully run. Total query runtime: 50 secs 759 msec. TO-BE Successfully run. Total query runtime: 48 secs 906 msec. 운영계 AS-IS 총 쿼리 실행시간: 22 secs 75 msec. TO-BE 총 쿼리 실행시간: 19 secs 하기 조건으로 인해 19초 이하로 튜닝 불가. rownum 3초 min/max 16초 → 위 로우넘과 통계 함수를 제거하면 0.07 초 가 나옵니다 인덱스 생성 스크립트 --INDEX create index idx_pt_lw_sche_mst_04 on IMCSUSER.PT_LW_SCHE_MST (UPDATE_ID); create index idx_pt_lw_sche..
-
[완료] /* modifySchedule.base.selectContentListBySeriesIdDup */Postgresql / PPAS/Query tuning 2022. 8. 29. 11:29
DEVSTP Mylg DB 수행 시간 : AS-IS : 2.9 sec TO-BE : 60ms 개선효과 : 97.93 % 원본 쿼리 WITH SUPER AS ( SELECT ALBUM_ID AS SERIES_ID FROM IMCSUSER.PT_LA_ALBUM_RELATION A WHERE A.ALBUM_GROUP_ID IN (SELECT ALBUM_GROUP_ID FROM IMCSUSER.PT_LA_ALBUM_RELATION WHERE ALBUM_ID IN ( '0154348001' ) AND QUALITY_TYPE = A.QUALITY_TYPE AND SALE_TYPE = A.SALE_TYPE AND GROUP_TYPE = 'C' GROUP BY ALBUM_GROUP_ID) UNION SELECT ALB..
-
[완료] /* modifySchedule.base.selectContentListBySeriesId */Postgresql / PPAS/Query tuning 2022. 8. 29. 11:24
DEVSTP Mylg DB 수행 시간 : AS-IS : 3.7 sec TO-BE : 60ms 개선효과 : 98.37 % * 원본 쿼리 WITH SUPER AS ( SELECT ALBUM_ID AS SERIES_ID FROM IMCSUSER.PT_LA_ALBUM_RELATION A WHERE A.ALBUM_GROUP_ID IN (SELECT ALBUM_GROUP_ID FROM IMCSUSER.PT_LA_ALBUM_RELATION WHERE ALBUM_ID IN ( '0154348001' ) AND QUALITY_TYPE = A.QUALITY_TYPE AND SALE_TYPE = A.SALE_TYPE AND GROUP_TYPE = 'C' GROUP BY ALBUM_GROUP_ID) UNION SELECT A..
-
[튜닝불가] /* categoryform.dr.getAlbumSeriesList */Postgresql / PPAS/Query tuning 2022. 8. 29. 11:00
Legacy DB 수행 시간 : 10분 정도 소요 MSA DB – MylgDB STP DB 기준 AS-IS Query 수행 속도 : 3분 * 개선포인트 도출 * 개선 포인트 SET work_mem = '1000MB'; ( 5초 정도 수행 빨라짐) join imcsuser.PT_LA_ALBUM_INFO ALB_INFO on (AST_INFO.ALBUM_ID = ALB_INFO.ALBUM_ID and AST_INFO.SCREEN_TYPE = 'I' ) -- SCREEN_TYPE INDEX 없음 ( I / N ) join imcsuser.PT_LA_ALBUM_SUB ALB_SUB on (AST_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID and ALB_INFO.ALBUM_ID = ALB_SUB...