database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 8521 게시물 읽기
No. 8521
아래 쿼리 속도 문제..
작성자
이기자(k3i2)
작성일
2009-10-08 16:23
조회수
7,511

//--------------- 인덱스는 아래처럼 생성했습니다. --------------------//

CREATE INDEX t_sale_idx1
  ON t_sugangsave
  USING btree
  (g_jjdc);

CREATE INDEX t_sale_idx2
  ON t_sugangsave
  USING btree
  (j_jjdc);


CREATE INDEX t_sale_idx3
  ON t_sugangsave
  USING btree
  (m_jjdc);

//-------------- 쿼리 입니다. ------------------------------------------------//
//---- 쿼리결과 속도가 훨씬 더걸립니다. T.T ; 결과를 못보고 스톱시킬만큼... -----//

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'));


//---------------- explain analyze 결과 입니다. ---------------------------------------//

//---- 이게 맞나요? DataOutput 과 History가 있던데, DataOutput 부분입니다. --------------//

"Nested Loop Left Join  (cost=0.00..34019360.05 rows=39 width=143) (actual time=2539.396..558452.883 rows=1079 loops=1)"
"  ->  Seq Scan on t_sugangsave_gita a  (cost=0.00..34005313.80 rows=39 width=129) (actual time=2536.402..550262.881 rows=1079 loops=1)"
"        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=904.94..904.95 rows=1 width=6) (actual time=17.955..17.956 rows=1 loops=14718)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave s3  (cost=0.00..904.93 rows=1 width=6) (actual time=17.927..17.943 rows=0 loops=14718)"
"                      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=904.94..904.95 rows=1 width=6) (actual time=17.987..17.988 rows=1 loops=15767)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave s3  (cost=0.00..904.93 rows=1 width=6) (actual time=17.361..17.977 rows=0 loops=15767)"
"                      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) (actual time=0.023..0.024 rows=1 loops=1471)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.016 rows=2 loops=1471)"
"                      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) (actual time=0.023..0.024 rows=1 loops=4203)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.017 rows=2 loops=4203)"
"                      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) (actual time=0.026..0.027 rows=1 loops=14408)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.017 rows=3 loops=14408)"
"                      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) (actual time=0.049..0.049 rows=1 loops=18560)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.030..0.036 rows=3 loops=18560)"
"                      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) (actual time=5.820..7.451 rows=1 loops=1079)"
"        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) (actual time=0.026..0.026 rows=1 loops=1079)"
"          ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.017 rows=2 loops=1079)"
"                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) (actual time=0.051..0.051 rows=1 loops=1079)"
"          ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.030..0.036 rows=2 loops=1079)"
"                Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
"Total runtime: 558455.855 ms"


//---------------- History 입니다. -----------------------------//

-- Executing query:
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'));
ERROR:  canceling statement due to user request


-- Executing query:
EXPLAIN ANALYZE 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'));

Total query runtime: 558578 ms.
Data retrieval runtime: 16 ms.
34 rows retrieved.
이 글에 대한 댓글이 총 4건 있습니다.

explain 값을 보니, subplan 쪽의 비용이 얼마가 되는지 모르니,

마지막 outer join에서 문제를 일으키나 봅니다.


Seq Scan on t_sugangsave_gita a  (cost=0.00..34005313.80 rows=39 width=129) (actual time=2536.402..550262.881 rows=1079 loops=1)


일단 필요한 작업은 아래

