쿼리 성능 튜닝

[hunjin-choi]

모든 카뎃의 최근 현황을 조회할때
user테이블에 다른 16개의 테이블을 join 시켜서 결과 테이블을 얻고
이 결과 테이블에서 where절을 써서 deleted_date = NULL 인 로우들만 가져오는 쿼리를 작성.

데이터 양이 작을때는 위 쿼리가 잘 작동했지만, 데이터 양이 많아지니까 작동이 제대로 안됨.

[hunjin-choi]

원인

1. 실행계획이 아래처럼 나오는데, left join 할 때 hash 알고리즘을 사용.
   hash 알고리즘은 메모리를 많이 쓰는데, 테이블의 데이터 양이 많으면 hash 알고리즘은 더 많은 메모리를 사용할 것.
   실제 사용하는 스프레드 시트에는 데이터가 상당히 많이 들어 있으니 hash 알고리즘 사용할 때 메모리가 과도하게 사용될 것.
   그래서 결국 temp 탈락이 발생하고 그래서 I/O 속도가 급격하게 느려지니까 결국 쿼리 속도도 심각하게 느려지는 문제 발생.
2. deleted_date 때문일수도...?? -> 이거때문에 Seq Scan 너무 많이 나옴 거기다가 hash도 쓰고 있음

분석

1. 일단 left join을 16번 하기는 해야함.
   하지만 위에서 처럼 하나의 쿼리에 left join 16번을 써버리면 temp탈락 문제 발생.
2. 한번에 피크를 너무 올려서 temp 탈락 문제 발생.

해결

방안1. 여러개의 쿼리로 나눠서 left join을 실행하면 될 듯.
방안2. lazy로딩써서 피크를 서서히 높이는 방법.

의문

1. 쿼리의 where delete_dated = NULL 의 실행계획을 예상해보면
   그냥 Seq Scan만 하면 되는거 같은데, 그 상위에서 hash는 왜 쓰는건지??
   -> hash해서 정렬한 다음에 delete_dated = NULL 을 찾는게 더 효율적이라고 판단해서??
2. 쿼리 여러개로 실행하면 여러개의 테이블이 생성되는데, 프론트에서 이 여러개의 테이블들을 잘 조합해서 표를 그려내야함.
   가능한지 물어보기

맨 아래 복붙된 실행계획은 가독성이 안좋은데, 바로 아래 csv파일로 보는게 더 좋습니다.

sql실행계획.csv

QUERY PLAN

