|
혹시 이거말씀하신건가요?
"Nested Loop Left Join (cost=0.00..34045933.70 rows=39 width=143)"
" -> Seq Scan on t_sugangsave_gita a (cost=0.00..34031887.45 rows=39 width=129)"
" Filter: (((mid)::text = 'scall'::text) AND ((((subplan) >= '20090101'::text) AND ((subplan) <= '20091010'::text)) OR (((subplan) >= '20090101'::text) AND ((subplan) <= '20091010'::text))) AND (((subplan) = 'D6'::text) OR ((subplan) = 'D96'::text)))"
" SubPlan"
" -> Aggregate (cost=905.65..905.66 rows=1 width=6)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave s3 (cost=0.00..905.65 rows=1 width=6)"
" Index Cond: (((mid)::text = ($0)::text) AND ((yb)::text = ($2)::text))"
" Filter: (((COALESCE(cancel, ''::character varying))::text <> 'Y'::text) AND (((m_jjdc)::text = 'D6'::text) OR ((m_jjdc)::text = 'D96'::text)))"
" -> Aggregate (cost=905.65..905.66 rows=1 width=6)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave s3 (cost=0.00..905.65 rows=1 width=6)"
" Index Cond: (((mid)::text = ($0)::text) AND ((yb)::text = ($2)::text))"
" Filter: (((COALESCE(cancel, ''::character varying))::text <> 'Y'::text) AND (((m_jjdc)::text = 'D6'::text) OR ((m_jjdc)::text = 'D96'::text)))"
" -> Aggregate (cost=4.34..4.35 rows=1 width=12)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave b (cost=0.00..4.33 rows=1 width=12)"
" Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
" -> Aggregate (cost=4.34..4.35 rows=1 width=12)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave b (cost=0.00..4.33 rows=1 width=12)"
" Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
" -> Aggregate (cost=4.34..4.35 rows=1 width=12)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave b (cost=0.00..4.33 rows=1 width=12)"
" Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
" -> Aggregate (cost=4.34..4.35 rows=1 width=12)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave b (cost=0.00..4.33 rows=1 width=12)"
" Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
" -> Index Scan using t_student_pkey on t_student st (cost=0.00..351.37 rows=1 width=31)"
" Index Cond: (((st.mid)::text = 'scall'::text) AND (st.stu_c = "outer".stu_c))"
" SubPlan"
" -> Aggregate (cost=4.34..4.35 rows=1 width=12)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave b (cost=0.00..4.33 rows=1 width=12)"
" Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
" -> Aggregate (cost=4.34..4.35 rows=1 width=12)"
" -> Index Scan using t_sugangsave_pkey on t_sugangsave b (cost=0.00..4.33 rows=1 width=12)"
" Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
//------- 쿼리입니다. 속도때문에 대분류,중분류는 빼고 과목만 조건절에 넣었습니다. -------------------------------------------------------------------------///
EXPLAIN SELECT a.stu_c,
'수강생명' AS stu_n,
(substring(a.yb,1,4)||'-'||substring(a.yb,5,4))as yb,
case length(coalesce(st.ptel,''))
when 11 then (substr(st.ptel,1,3)||'-'||substr(st.ptel,4,4)||'-'||'0000')
when 10 then (substr(st.ptel,1,3)||'-'||substr(st.ptel,4,3)||'-'||'0000')
when 8 then (substr(st.ptel,1,4)||'-'||'0000')
when 7 then (substr(st.ptel,1,3)||'-'||'0000')
else '' end as tel,
coalesce(a.so_j,0)+ coalesce(a.so_d,0)as geum,
a.deung_day,
(case coalesce(a.msin_text,'')when''then''else'종합:'||a.msin_text end ||
case coalesce(a.dan_sin_text,'')when''then''else' 단과:'||a.dan_sin_text end)as disp_sin,
(select min(b.dan_date1) from t_sugangsave b
where b.mid=a.mid and b.stu_c=a.stu_c and b.yb=a.yb)as gigan,
(select max(b.dan_date2) from t_sugangsave b
where b.mid=a.mid and b.stu_c=a.stu_c and b.yb=a.yb)as gigan2,
case coalesce(a.j_jjdn,'')when''then'단과'else
case coalesce(a.dan_sin_text,'')when''then a.j_jjdn else '단과/'|| a.j_jjdn end end as j_jjdn,
0 as chk
FROM t_sugangsave_gita a left outer join
t_student st on st.mid=a.mid and st.stu_c=a.stu_c
Where a.mid='scall' and
( ((select min(b.dan_date1) from t_sugangsave b
where b.mid=a.mid and b.stu_c=a.stu_c and b.yb=a.yb) between '20090101' and '20091010') or
((select min(b.dan_date2) from t_sugangsave b
where b.mid=a.mid and b.stu_c=a.stu_c and b.yb=a.yb) between '20090101' and '20091010')) and
((select max(s3.m_jjdc) from t_sugangsave s3
where s3.mid=a.mid and s3.yb=a.yb and
coalesce(s3.cancel,'')<>'Y' and s3.m_jjdc in('D6','D96'))in('D6','D96')); <-- 속도때문에 과목도 두개코드만 했음.
|
