Postgresql / PPAS/Query tuning
[완료]/* contentMonitoring.distr.selectAlbumInfoDistrList */
원샷원따봉
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.series_name, a_1.vod_type, a_1.music_cont_type, a_1.update_date" " Sort Key: a_1.insert_date DESC" " Sort Method: external merge Disk: 48296kB" " Buffers: shared hit=20793, temp read=24264 written=24328" " Worker 0: actual time=303.531..370.174 rows=320123 loops=1" " Sort Method: external merge Disk: 47192kB" " Buffers: shared hit=6960, temp read=8028 written=8049" " Worker 1: actual time=303.256..373.003 rows=316048 loops=1" " Sort Method: external merge Disk: 45880kB" " Buffers: shared hit=6704, temp read=7844 written=7865" |
TO-BE
| -> Sort (cost=10000127110.87..10000129541.07 rows=972082 width=151) (actual time=895.304..973.512 rows=973519 loops=1)" " 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.series_name, a_1.vod_type, a_1.music_cont_type, a_1.update_date" " Sort Key: a_1.insert_date DESC" " Sort Method: quicksort Memory: 255838kB" " Buffers: shared hit=20713" |
* 원본 쿼리
| SELECT /* contentMonitoring.distr.selectAlbumInfoDistrList */ * FROM ( SELECT ALBUM_ID , ALBUM_NAME , GENRE_LARGE , GENRE_MID , GENRE_SMALL , SERIES_ID_IMCS , SERIES_NAME , VOD_TYPE , MUSIC_CONT_TYPE , INSERT_DATE , UPDATE_DATE , COUNT(1) OVER() TOTAL_CNT , ROW_NUMBER() OVER(ORDER BY INSERT_DATE DESC) AS RNUM FROM IMCSUSER.PT_LW_ALBUM_INFO A WHERE 1=1 ) A WHERE A.RNUM BETWEEN 0 + 1 AND 0 + 20 |