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 9322 게시물 읽기
No. 9322
특정한 회원만 쿼리가 느려지는데...
작성자
iyob
작성일
2013-07-31 17:42ⓒ
2013-07-31 17:43ⓜ
조회수
9,623

안녕하세요?

어떻게 문의를 드려야할 지 난감하네요. 어디 하소연할 데도 없고 방법이 없어 여기에 문의를 드립니다.

리눅스에서 postgresql v8.4을 설치해서 사용중입니다. php로 작성했구요.

특정한 회원이 특정한 페이지에서 과도하게 느려지는 현상이 발견되는데 5배 이상 느려지는데 그 이유를 모르겠습니다.

프로세스를 보면 postgres가 cpu 점유률 100%로 쿼리되는 동안 계속 떠 있습니다.

쿼리를 단계별로 출력해서 pgAdmin에서 찍으면 속도는 나오는 데 웹브라우저에서 하면 느려지는 현상이 발생합니다.

대략 짐작되는 이유도 몰라서 여기에 문의를 드려 봅니다. 가능한 원인이라도 나열해 주시면 도움이 되겠습니다.

감사합니다.
이 글에 대한 댓글이 총 5건 있습니다.

postgresql.conf 에서

 log_min_duration_statement 값을 0으로 설정해서 

모든 쿼리의 실행시간을 기록하세요. 

그러면 어느 쿼리에서 정말 늦었는지 확인할 수 있겠죠. 

 

그 다음 그 쿼리의 실행계획을 살펴보세요. 

무엇때문에 쿼리 수행 시간이 많이 걸렸는지 알수 있겠죠.

 

김상기(ioseph)님이 2013-08-05 10:25에 작성한 댓글입니다.

답변주셔서 감사합니다.

일러주신 방법대로 해 보겠습니다.

무더위에 건강하세요.

iyob님이 2013-08-05 14:10에 작성한 댓글입니다. Edit Ｘ

문제가 되는 쿼리를 발견했구요, 아래와 같습니다.

SELECT *
FROM timetable_view
WHERE active = 't' AND member_id = 'SH000092' AND subject_id = 'ENG' AND lesson_type = '1'
ORDER BY period ASC, lesson_sy_seq ASC, student ASC;

위에서 member_id에 따라 쿼리가 실제로 3초여 정도 걸리는가 하면 144초까지 걸립니다.

그래서 위 조건에서 하나씩 테스트해보니 subject_id에서 속도저하가 일어나더군요. 조건을 빼니 속도가 같이 나옵니다. 그런데 왜 그렇게 되는지 모르겠습니다.

위 쿼리를 EXPLAIN 하면 아래와 같습니다. 답변 부탁드립니다. 감사합니다.

