검색 쿼리 성능 향상을 위한 팁부탁드립니다. 0 24 2,017

by playlyun [SQL Query] query [2017.09.13 15:47:47]


현재 고유아이디와 시간값을 PK 로 하여, 해당 ID의 장비의 상태 데이터를 저장하는 테이블이 있습니다.

 

사용 프로그램 설정에 따라 해당 테이블에서 A장비의 11:30:01~11:31:32, 12:12:31 ~ 12:13:59 이런 시간간격범위로 쿼리를 해와야합니다.

시간 범위 조건은 하나가 될 수도 있고, 여러개가 될수도 있어서 현재 프로그램상에서 WHERE절에 시간범위 or 시간범위 ... 이런식으로 or로 조건을 붙여서 쿼리를 합니다.

 

이렇게 하니 쿼리가 엄청 느리네요. 분석을 하면 PK INDEX SCAN을 한다고는 표시가 되는데, 향상시킬 방법이 없을까요? ㅠ.ㅠ

by playlyun [2017.09.13 15:49:50]

쿼리분석내용을 인덱스 스캔에 걸려있는데 ID만 있고 시간 속성은 안써있네요.

PK를 아이디+시간으로 해놨는데 PK INDEX는 탄다고 표시되어있지만내용에 시간은 고려안하네요. 쿼리때문인가요? 아무튼 조언기다립니다..


by jkson [2017.09.13 16:07:08]

pk가 아이디 + 시간인데

조건에는 아이디 없이 시간만 있는 거예요?

전체 쿼리랑 실행계획 올려보세요.


by playlyun [2017.09.13 16:32:26]

"Index Scan using "PK_HIS_WORKING_DATA" on his_working_data w  (cost=0.00..454258.47 rows=1 width=16) (actual time=310077.684..310077.684 rows=0 loops=1)"
"  Index Cond: ((booth_id = 3) AND (zone_id = 3) AND (robot_id = 9))"
"  Filter: ((factory_id = 202) AND (color = 16) AND (((time_stamp >= to_timestamp('2017-07-05 13:31:24.981'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) AND (time_stamp <= to_timestamp('2017-07-05 13:31:56.7'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text))) OR ((time_stamp >= to_timestamp('2017-07-05 13:29:24.231'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) AND (time_stamp <= to_timestamp('2017-07-05 13:30:35.653'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text))) OR ((time_stamp >= to_timestamp('2017-07-05 13:25:23.59'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) AND (time_stamp <= to_timestamp('2017-07-05 13:26:35.247'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text))) OR ((time_stamp >= 
..."  
Rows Removed by Filter: 4872347"
"Total runtime: 310079.625 ms"
 


by playlyun [2017.09.13 16:32:41]

너무 길어서 안올라가네요 ㅠ


by playlyun [2017.09.13 16:33:08]

