postgreSQL EXPLAIN ANALYZE

EXPLAIN

• SQL 쿼리가 어떻게 실행될지 보여주는 실행 계획 분석 도구.
• 실제로 쿼리를 실행하지는 않고 옵티마이저가 만든 계획만 보여준다.

-- 기본 사용법
EXPLAIN SELECT * FROM player WHERE id = 1;

-- 내 db 예시: 캐릭터 테이블과 users 테이블 조인
EXPLAIN
SELECT users.user_id, users.name AS user_name, nickname AS character_name, level, job_type
FROM characters
LEFT JOIN auth.users ON characters.user_id = auth.users.user_id
ORDER BY auth.users.user_id DESC;

이런식으로 실행계획과 예상결과를 보여준다.

실행계획은 가장 안쪽부터 읽으면 된다. (들여쓰기가 가장 긴곳)

내 쿼리 플랜을 해석해보자.

1. 캐릭터 테이블을 풀스캔(Scan) 하고 조인을 위해 데이터를 해시 테이블에 저장했다.(Hash)
2. users 테이블을 풀스캔(Scan) 하고 메모리에 만들어둔 캐릭터 해시 테이블과 users 레코드를 대조하여(Hash Cond) 조인함 (Hash Right Join) - LEFT JOIN을 시켰는데 RIGHT JOIN을 하는 이유는 옵티마이저가 효율을 위해 알아서 바꿔버린 것이다.
3. 최종 정렬 (Sort)

비용과 데이터 예측치 분석

 

 

EXPLAIN  ANALYZE

• 실제 데이터 읽음
• 실제 시간 측정
• 실제 row 수 계산
• 실행 시간 출력

-- 기본 사용법
EXPLAIN ANALYZE SELECT * FROM player WHERE id = 1;

-- 내 db 예시: 캐릭터 테이블과 users 테이블 조인
EXPLAIN ANALYZE
SELECT users.user_id, users.name AS user_name, nickname AS character_name, level, job_type
FROM characters
LEFT JOIN auth.users ON characters.user_id = auth.users.user_id
ORDER BY auth.users.user_id DESC;

계획을 짜는데는 0.952ms 계획을 실행하는데는 0.522ms가 걸렸다.

지금은 데이터가 적어서 실행시간이 매우 짧다.

 

쿼리가 느리면 EXPLAIN ANALYZE를 붙여서 돌리고, seq Scan과 같이 인덱스를 사용하지 않고 풀스캔 하는 부분에 인덱스를 사용하는걸 고려한다거나 actual time이 큰 부분이 있다면 왜그런지 확인해본다던지 해서 문제 해결에 도움을 받을 수 있다.