[완료]/* contentMonitoring.distr.selectAlbumInfoDistrList */

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