8.1 -> 8.4버전으로 업하면서 dump받았던 데이타를 Restore시켰더니 똑같은 쿼리인데 엄청 느려졌습니다.
Select t_in.mcode, t_in.serial
From t_in left join t_out on t_in.mcode=t_out.mcode and
t_in.serial=t_out.serial and t_out.jobday <= '2011-03-17'::date
left join t_bi on t_in.mcode=t_bi.mcode and t_in.serial=t_bi.serial and t_bi.jobday <= '2011-03-17'::date
left join t_sale on t_in.mcode=t_sale.mcode and t_in.serial=t_sale.serial and t_sale.status<>'C' and t_sale.status<>'CA' and t_sale.saledate <= '2011-03-17'::date
left join t_sale_ucard on (select status
from t_sale
where t_sale.idx=t_sale_ucard.idx_sub)<>'C' and
(select status
from t_sale
where t_sale.idx=t_sale_ucard.idx_sub)<>'CA' and
t_in.mcode=t_sale_ucard.umcode and t_in.serial=t_sale_ucard.userial and
t_sale_ucard.saledate <= '2011-03-17'::date
left join t_gong on t_in.mcode=t_gong.mcode and t_in.serial=t_gong.serial and t_gong.jobday <= '2011-03-17'::date
left join t_bp on t_in.mcode=t_bp.mcode and t_in.serial=t_bp.serial and t_bp.jobday <= '2011-03-17'::date and
(t_in.jobday < t_bp.jobday or (t_in.jobday = t_bp.jobday and t_in.settime < t_bp.settime))
left join t_gp on t_in.mcode=t_gp.mcode and t_in.serial=t_gp.serial and t_gp.jobday <= '2011-03-17'::date
where coalesce(t_in.debenture_debt, '') <> 'M' and t_in.jobday <= '2011-03-17'::date and
( t_sale.idx is null and t_sale_ucard.idx_sub is null and t_gong.idx is null and t_bp.idx is null )
group by t_in.mcode, t_in.serial
위 쿼리에서 밑에서 두번째,
( t_sale.idx is null and t_sale_ucard.idx_sub is null and t_gong.idx is null and t_bp.idx is null )
이것만 없으면 빠릅니다.
근데, 이전 버전땐 안없애도 빨랐거든요. 왜 이러죠?
인덱스도 그대로 살아있는데...
|