EXPLAIN이 뭐고, 이걸로 쿼리 분석하는 방법

예를 들어서, 너가 앱을 개발하고 있는데 어느 날 갑자기 어떤 쿼리가 DB에서 제일 무거워졌다고 해보자. 앱이 느려지고 에러도 나기 시작해. 이럴 때 EXPLAIN이 등장해서, 어디서 꼬였는지 파악할 수 있게 도와줘. EXPLAIN 분석을 기반으로 쿼리를 최적화하면 리소스도 아끼고, 시간도 절약하고, 사용자 경험도 훨씬 좋아질 수 있어.

EXPLAIN은 PostgreSQL 내부를 들여다보는 창문 같은 거야. DB가 쿼리를 어떻게 실행할지 보여주거든. 인덱스를 쓸지, 아니면 테이블 전체를 다 뒤질지, 옵티마이저가 어떤 단계를 거치는지, 순서가 어떻게 되는지, 중간 결과가 얼마나 큰지도 다 볼 수 있어.

다시 말해서, EXPLAIN은 쿼리 실행 결과를 미리 예측하게 해줘. 얼마나 무거운 쿼리인지, 몇 줄을 처리할지, 어떤 리소스가 쓰일지 알 수 있지. 갑자기 쿼리가 느려지면, 왜 그런지 파악할 때 필수 도구야.

EXPLAIN은 어두운 곳에서 비추는 손전등 같아. 덕분에 내부에서 무슨 일이 일어나는지, 어디서 꼬였는지 딱 볼 수 있지.

EXPLAIN 명령어 문법

기본 EXPLAIN 문법부터 보자:

EXPLAIN 너의_SQL_쿼리;

예시 쿼리:

EXPLAIN SELECT * FROM students WHERE age > 20;

이 명령어를 실행하면, PostgreSQL은 쿼리를 실제로 실행하지 않아. 대신 실행 계획만 보여줘. 건물 짓기 전에 설계도 보는 거랑 비슷해. 뭘 할지 미리 보는 거지, 실수로 뭔가 망가뜨리기 전에.

출력 예시:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

이 출력이 좀 무서울 수도 있는데, 걱정 마! 이제 주요 구성 요소를 하나씩 알아보자.

기본 실행 계획 뜯어보기

아까 그 결과를 하나씩 보자:

1. Seq Scan on students — PostgreSQL이 students 테이블 전체를 순차적으로 스캔한다는 뜻이야. (순차 스캔) 이게 항상 나쁜 건 아닌데, 테이블이 크면 Seq Scan은 느릴 수 있어.

2. cost=0.00..35.00 — 이건 작업 실행에 드는 비용 추정치야:

   • Startup Cost: 작업 시작할 때 드는 비용 (여기선 0.00).
   • Total Cost: 작업 끝까지 드는 총 비용 (여기선 35.00).

3. rows=7 — PostgreSQL이 age > 20 조건으로 7줄이 나올 거라고 예상한 거야. 이걸 "카디널리티(cardinality)"라고 해. 만약 이상한 숫자가 나오면, 테이블 통계가 오래됐다는 신호일 수도 있어.

4. width=37 — 한 줄의 평균 크기(바이트 단위).

5. Filter: (age > 20) — PostgreSQL이 각 줄마다 이 조건을 체크한다는 뜻이야.

이렇게 EXPLAIN 출력은 PostgreSQL이 어떤 전략과 가정을 쓰는지 보여줘. 이걸 바탕으로 쿼리를 최적화할 수 있지.

EXPLAIN 명령어 옵션

기본 EXPLAIN만으로도 쓸만하지만, 아래 옵션들을 추가하면 더 다양한 정보를 볼 수 있어:

ANALYZE

이 옵션을 쓰면 PostgreSQL이 실행 계획만 보여주는 게 아니라, 실제로 쿼리도 실행해서 실제 데이터를 보여줘. 예시:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

이렇게 하면 PostgreSQL의 예상치와 실제 실행 결과를 비교할 수 있어서, 얼마나 정확한지 알 수 있어.

VERBOSE

더 자세한 정보를 보여줘. 깊게 분석할 때 유용해. 예시:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

쿼리 실행할 때 메모리 버퍼 사용량을 보여줘. ANALYZE랑 같이 써야 해:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

비용(cost) 정보를 숨기거나 보여주고 싶을 때 써. 예시:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

실행 계획을 JSON이나 XML 같은 다른 포맷으로도 볼 수 있어. 예시:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

EXPLAIN 사용 예시

university라는 데이터베이스에 students 테이블이 있다고 해보자. 20살 넘은 학생을 찾고 싶으면:

EXPLAIN SELECT * FROM students WHERE age > 20;

출력 예시:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

아까 말했듯이, 이건 Seq Scan이라서 테이블이 크면 비효율적일 수 있어.

이제 age 컬럼에 인덱스를 만들어서 실행 계획이 바뀌는지 보자:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

출력:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

이제 PostgreSQL이 인덱스 스캔(Index Scan)을 써. 보통 테이블이 크면 이게 훨씬 빨라.

자주 묻는 질문과 실수

인덱스가 있는데도 쿼리가 느린 이유는?

쿼리가 너무 많은 줄을 반환하면, 인덱스를 써도 별로 이득이 없을 수 있어. 인덱스가 오래됐거나, 품질이 안 좋을 수도 있고.

EXPLAIN 출력이 너무 어려우면?

간단한 쿼리부터 시작해서, 실행 계획의 각 노드를 하나씩 공부해봐.

테이블 통계가 오래됐는지 어떻게 알아?

ANALYZE students 명령어를 실행해봐.

ANALYZE 없이 EXPLAIN만 쓸 땐 언제야?

실제로 실행하지 않고 계획만 보고 싶을 때 (예를 들어, 데이터를 바꾸는 쿼리라면).