"Sort  (cost=2781.39..2781.39 rows=1 width=430)"
"  Sort Key: tt.period, lsy.seq, s.student"
"  ->  Nested Loop Left Join  (cost=867.56..2781.38 rows=1 width=430)"
"        Join Filter: (ttc.code = (public.code.code)::bpchar)"
"        ->  Nested Loop Left Join  (cost=867.56..2775.37 rows=1 width=417)"
"              ->  Nested Loop Left Join  (cost=867.56..2767.09 rows=1 width=392)"
"                    Join Filter: (tt.subject_id = s.id)"
"                    ->  Nested Loop Left Join  (cost=867.56..2766.01 rows=1 width=388)"
"                          Join Filter: (t.subject_id = s.id)"
"                          ->  Nested Loop Left Join  (cost=867.56..2764.94 rows=1 width=385)"
"                                Join Filter: ((tt.member_id = t.member_id) AND (tt.subject_id = t.subject_id))"
"                                ->  Nested Loop Left Join  (cost=867.56..2752.45 rows=1 width=354)"
"                                      Join Filter: (tt.lesson_schoolyear = lsy.id)"
"                                      ->  Nested Loop Left Join  (cost=867.56..2751.16 rows=1 width=327)"
"                                            Join Filter: (tt.lesson_type = (public.code.code)::bpchar)"
"                                            ->  Hash Left Join  (cost=867.56..2745.16 rows=1 width=314)"
"                                                  Hash Cond: (tt.student_id = ss.student_id)"
"                                                  Join Filter: (student_status_last_id((tt.student_id)::text) = ss.id)"
"                                                  ->  Nested Loop Left Join  (cost=768.56..2645.61 rows=1 width=306)"
"                                                        Join Filter: (tt.student_id = s.id)"
"                                                        ->  Nested Loop Left Join  (cost=0.00..597.49 rows=1 width=82)"
"                                                              Join Filter: (tt.register = m.id)"
"                                                              ->  Seq Scan on timetable tt  (cost=0.00..593.42 rows=1 width=72)"
"                                                                    Filter: (active AND (member_id = 'SH000092'::bpchar) AND (subject_id = 'ENG'::bpchar) AND (lesson_type = '1'::bpchar))"
"                                                              ->  Seq Scan on member m  (cost=0.00..2.92 rows=92 width=19)"
"                                                        ->  Hash Left Join  (cost=768.56..2007.62 rows=1800 width=575)"
"                                                              Hash Cond: (s.counselor = m.id)"
"                                                              ->  Hash Left Join  (cost=764.49..1015.80 rows=1800 width=565)"
"                                                                    Hash Cond: (s.class_type_id = ct.id)"
"                                                                    ->  Hash Left Join  (cost=763.44..990.00 rows=1800 width=553)"
"                                                                          Hash Cond: (s.id = (tpi.test_taker)::bpchar)"
"                                                                          ->  Hash Left Join  (cost=267.05..485.93 rows=1800 width=537)"
"                                                                                Hash Cond: ((public.student_status.status_code)::text = (public.code.code)::text)"
"                                                                                ->  Hash Left Join  (cost=260.49..449.92 rows=1800 width=524)"
"                                                                                      Hash Cond: (s.school_id = sch.id)"
"                                                                                      ->  Hash Left Join  (cost=253.42..418.10 rows=1800 width=513)"
"                                                                                            Hash Cond: (s.schoolyear_id = sy.id)"
"                                                                                            ->  Hash Left Join  (cost=252.13..392.06 rows=1800 width=504)"
"                                                                                                  Hash Cond: (s.id = sf.student_id)"
"                                                                                                  ->  Hash Left Join  (cost=239.03..369.72 rows=1800 width=430)"
"                                                                                                        Hash Cond: (s.id = public.student_status.student_id)"
"                                                                                                        ->  Seq Scan on student s  (cost=0.00..96.00 rows=1800 width=365)"
"                                                                                                        ->  Hash  (cost=220.98..220.98 rows=1444 width=76)"
"                                                                                                              ->  Hash Left Join  (cost=123.49..220.98 rows=1444 width=76)"
"                                                                                                                    Hash Cond: (public.student_status.subacademy_id = sc.id)"
"                                                                                                                    ->  Nested Loop  (cost=122.33..199.97 rows=1444 width=69)"
"                                                                                                                          ->  HashAggregate  (cost=122.33..124.33 rows=200 width=4)"
"                                                                                                                                ->  HashAggregate  (cost=77.33..99.83 rows=1800 width=15)"
"                                                                                                                                      ->  Seq Scan on student_status  (cost=0.00..62.89 rows=2889 width=15)"
"                                                                                                                          ->  Index Scan using student_status_pkey on student_status  (cost=0.00..0.37 rows=1 width=69)"
"                                                                                                                                Index Cond: (public.student_status.id = (max(public.student_status.id)))"
"                                                                                                                    ->  Hash  (cost=1.07..1.07 rows=7 width=11)"
"                                                                                                                          ->  Seq Scan on subacademy sc  (cost=0.00..1.07 rows=7 width=11)"
"                                                                                                  ->  Hash  (cost=9.98..9.98 rows=249 width=85)"
"                                                                                                        ->  Hash Left Join  (cost=1.07..9.98 rows=249 width=85)"
"                                                                                                              Hash Cond: (sf.discount_id = dc.id)"
"                                                                                                              ->  Seq Scan on student_family sf  (cost=0.00..5.49 rows=249 width=34)"
"                                                                                                              ->  Hash  (cost=1.03..1.03 rows=3 width=54)"
"                                                                                                                    ->  Seq Scan on price_discount dc  (cost=0.00..1.03 rows=3 width=54)"
"                                                                                            ->  Hash  (cost=1.13..1.13 rows=13 width=12)"
"                                                                                                  ->  Seq Scan on schoolyear sy  (cost=0.00..1.13 rows=13 width=12)"
"                                                                                      ->  Hash  (cost=4.81..4.81 rows=181 width=15)"
"                                                                                            ->  Seq Scan on school sch  (cost=0.00..4.81 rows=181 width=15)"
"                                                                                ->  Hash  (cost=5.74..5.74 rows=66 width=16)"
"                                                                                      ->  Seq Scan on code  (cost=0.00..5.74 rows=66 width=16)"
"                                                                                            Filter: ((codegroup)::text = ANY ('{new_reason,stop_reason,repay_reason}'::text[]))"
"                                                                          ->  Hash  (cost=495.23..495.23 rows=93 width=54)"
"                                                                                ->  Hash Left Join  (cost=455.91..495.23 rows=93 width=54)"
"                                                                                      Hash Cond: ((tpi.test_taker)::text = (public.test_paper_info.test_taker)::text)"
"                                                                                      ->  GroupAggregate  (cost=444.97..481.62 rows=93 width=15)"
"                                                                                            ->  Sort  (cost=444.97..456.80 rows=4731 width=15)"
"                                                                                                  Sort Key: tpi.test_taker"
"                                                                                                  ->  Hash Left Join  (cost=3.83..156.20 rows=4731 width=15)"
"                                                                                                        Hash Cond: (tr.test_paper_info_id = tpi.id)"
"                                                                                                        ->  Seq Scan on test_result tr  (cost=0.00..87.31 rows=4731 width=8)"
"                                                                                                        ->  Hash  (cost=2.26..2.26 rows=126 width=15)"
"                                                                                                              ->  Seq Scan on test_paper_info tpi  (cost=0.00..2.26 rows=126 width=15)"
"                                                                                      ->  Hash  (cost=9.77..9.77 rows=93 width=19)"
"                                                                                            ->  Hash Semi Join  (cost=6.14..9.77 rows=93 width=19)"
"                                                                                                  Hash Cond: (public.test_paper_info.id = "ANY_subquery".max)"
"                                                                                                  ->  Seq Scan on test_paper_info  (cost=0.00..2.26 rows=126 width=23)"
"                                                                                                  ->  Hash  (cost=4.98..4.98 rows=93 width=4)"
"                                                                                                        ->  Subquery Scan "ANY_subquery"  (cost=2.89..4.98 rows=93 width=4)"
"                                                                                                              ->  HashAggregate  (cost=2.89..4.05 rows=93 width=15)"
"                                                                                                                    ->  Seq Scan on test_paper_info  (cost=0.00..2.26 rows=126 width=15)"
"                                                                    ->  Hash  (cost=1.02..1.02 rows=2 width=15)"
"                                                                          ->  Seq Scan on class_type ct  (cost=0.00..1.02 rows=2 width=15)"
"                                                              ->  Hash  (cost=2.92..2.92 rows=92 width=19)"
"                                                                    ->  Seq Scan on member m  (cost=0.00..2.92 rows=92 width=19)"
"                                                  ->  Hash  (cost=62.89..62.89 rows=2889 width=19)"
"                                                        ->  Seq Scan on student_status ss  (cost=0.00..62.89 rows=2889 width=19)"
"                                            ->  Seq Scan on code  (cost=0.00..5.98 rows=1 width=16)"
"                                                  Filter: (((public.code.codegroup)::text = 'lesson_types'::text) AND ((public.code.code)::bpchar = '1'::bpchar))"
"                                      ->  Seq Scan on schoolyear lsy  (cost=0.00..1.13 rows=13 width=30)"
"                                ->  Nested Loop Left Join  (cost=0.00..12.47 rows=1 width=40)"
"                                      Join Filter: (s.academy_id = a.id)"
"                                      ->  Nested Loop Left Join  (cost=0.00..11.45 rows=1 width=43)"
"                                            Join Filter: (m.department_id = d.id)"
"                                            ->  Nested Loop Left Join  (cost=0.00..10.36 rows=1 width=47)"
"                                                  Join Filter: (m.subacademy_id = s.id)"
"                                                  ->  Nested Loop Left Join  (cost=0.00..9.20 rows=1 width=48)"
"                                                        Join Filter: (m.working_id = w.id)"
"                                                        ->  Nested Loop Left Join  (cost=0.00..8.13 rows=1 width=50)"
"                                                              Join Filter: (m.grade_id = g.id)"
"                                                              ->  Nested Loop Left Join  (cost=0.00..6.80 rows=1 width=54)"
"                                                                    Join Filter: (m.duty_id = dt.id)"
"                                                                    ->  Nested Loop Left Join  (cost=0.00..5.66 rows=1 width=46)"
"                                                                          Join Filter: (t.member_id = m.id)"
"                                                                          ->  Seq Scan on teacher t  (cost=0.00..2.50 rows=1 width=18)"
"                                                                                Filter: ((member_id = 'SH000092'::bpchar) AND (subject_id = 'ENG'::bpchar))"
"                                                                          ->  Seq Scan on member m  (cost=0.00..3.15 rows=1 width=37)"
"                                                                                Filter: (m.id = 'SH000092'::bpchar)"
"                                                                    ->  Seq Scan on duty dt  (cost=0.00..1.06 rows=6 width=12)"
"                                                              ->  Seq Scan on grade g  (cost=0.00..1.15 rows=15 width=4)"
"                                                        ->  Seq Scan on working w  (cost=0.00..1.03 rows=3 width=2)"
"                                                  ->  Seq Scan on subacademy s  (cost=0.00..1.07 rows=7 width=7)"
"                                            ->  Seq Scan on department d  (cost=0.00..1.04 rows=4 width=4)"
"                                      ->  Seq Scan on academy a  (cost=0.00..1.01 rows=1 width=3)"
"                          ->  Seq Scan on subject s  (cost=0.00..1.06 rows=1 width=11)"
"                                Filter: (s.id = 'ENG'::bpchar)"
"                    ->  Seq Scan on subject s  (cost=0.00..1.06 rows=1 width=8)"
"                          Filter: (s.id = 'ENG'::bpchar)"
"              ->  Index Scan using timetable_code_pkey on timetable_code ttc  (cost=0.00..8.27 rows=1 width=29)"
"                    Index Cond: (tt.id = ttc.timetable_id)"
"        ->  Seq Scan on code  (cost=0.00..5.49 rows=41 width=16)"
"              Filter: ((public.code.codegroup)::text = 'stop_reason'::text)"
 