(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,


저 explain 결과로 추측하면, sugansave 랑 sugansave_gita 두 테이블의 조인에서 아마 어마어마한 비용을 소비하는가봅니다.


저런 복잡한 쿼리들의 튜닝 작업은 일단 그 안에서 쓰이는 모든 서브 쿼리들의 비용을 먼저 산정해보세요. 그리고, 각각의 쿼리들이 깔끔하게 돌아가는지부터 확인하고,

이것들을 어떻게 합칠 때, 갑자기 비용이 느는지를 밝혀야합니다.

그리고, 찾아냈다면, 그것을 피할 수 있는 방법을 찾아야하고.


구체적으로 테이블 구조와 샘플 자료를 올려주시면,

여기 있는 분들 가운데 호기심 많은 분들이 가장 적절한 쿼리를 만들어줄지도 모르겠네요.


일단은 제가 보기에는 늘 그랬듯이, 이런 문제는 요구분석이 미흡해서, 설계가 잘못된 경우인 것 같습니다. 튼튼하지 못한 자료설계 기반으로 응용프로그램을 운영하려고 하니, 저런 얄궂은 쿼리들이 나오는게 아닐까싶습니다. 이런 경우는 자료구조를 다시 짜는 것이 오히려 비용이 싸죠.

김상기(ioseph)님이 2009-10-08 17:36에 작성한 댓글입니다.

감사합니다.

생각을 많이 해봐야겠군요

이기자님이 2009-10-08 17:42에 작성한 댓글입니다. Edit Ｘ

일때문에 댓글을 좀 늦게 달게 되었네요.

인덱스 생성 후에도 결과적으로 플랜은 완전히 똑같기 때문에 그거때문에 더 느려진건 아니구요.

explain에 analyze까지 하게 되면 조금 더 느려지는건 맞습니다. ^^;

아무튼 새로 생성된 인덱스는 전혀 타지 않네요.

이런거는 그저 스키마랑 샘플 데이터를 놓고 작업하는게 편하지만...

사정이 어떠신지 모르니;;


올려주신 플랜을 보면 마지막의 outer join보다는 그 전에 문제가 있다고 생각됩니다.


"Nested Loop Left Join  (cost=0.00..34019360.05 rows=39 width=143) (actual time=2539.396..558452.883 rows=1079 loops=1)"
"  ->  Seq Scan on t_sugangsave_gita a  (cost=0.00..34005313.80 rows=39 width=129) (actual time=2536.402..550262.881 rows=1079 loops=1)"
"        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=904.94..904.95 rows=1 width=6) (actual time=17.955..17.956 rows=1 loops=14718)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave s3  (cost=0.00..904.93 rows=1 width=6) (actual time=17.927..17.943 rows=0 loops=14718)"
"                      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=904.94..904.95 rows=1 width=6) (actual time=17.987..17.988 rows=1 loops=15767)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave s3  (cost=0.00..904.93 rows=1 width=6) (actual time=17.361..17.977 rows=0 loops=15767)"
"                      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) (actual time=0.023..0.024 rows=1 loops=1471)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.016 rows=2 loops=1471)"
"                      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) (actual time=0.023..0.024 rows=1 loops=4203)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.017 rows=2 loops=4203)"
"                      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) (actual time=0.026..0.027 rows=1 loops=14408)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.017 rows=3 loops=14408)"
"                      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) (actual time=0.049..0.049 rows=1 loops=18560)"
"                ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.030..0.036 rows=3 loops=18560)"
"                      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) (actual time=5.820..7.451 rows=1 loops=1079)"
"        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) (actual time=0.026..0.026 rows=1 loops=1079)"
"          ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.013..0.017 rows=2 loops=1079)"
"                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) (actual time=0.051..0.051 rows=1 loops=1079)"
"          ->  Index Scan using t_sugangsave_pkey on t_sugangsave b  (cost=0.00..4.33 rows=1 width=12) (actual time=0.030..0.036 rows=2 loops=1079)"
"                Index Cond: (((mid)::text = ($0)::text) AND (stu_c = $1) AND ((yb)::text = ($2)::text))"
"Total runtime: 558455.855 ms"


즉... t_sugang_save_gita 테이블의 1건당 서브플랜에서 14718, 15767건의 루프를 돌면서

t_sugang_save_gita 건수 * 14718 * 15767

요렇게만 해도 벌써 스캔해야 되는 로우 수가 엄청나게 됩니다.

SHK(rusion)님이 2009-10-14 13:02에 작성한 댓글입니다.

처음에 플랜을 제대로 안봐서 분류 쪽에 인덱스를 두라고 말씀 드렸었는데

이렇게 되면 

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

여기서 쓰이는

dan_date1 컬럼과

dan_date2 컬럼에

각각 단독 인덱스를 생성하시고, 다시 테스트 결과를 올려 주세요. :)

저도 초짜인지라;; 스키마랑 샘플데이타 없이는 아무래도 힘드네요. -ㅁ-;

SHK(rusion)님이 2009-10-14 13:10에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
8524windows에서 sql shell 명령어 사용 질문드립니다. [1]
질문자
2009-10-12
8064
8523윈도우즈에서 postgresql 설치 메뉴얼은? [1]
박병호
2009-10-09
7688
8522속도 [2]
eee
2009-10-09
7108
8521아래 쿼리 속도 문제.. [4]
이기자
2009-10-08
7511
8520아래 쿼리의 EXPLAIN. [3]
이기자
2009-10-08
7238
8519쿼리문좀 봐주세요 T.T 거북이 보다 더 느립니다. [3]
이기자
2009-10-08
7602
8518연달아서 질문입니다 ㅡ,.ㅡ [1]
초보자
2009-10-06
6728
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는 PostgreSQL v16.2로 자료를 관리합니다