아래는 3개의 테이블을 검색한 결과를 취득하는 경우입니다.
시작일, 종료일이 있어 오늘의 날과 비교하여
해당 데이터(시작일 <= 오늘 <= 종료일)만 취득 하게 됩니다.
그런데 보통 아래의 실행속도가 100ms가 나오는데
주석 처리한 부분을 포함 시키면 1200ms정도가 실행 속도가 저하됩니다.
다른 테이블에서도 같은 처리를 하고 있는데 왜 이렇게 속도가 저하 되는지..
특별히 인덱스를 주지는 않았습니다.
이 정도의 차이가 어디서 오는지요?
SELECT
a.valid_start_date AS valid_start_date_ap_b, a.valid_end_date AS valid_end_date_ap_b,
c.valid_start_date AS valid_start_date_ap_m, c.valid_end_date AS valid_end_date_ap_m,
d.valid_start_date AS valid_start_date_ap_s, d.valid_end_date AS valid_end_date_ap_s
FROM ap_b_vw a, ap_m_vw c, ap_s_vw d
WHERE
d.ap_b_cd = c.ap_b_cd
AND d.ap_m_cd = c.ap_m_cd
AND c.ap_b_cd = a.ap_b_cd
AND a.mst_kind_cd::text = c.mst_kind_cd::text
AND a.ap_mst_ver = c.ap_mst_ver
AND a.mst_kind_cd::text = d.mst_kind_cd::text
AND a.ap_mst_ver = d.ap_mst_ver
and (c.valid_start_date IS NULL
AND c.valid_end_date IS NULL
OR to_date(c.valid_start_date, 'YYYYMMDD'::text) <= to_date(now(), 'YYYYMMDD'::text)
-- AND c.valid_end_date IS NULL
OR to_char(c.valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)
AND to_char(c.valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text)
OR c.valid_start_date IS NULL
AND to_char(c.valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))
AND (a.valid_start_date IS NULL
AND a.valid_end_date IS NULL
OR to_char(a.valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)
AND a.valid_end_date IS NULL
OR to_char(a.valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)
AND to_char(a.valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text)
OR a.valid_start_date IS NULL
AND to_char(a.valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text));
<EXPALIN ANALYZE 결과>
주석 사용
"Hash Join (cost=36.56..54.07 rows=1 width=48) (actual time=16.287..22.896 rows=499 loops=1)"
" Hash Cond: ((c.ap_b_cd = a.ap_b_cd) AND ((c.mst_kind_cd)::text = (a.mst_kind_cd)::text) AND (c.ap_m_cd = d.ap_m_cd))"
" Join Filter: (a.ap_mst_ver = c.ap_mst_ver)"
" -> Seq Scan on ap_m (cost=0.00..15.84 rows=78 width=581) (actual time=0.181..3.446 rows=190 loops=1)"
" Filter: (((valid_start_date IS NULL) AND (valid_end_date IS NULL)) OR (to_date((valid_start_date)::text, 'YYYYMMDD'::text) <= to_date((now())::text, 'YYYYMMDD'::text)) OR ((to_char(valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))) OR ((valid_start_date IS NULL) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))))"
" -> Hash (cost=36.54..36.54 rows=1 width=116) (actual time=16.051..16.051 rows=499 loops=1)"
" -> Hash Join (cost=7.84..36.54 rows=1 width=116) (actual time=1.317..14.234 rows=499 loops=1)"
" Hash Cond: (((d.mst_kind_cd)::text = (a.mst_kind_cd)::text) AND (d.ap_b_cd = a.ap_b_cd))"
" Join Filter: (a.ap_mst_ver = d.ap_mst_ver)"
" -> Seq Scan on ap_s (cost=0.00..20.78 rows=452 width=289) (actual time=0.107..9.118 rows=537 loops=1)"
" -> Hash (cost=7.72..7.72 rows=8 width=53) (actual time=1.175..1.175 rows=46 loops=1)"
" -> Subquery Scan a (cost=0.00..7.72 rows=8 width=53) (actual time=0.106..1.033 rows=46 loops=1)"
" -> Seq Scan on ap_b (cost=0.00..7.64 rows=8 width=571) (actual time=0.103..0.940 rows=46 loops=1)"
" Filter: (((valid_start_date IS NULL) AND (valid_end_date IS NULL)) OR ((to_char(valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)) AND (valid_end_date IS NULL)) OR ((to_char(valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))) OR ((valid_start_date IS NULL) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))))"
"Total runtime: 23.567 ms"
주석미사용
"Nested Loop (cost=7.84..52.76 rows=1 width=48) (actual time=1.677..1262.605 rows=499 loops=1)"
" Join Filter: ((c.ap_b_cd = a.ap_b_cd) AND ((a.mst_kind_cd)::text = (c.mst_kind_cd)::text) AND (a.ap_mst_ver = c.ap_mst_ver) AND (d.ap_m_cd = c.ap_m_cd))"
" -> Hash Join (cost=7.84..36.54 rows=1 width=116) (actual time=1.344..10.918 rows=499 loops=1)"
" Hash Cond: (((d.mst_kind_cd)::text = (a.mst_kind_cd)::text) AND (d.ap_b_cd = a.ap_b_cd))"
" Join Filter: (a.ap_mst_ver = d.ap_mst_ver)"
" -> Seq Scan on ap_s (cost=0.00..20.78 rows=452 width=289) (actual time=0.150..6.679 rows=537 loops=1)"
" -> Hash (cost=7.72..7.72 rows=8 width=53) (actual time=1.153..1.153 rows=46 loops=1)"
" -> Subquery Scan a (cost=0.00..7.72 rows=8 width=53) (actual time=0.110..1.011 rows=46 loops=1)"
" -> Seq Scan on ap_b (cost=0.00..7.64 rows=8 width=571) (actual time=0.107..0.916 rows=46 loops=1)"
" Filter: (((valid_start_date IS NULL) AND (valid_end_date IS NULL)) OR ((to_char(valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)) AND (valid_end_date IS NULL)) OR ((to_char(valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))) OR ((valid_start_date IS NULL) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))))"
" -> Seq Scan on ap_m (cost=0.00..15.56 rows=22 width=581) (actual time=0.017..2.251 rows=190 loops=499)"
" Filter: (((valid_start_date IS NULL) AND (valid_end_date IS NULL)) OR ((to_date((valid_start_date)::text, 'YYYYMMDD'::text) <= to_date((now())::text, 'YYYYMMDD'::text)) AND (valid_end_date IS NULL)) OR ((to_char(valid_start_date, 'YYYYMMDD'::text) <= to_char(now(), 'YYYYMMDD'::text)) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))) OR ((valid_start_date IS NULL) AND (to_char(valid_end_date, 'YYYYMMDD'::text) >= to_char(now(), 'YYYYMMDD'::text))))"
"Total runtime: 1263.187 ms"
|