select *
FROM his_working_data as w 
WHERE w.factory_id = 202 and w.booth_id = 3 and w.zone_id = 3 and w.robot_id = 9 and  ( w.time_stamp BETWEEN to_timestamp('2017-07-05 13:31:24.981','YYYY-MM-DD HH24:MI:SS.MS') AND to_timestamp('2017-07-05 13:31:56.7','YYYY-MM-DD HH24:MI:SS.MS') OR w.time_stamp BETWEEN to_timestamp('2017-07-05 13:29:24.231','YYYY-MM-DD HH24:MI:SS.MS') AND to_timestamp('2017-07-05 13:30:35.653','YYYY-MM-DD HH24:MI:SS.MS') OR....
 


by jkson [2017.09.13 16:50:00]

사용하시는 DB 종류가??


by playlyun [2017.09.13 17:22:10]

postgresql입니다


by 마농 [2017.09.13 16:52:08]

1. 인덱스 구성 항목들을 나열해 주세요.
2. 오라클이 아닌듯 하네요? to-timestamp 에서 '.MS' 부분이 이상하네요? '.FF' 해야 하는 것 아닌지?
3. 다음 조건을 추가해 보세요. AND w.time_stamp BETWEEN 제일작은시작시간 AND 제일큰종료시간


by playlyun [2017.09.13 17:23:52]

booth_id, zone_id, robot_id, time_stamp 가 PK 이고요.

robot_id,time_stamp 만 따로 인덱스 하나 해놨고

time_stamp만 따로 인덱스설정 해놨는데 맨위에 PK INDEX를 time_stamp없이 타네요.

3번은 테스트 해볼게요


by playlyun [2017.09.13 17:29:29]

시간 조건 or 시간조건 or 시간조건 부분을

가장 작은시간의 조건or 두번째 작은시간의 조건 or ... 하니까 time_stamp까지 인덱스 타네요;;


by playlyun [2017.09.13 17:39:12]

일자(日)가 바뀌니까 안타네요, 시간순은 상관이 없는걸까.. 더 테스트갑니다..


by 마농 [2017.09.13 17:05:56]

엄청 긴 댓글들 짧게 수정해 주시면 안될까요?
여러번 중복 올린 것들 삭제해 주시구요.
긴 댓글들로 인해 다른 댓글 확인이 힘드네요.


by playlyun [2017.09.13 17:22:01]

네 수정하였습니다 ㅜ


by jkson [2017.09.13 17:22:49]

쿼리가 너무 길어요.

아마도 바인드변수로 처리하는 게 아니고 for 문 돌리면서 리터럴로 쿼리 만드는 게 아닌가 싶은데..

이렇게 쿼리를 만드시면 파싱에서 조차 문제가 있겠네요.

어떤 용도의 쿼리인지요? 저 시간대가 어떤 의미를 가지는 것인지?


by playlyun [2017.09.13 17:28:28]

특정 작업을 한 시간대의 상태를 볼려는 쿼리이고요 for문돌리면서 or 시간조건 계속 붙인 쿼리에요 


by 마농 [2017.09.13 17:42:16]

특정 작업을 한 시간대가 저장된 테이블이 따로 있을 것 같네요.
쿼리를 스트링으로 만들지 마시고 조인형태로 풀어보세요.
 

SELECT *
  FROM his_working_data AS w
 INNER JOIN xxx AS x -- 특정 작업을 한 시간대가 저장된 테이블
    ON w.time_stamp BETWEEN x.s_time_stamp AND x.e_teim_stamp
 WHERE w.factory_id = 202
   AND w.booth_id   = 3
   AND w.zone_id    = 3
   AND w.robot_id   = 9
   AND w.color      = 16
   AND x.zzz = 111    -- 특정 작업 추출 조건
;

 


by playlyun [2017.09.13 17:45:43]

제가 생각못한 방법을 항상 제시해주셔서 감사합니다..

조인조건에 시간범위가 되나보군요;; 다시 해볼게요!


by playlyun [2017.09.14 09:00:25]

작업조건으로 시간범위 구하는 쿼리를 사전에 따로 보내는게 있었는데 도움되었어요 '-';;

 

성능도 일단 time_stamp를 고려한 인덱스를 타니 기대해보고 지켜보려합니당.


by jkson [2017.09.13 17:54:20]

저 시간대 조건이 계속 변동 되어서 테이블로 관리하기 어렵고(혹은 테이블로 관리가 안 되고 있다든지)

파싱부하가 너무 심하다면

시간대를 10개 정도씩(성능이 괜찮다면 더 큰 크기로..) 가지고 오는 쿼리를 만드시고

리터럴로 처리할 게 아니라 바인드변수로 만드세요.

select ~

from table

where (시간대1바인드변수처리 or 시간대2바인드변수처리 or .. 시간대10바인드변수처리)

and 시간대최소~최대탐색조건

프로그램단에서 10개의 조건씩 끊어서 쿼리를 날리시고 결과 데이터셋에 계속 append해주시는 게 좋을 것 같습니다.


by playlyun [2017.09.13 18:16:55]

바인드변수개념이 없어서 공부좀해봐야겠네요 ㅎ흐ㅜㅜㅜㅜ


by jkson [2017.09.13 18:32:37]

바인드변수는 쿼리 자체에 값을 바로 적는 게 아니라 :dt와 같이 변수에 담는 방법이예요.

바인드변수는 오라클에서는 :dt 와 같이 : 기호 사용하구요.

바인드변수를 사용하지 않으면 쿼리의 실행계획을 그때그때 새로 만들어야하기 때문에

되도록 바인드변수를 사용하셔야해요.

더군다나 쿼리 길이가 엄청 길어지면 파싱 시간이 너무 오래 걸리기 때문에 조금씩 끊어서

쿼리 실행하라는 말씀을 드린 거예요.


by jkson [2017.09.13 18:03:30]

그런데 저렇게 or 조건이 많이 걸리면 filter 조건이 되는 건

수직 탐색 비용이 더 크다고 판단해서 저렇게 탐색하는 걸까요?

공부했는데 까먹은 건가;;

오라클에서도 비슷한 상황 만들어서 실행해보니 or 조건쪽은 다 filter 처리네요.

or 조건이 시간이든 문자든 다 filter.. 음..


by playlyun [2017.09.13 18:16:36]

or 조건부가 많아지면 무시하는거 같기도하고 잘모르겠네요 ㅠㅠ


by jkson [2017.09.13 18:28:26]

아.. 생각해보니 id 별 시간대가 많지 않을 것 같네요..

id별로 시간대가 아주 많을 경우에는 index로 access하는 게 효율적이지만 id별로 시간대 종류가 아주 작다면 수직탐색보다

그냥 filter하는 게 낫다고 옵티마이져가 판단한 것 같아 보이네요.

만약 id별로 시간대가 아주 많은데 filter 처리되었다면 통계정보가 실제와 다르거나..

------------------------------------------------------------------------------

실제로 10만 건 데이터를 생성하고 id를 제 각각 다르게 했을 때는 filter,

전부 동일하게 했을 때는 access 되네요.

------------------------------------------------------------------------------

시간대 or 조건을 5건만 해본다

-> 여전히 filter이지만 빨라졌다 -> 쿼리 길이 문제

-> 여전히 느리다 -> 통계정보 안 맞음

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입