Hash Left Join  (cost=222.04..258.89 rows=400 width=2536) (actual time=0.019..0.024 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userLapiscineInformation".fk_user_no)
->  Hash Left Join  (cost=207.31..242.64 rows=400 width=2392) (actual time=0.018..0.023 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userOtherInformation".fk_user_no)
->  Hash Left Join  (cost=192.39..226.20 rows=400 width=2256) (actual time=0.018..0.023 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userAccessCardInformation".fk_user_no)
->  Hash Left Join  (cost=178.86..211.15 rows=400 width=2056) (actual time=0.018..0.022 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userComputationFund".fk_user_no)
->  Hash Left Join  (cost=164.84..195.61 rows=400 width=1884) (actual time=0.018..0.022 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userOtherEmploymentStatus".fk_user_no)
->  Hash Left Join  (cost=147.29..176.52 rows=400 width=1804) (actual time=0.017..0.021 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userHrdNetUtilize".fk_user_no)
->  Hash Left Join  (cost=133.46..161.17 rows=400 width=1620) (actual time=0.017..0.021 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userHrdNetUtilizeConsent".fk_user_no)
->  Hash Left Join  (cost=115.91..142.08 rows=400 width=1540) (actual time=0.017..0.020 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userEmploymentStatus".fk_user_no)
->  Hash Left Join  (cost=100.08..124.71 rows=400 width=1428) (actual time=0.017..0.019 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userLoyaltyManagement".fk_user_no)
->  Hash Left Join  (cost=85.15..108.27 rows=400 width=1292) (actual time=0.016..0.019 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userLearningDataAPI".fk_user_no)
->  Hash Left Join  (cost=69.61..91.20 rows=400 width=1172) (actual time=0.016..0.018 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userInterruptionOfCourse".fk_user_no)
->  Hash Left Join  (cost=54.89..74.96 rows=400 width=1028) (actual time=0.016..0.018 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userBlackhole".fk_user_no)
->  Hash Left Join  (cost=40.36..58.91 rows=400 width=876) (actual time=0.016..0.017 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userLeaveOfAbsence".fk_user_no)
->  Hash Left Join  (cost=26.64..43.67 rows=400 width=684) (actual time=0.015..0.017 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userCourseExtension".fk_user_no)
->  Hash Left Join  (cost=12.11..27.62 rows=400 width=532) (actual time=0.015..0.016 rows=0 loops=1)
Hash Cond: ("User".intra_no = "User__User_userPersonalInformation".fk_user_no)
->  Seq Scan on "user" "User"  (cost=0.00..14.00 rows=400 width=172) (actual time=0.015..0.015 rows=0 loops=1)
->  Hash  (cost=12.10..12.10 rows=1 width=360) (never executed)
->  Seq Scan on user_personal_information "User__User_userPersonalInformation"  (cost=0.00..12.10 rows=1 width=360) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=14.50..14.50 rows=2 width=152) (never executed)
->  Seq Scan on user_course_extension "User__User_userCourseExtension"  (cost=0.00..14.50 rows=2 width=152) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=13.70..13.70 rows=2 width=192) (never executed)
->  Seq Scan on user_leave_of_absence "User__User_userLeaveOfAbsence"  (cost=0.00..13.70 rows=2 width=192) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=14.50..14.50 rows=2 width=152) (never executed)
->  Seq Scan on user_blackhole "User__User_userBlackhole"  (cost=0.00..14.50 rows=2 width=152) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=14.70..14.70 rows=2 width=144) (never executed)
->  Seq Scan on user_interruption_of_course "User__User_userInterruptionOfCourse"  (cost=0.00..14.70 rows=2 width=144) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=15.50..15.50 rows=3 width=120) (never executed)
->  Seq Scan on user_learning_data_api "User__User_userLearningDataAPI"  (cost=0.00..15.50 rows=3 width=120) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=14.90..14.90 rows=2 width=136) (never executed)
->  Seq Scan on user_loyalty_management "User__User_userLoyaltyManagement"  (cost=0.00..14.90 rows=2 width=136) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=15.80..15.80 rows=3 width=112) (never executed)
->  Seq Scan on user_employment_status "User__User_userEmploymentStatus"  (cost=0.00..15.80 rows=3 width=112) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=17.50..17.50 rows=4 width=80) (never executed)
->  Seq Scan on user_hrd_net_utilize_consent "User__User_userHrdNetUtilizeConsent"  (cost=0.00..17.50 rows=4 width=80) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=13.80..13.80 rows=2 width=184) (never executed)
->  Seq Scan on user_hrd_net_utilize "User__User_userHrdNetUtilize"  (cost=0.00..13.80 rows=2 width=184) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=17.50..17.50 rows=4 width=80) (never executed)
->  Seq Scan on user_other_employment_status "User__User_userOtherEmploymentStatus"  (cost=0.00..17.50 rows=4 width=80) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=14.00..14.00 rows=2 width=172) (never executed)
->  Seq Scan on user_computation_fund "User__User_userComputationFund"  (cost=0.00..14.00 rows=2 width=172) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=13.50..13.50 rows=2 width=200) (never executed)
->  Seq Scan on user_access_card_information "User__User_userAccessCardInformation"  (cost=0.00..13.50 rows=2 width=200) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=14.90..14.90 rows=2 width=136) (never executed)
->  Seq Scan on user_other_information "User__User_userOtherInformation"  (cost=0.00..14.90 rows=2 width=136) (never executed)
Filter: (deleted_date IS NULL)
->  Hash  (cost=14.70..14.70 rows=2 width=144) (never executed)
->  Seq Scan on user_lapiscine_information "User__User_userLapiscineInformation"  (cost=0.00..14.70 rows=2 width=144) (never executed)
Filter: (deleted_date IS NULL)
Planning:
Buffers: shared hit=16
Planning Time: 8.879 ms
Execution Time: 0.708 ms

[hunjin-choi]

lazy로딩 써서 피크를 한번에 올리지 않는다.
역정규화
테이블 조인시 해쉬알고리즘 사용하면 두 테이블 각각에 대해 해쉬맵을 만드는지? 아니면 한쪽 테이블만 선택해서 해쉬맵을 만드는지?
fk 인덱싱
실험: 두 테이블을 join 할때 (fk를 가지는 테이블쪽의) fk컬럼에 index를 주고 안주고의 차이가 join의 실행계획에 영향을 미치는지?
결과:
통계정보 업데이트 주기적으로
불필요한 where 절 (Seq Scan을 불필요하게 많이 씀)
레디스 써서 캐싱하기 -> 캐싱하고 안하고가 문제가 아님

주팍님이 알려주신거
select
from
where
에서 원래 실행순서는 from -> where -> select 인데
where에 들어가는 컬럼이 어떤것이냐에 따라 from에서 join알고리즘에 영향을 줌

lazy로딩을 쓰는 본래 목적이 피크를 서서히 높이기 위함인지? -> 맞는듯. (총량/시간)인데 총량은 동일. 시간을 늘리는 거.