EXPLAIN ANALYZE를 사용해서 쿼리의 실제 실행 시간 측정하기

EXPLAIN 명령어가 마치 크리스탈볼처럼 PostgreSQL이 쿼리를 “어떻게” 실행할지 보여준다면, EXPLAIN ANALYZE는 진짜 탐정처럼 실제로 무슨 일이 일어났는지 파헤쳐 주는 거야.

EXPLAIN과 EXPLAIN ANALYZE의 주요 차이점:

EXPLAIN – 이건 이론이야. PostgreSQL이 쿼리를 어떻게 실행할지 계획을 보여줘. 예상되는 값들, 예를 들면 행의 개수(rows)나 실행 비용(cost) 같은 걸 볼 수 있어.

EXPLAIN ANALYZE – 이건 실전이지. PostgreSQL이 실제로 쿼리를 실행하고 다음을 보여줘:

• 각 단계에서 실제로 처리된 행의 수.
• 각 연산의 실제 실행 시간.
• 플랜의 예상치(rows와 cost)와의 비교.

예시: 네 쿼리가 100개의 행을 처리할 거라고 예상했는데, 실제로는 10,000개의 행을 처리했다면, EXPLAIN ANALYZE가 바로 이 불편한 진실을 까발려줄 거야!

기본 문법과 사용법

EXPLAIN처럼 EXPLAIN ANALYZE도 사용법이 엄청 쉬워. 그냥 EXPLAIN 명령어에 ANALYZE만 붙이면 돼.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

PostgreSQL이 하는 일:

• 쿼리를 실제로 실행해.
• 실행 플랜의 각 연산에 실제 수치를 기록해.
• 쿼리 실행 과정을 전부 설명해줘.

EXPLAIN ANALYZE가 제공하는 데이터는?

연산의 실제 실행 시간:

• Actual Start Time: 연산이 시작된 시점.
• Actual End Time: 연산이 끝난 시점.

총 처리된 행의 수:

이걸로 플랜의 예상치(rows)가 얼마나 정확한지 알 수 있어.

버퍼 정보:

디스크와 메모리 버퍼가 어떻게 사용됐는지 보여줘.

EXPLAIN ANALYZE 사용 예시

구체적인 예시를 보자. students라는 테이블이 있고, 학생 데이터가 들어있어:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

20살 넘는 학생을 뽑는 쿼리를 실행해보자:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

결과 예시:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

결과 해석:

• Seq Scan – PostgreSQL이 테이블을 순차적으로 스캔한다는 뜻이야.
• cost=0.00..14.00 – 이건 연산의 예상 비용이야.
• rows=2 – PostgreSQL이 쿼리가 2개의 행을 반환할 거라고 예상했어(그리고 맞았지!).
• actual time=0.025..0.026 – 연산의 실제 실행 시간(밀리초 단위).
• Rows Removed by Filter: 2 – 조건 WHERE에 맞지 않아서 2개의 행이 필터링됐어.

이론과 실제 비교

EXPLAIN ANALYZE의 마법은 바로 이거야: 쿼리가 실제로 어떻게 실행됐는지 보여주고, 이걸 이론적인 실행 플랜과 비교할 수 있게 해줘.

좀 더 복잡한 예시를 보자.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

결과 예시:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

뭘 볼 수 있냐면:

1. PostgreSQL이 쿼리를 0.057밀리초 만에 실행했어.
2. 조건 WHERE에 맞는 행은 딱 하나(rows=1)야.
3. 세 개의 행이 필터링됐어(Rows Removed by Filter: 3).

요약

EXPLAIN ANALYZE를 쓰면 병목 현상을 찾고 쿼리를 어떻게 최적화할지 알 수 있어. 예를 들어:

• Seq Scan이 너무 "무겁다" 싶으면, 인덱스를 추가할 때가 된 거야.
• PostgreSQL의 예상치가 실제 데이터랑 많이 다르면, 테이블 통계(ANALYZE)나 인덱스 구조를 점검해봐야 해.