SQL - partition by 성능개선

row_number() over (partition by ~ order by ~)

문법을 사용하는 도중 성능 이슈 문제가 발견되어 몇 가지 개선방법을 기록하기 위해 글을 남깁니다.

1. partition by ~ order by ~ 사용할 때 partition by 컬럼과 order by 컬럼에 index를 걸어줍니다.

2. 만약 partition by 컬럼과 order by 컬럼이 한 테이블에 없다면, 

 

 

with book(brand_cd, wish_dt) as (
	select 
		brand_cd
		,create_dt as wish_dt
	from ecomms.tpd_brand_bookmark book
	where book.mem_no = (select mem_no from "member".tmb_mem where mem_no=13336558 and site_cd ='1')
	and book.site_cd = '1'
	--<if test='sortBrd != null and sortBrd.equals("R")'></if>	-- R 최근 찜한 순 
	--order by book.create_dt desc
	--limit 10
),
brand_hgrnk(brand_cd, brand_no, main_nm_kr, wish_dt) as (
	select 
		tem_brand.brand_cd
		, tem_brand.brand_no
		, (select hgrnk_brand_nm from ecomms.tpd_brand_hgrnk where hgrnk_brand_cd=tem_brand.hgrnk_brand_cd) as main_nm_kr
		, book.wish_dt
	from ecomms.tpd_brand_lwrnk tem_brand
	inner join book on book.brand_cd like 'BH%' and book.brand_cd = tem_brand.hgrnk_brand_cd
),
brand(brand_cd, brand_no, main_nm_kr) as (
select 
lwk.brand_cd
,lwk.brand_no
,lwk.main_nm_kr
,book.wish_dt
from ecomms.tpd_brand_lwrnk lwk
inner join book on book.brand_cd = lwk.brand_cd 
where not exists(
	select 
	tem.brand_cd
	, tem.brand_no
	, tem.main_nm_kr
	, tem.wish_dt
	from brand_hgrnk tem
	where tem.brand_no = lwk.brand_no
)
union all 
select 
hgrnk.brand_cd
,hgrnk.brand_no
,hgrnk.main_nm_kr
,hgrnk.wish_dt
from brand_hgrnk hgrnk
order by main_nm_kr asc
),
product(
rak,
prd_no,
prd_nm,
site_cd,
brand_no,
brand_cd,
main_nm_kr,
norm_prc,
price,
dsc_rt,
sel_prc,
basic_ext_nm,
hztl_ext_nm,
wish_dt
) as (
	select
	row_number() over (partition by inner_temp.brand_no order by inner_temp.price desc) as rak,
	inner_temp.prd_no,
	inner_temp.prd_nm,
	inner_temp.site_cd,
	inner_temp.brand_no,
	inner_temp.brand_cd,
	inner_temp.main_nm_kr,
	inner_temp.norm_prc,
	inner_temp.price,
	inner_temp.dsc_rt,
	inner_temp.sel_prc,
	inner_temp.basic_ext_nm,
	inner_temp.hztl_ext_nm,
	inner_temp.wish_dt
	from (
		select 
			--row_number() over(partition by tp.brand_no order by coalesce(case when #{deviceCd} = '001' then td.dc_prc_pc when #{deviceCd} = '002' then td.dc_prc_mc else td.dc_prc_app END, prc.sel_prc) desc) prd_rank,
		--	row_number() over(partition by tp.brand_no order by td.dc_prc_pc) prd_rank, -- 할인순
			--<if test='sortPrd != null and sortPrd.equals("N")'></if>	-- 최신순
		    --row_number() over(partition by tp.brand_no order by tp.create_dt desc) prd_rank, -- 최신순
			tp.prd_no,
			tp.prd_nm,
			tp.site_cd,
			tp.brand_no,
			brand.brand_cd,
			brand.main_nm_kr,
			prc.norm_prc,
			--coalesce(case when #{deviceCd} = '001' then td.dc_prc_pc when #{deviceCd} = '002' then td.dc_prc_mc else td.dc_prc_app END, prc.sel_prc) as price,
			td.dc_prc_pc as price,
			--coalesce(case when #{deviceCd} = '001' then td.tot_rate_pc when #{deviceCd} = '002' then td.tot_rate_mc else td.tot_rate_app END, prc.dsc_rt) as dsc_rt,
			td.tot_rate_pc as dsc_rt,
			coalesce(td.norm_prc, prc.norm_prc) as sel_prc,
			img.basic_ext_nm,
			img.hztl_ext_nm,
			brand.wish_dt
		from ecomms.tpd_prd tp
		inner join brand on brand.brand_no = tp.brand_no
		inner join ecomms.tpd_prd_prc prc on prc.prd_no = tp.prd_no
		inner join ecomms.tpd_prd_image img on img.prd_no = tp.prd_no
		inner join searchs.ttmp_dynamic td on td.prd_no = tp.prd_no
		where 
		tp.create_dt :: date between (now() + '-6 months')::date and now()::date
		and tp.site_cd = '1'
		and tp.use_yn = 'Y'
		and tp.prd_stat_cd = '05'
		and tp.disp_yn = 'Y'
		and tp.prd_sel_cd = '02'
	) inner_temp
)
select 
product.rak,
product.prd_no,
product.prd_no,
product.prd_nm,
product.site_cd,
product.brand_no,
product.brand_cd,
product.main_nm_kr,
product.norm_prc,
product.price,
product.dsc_rt,
product.sel_prc,
product.basic_ext_nm,
product.hztl_ext_nm,
product.wish_dt
from product product
where product.rak <= 8
;