함수와 프로시저 성능 분석: EXPLAIN ANALYZE 사용법

EXPLAIN ANALYZE는 PostgreSQL이 네 쿼리를 실행할 때 "어떻게 생각하는지" 이해하는 데 도움을 줘:

• 데이터를 처리하기 위해 어떤 단계들이 실행되는지.
• 각 단계가 얼마나 걸리는지.
• 왜 어떤 쿼리가 느린지 — 예를 들어 전체 테이블 스캔(Seq Scan)이거나 인덱스를 안 쓰는 경우 등.

EXPLAIN ANALYZE 명령은 실제로 쿼리를 실행하고 PostgreSQL이 실행을 어떻게 최적화하는지 보여줘. 시계 내부를 분해해서 어떻게 작동하는지 보는 거랑 비슷해. EXPLAIN ANALYZE는 네 SQL 쿼리로 그걸 해주는 거지.

EXPLAIN ANALYZE 문법

일단 간단하게 시작해보자. 기본 명령은 이렇게 생겼어:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

이 쿼리는 SELECT 명령을 실행하고 PostgreSQL이 데이터를 어떻게 처리하는지 보여줄 거야.

EXPLAIN ANALYZE 결과는 쿼리 실행 트리로 나와. 트리의 각 단계는 PostgreSQL이 실행하는 단계를 설명해:

• Operation Type — 작업 타입(예: Seq Scan, Index Scan 등).
• Cost — PostgreSQL이 이 작업을 얼마나 "비싸다"고 생각하는지.
• Rows — 예상되는 행 수와 실제로 나온 행 수.
• Time — 작업에 걸린 시간.

출력 예시:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Seq Scan on students에 주목해봐. 이건 PostgreSQL이 students 테이블의 모든 행을 다 훑는다는 뜻이야. 테이블이 크면 이거 진짜 느릴 수 있어.

EXPLAIN ANALYZE 사용 예시

실전 예시 몇 개를 같이 보면서 쿼리 문제를 찾고 고치는 방법을 배워보자.

예시 1: 전체 테이블 스캔

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

출력:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

여기서 문제는 PostgreSQL이 Seq Scan을 한다는 거야. 즉, 테이블의 모든 행을 다 훑는다는 뜻이지. 만약 행이 수백만 개면 이게 병목이 될 수 있어.

해결책: age 컬럼에 인덱스를 만들어보자.

CREATE INDEX idx_students_age ON students(age);

이제 같은 쿼리를 다시 실행해봐:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

출력:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

이제 Index Scan이 나왔지? Seq Scan 대신! 오예, 쿼리 속도 엄청 빨라졌어!

예시 2: JOIN이 있는 복잡한 쿼리

예를 들어 students랑 courses 두 테이블이 있다고 해보자. 학생 이름이랑 수강하는 과목 이름을 알고 싶어.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

출력은 대략 이렇게 나올 수 있어:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

보다시피 PostgreSQL이 인덱스를 잘 쓰고 있어서 실행이 빠르지. 만약 인덱스가 없으면 Seq Scan이 나와서 느려질 수 있어.

함수 성능 최적화

이번엔 특정 나이보다 많은 학생 목록을 반환하는 함수가 있다고 해보자:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

EXPLAIN ANALYZE로 이 함수의 성능도 분석할 수 있어:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

함수 실행 속도 높이기

함수 실행이 느리면, 아마도 전체 테이블 스캔이 문제일 수 있어. 이럴 땐 이렇게 해봐:

1. 필터에 쓰는 컬럼(age)에 인덱스가 있는지 확인해.
2. 테이블 행 수가 너무 많으면 파티셔닝도 고려해봐.

병목 현상과 해결 방법

1. 전체 테이블 스캔(Seq Scan) 인덱스를 써서 행 검색을 빠르게 해봐. 하지만 인덱스가 너무 많으면 데이터 삽입이 느려질 수도 있으니 주의!

2. 결과 행이 너무 많을 때 쿼리 결과가 수백만 행이면 WHERE, LIMIT 같은 필터나 OFFSET으로 페이지네이션을 고려해봐.

3. "비싼" 작업들 정렬, 집계, 큰 테이블 JOIN 같은 작업은 리소스를 많이 써. 인덱스를 활용하거나 쿼리를 여러 단계로 나눠서 실행해봐.