iyob님이 2013-08-05 19:27에 작성한 댓글입니다. Edit Ｘ

실행 계획이 눈에 잘 안들어와서 뭐라 말을 못하겠지만,

어떤 특정 자료를 접근할 때만 늦다면, 

그 특정 자료쪽에 자료량이 많아서 인덱스를 사용하지 못하는 경우에 그렇게 됩니다. 

처리가 빨리 되는 놈과, 

늦게 되는 놈의 실행 결과를 비교해서, 

늦게 처리 되는 경우를 피해 갈 수 있도록 쿼리를 다시 만들어봐야겠네요.

 

김상기(ioseph)님이 2013-08-06 09:31에 작성한 댓글입니다.

항상 친절한 답변 주셔서 감사합니다.

일단, 문제의 부분을 회피하는 방법으로 수정을 했습니다.

쿼리에서는 필요한 조건인데 걸지 않고 프로그램상에서 처리하도록 바꿨습니다.

말씀하신대로 기존 뷰에서 문제가 생기는 경우는 확실한데 말씀하신대로 뷰생성 쿼리를 조사해보는 수 밖에 없을 것 같네요.

인덱스 적용여부를 검토해 보겠습니다. 감사합니다.

iyob님이 2013-08-06 14:06에 작성한 댓글입니다. Edit Ｘ
[Top]
No.
제목
작성자
작성일
조회
9325pgsql에서 rownum 후 group by order by 처리는? [1]
pgadmin
2013-08-07
10295
9324pgFouine로 쿼리를 보니 #015#011#011 문자가 들어 있는데 없애는 방법 좀 가르쳐 주세요. [2]
iyob
2013-08-06
9486
9323무설치 배포.. [2]
CS.Shin
2013-08-05
9955
9322특정한 회원만 쿼리가 느려지는데... [5]
iyob
2013-07-31
9623
9321update 구문 질문. [2]
pranludi
2013-07-29
9680
9320tablespace가 disk full일 때... [1]
DB랑
2013-07-25
9681
9319bytea형식은 어떻게 Select해야되나요. [1]
이기자
2013-07-24
9290
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.033초, 이곳 서비스는 PostgreSQL v16.2로 자료를 관리합니다