EXPLAIN을 사용하여 PostgreSQL에서 데이터베이스 쿼리를 프로파일링하여 성능이 느린 애플리케이션을 최적화합니다. 느린 쿼리는 대부분의 웹 애플리케이션에서 가장 큰 단일 병목 현상입니다. 이 가이드는 필수 인덱스 최적화 지침, 실행 계획을 읽는 방법, 자체 호스팅 또는 관리형 데이터베이스에 대한 주요 구성 매개 변수 조정을 안내합니다.

1. 효과적인 인덱스 설계

최신 데이터베이스의 주요 병목 현상은 디스크 I/O(스토리지에서 블록 파일 읽기)입니다. 인덱스를 추가하면 PostgreSQL이 전체 테이블을 스캔하는 대신 데이터 레코드를 직접 쿼리할 수 있습니다.

1) B-Tree 인덱스 기본값

표준 B-트리 인덱스는 여러 연산자 클래스를 지원합니다.

• 직접 일치 및 범위(=, <, >, BETWEEN)
• 가입 기준(JOIN ON ...)
• 정렬 작업(ORDER BY)

2) 복합 인덱스의 열 순서 규칙

복합 인덱스(여러 열 결합)를 생성할 때 PostgreSQL은 열을 왼쪽에서 오른쪽으로 구문 분석합니다. 주문 사항:

-- Create a composite index
CREATE INDEX idx_users_status_created ON users (status, created_at);

-- ◯ Index will be used (leftmost column "status" is present in the query)
SELECT * FROM users WHERE status = 'active' AND created_at > '2026-01-01';
SELECT * FROM users WHERE status = 'active';

-- ✕ Index will NOT be used efficiently (leftmost column "status" is missing)
SELECT * FROM users WHERE created_at > '2026-01-01';

2. EXPLAIN ANALYZE을 사용하여 실행 계획 프로파일링

느린 쿼리를 진단하려면 SQL 문 앞에 EXPLAIN ANALYZE을 추가하고 콘솔에서 실행하세요.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 45291;

출력의 주요 용어:

• Seq 스캔(순차 스캔): 데이터베이스가 테이블의 모든 단일 행을 스캔합니다. 큰 테이블에서 이런 일이 발생하면 인덱스가 누락된 것입니다.
• 인덱스 스캔 / 인덱스 전용 스캔: 데이터베이스가 인덱스를 쿼리하고 있습니다. Index Only Scan은 요청된 모든 열이 인덱스 트리 내에 존재하기 때문에 가장 빠른 조회 전략입니다. 즉, Postgres는 기본 테이블 힙에서 데이터 블록을 조회할 필요가 없습니다.
• 실제 시간: 실행 기간(밀리초)입니다. 이를 사용하여 어떤 하위 작업(예: 정렬 또는 해시 조인)이 가장 많은 시간을 소모하는지 찾아냅니다.

3. 구성 매개변수 최적화

Postgres를 자체 호스팅하거나 전용 서버를 사용하는 경우 기본 메모리 값은 보수적인 경우가 많습니다. postgresql.conf 파일에서 다음 값을 조정하세요.

• shared_buffers: 데이터베이스 테이블 캐싱 전용 메모리입니다. 전체 시스템 RAM의 약 **25%**를 할당하세요.
• work_mem: 임시 디스크 파일에 쓰기 전 내부 정렬 작업(ORDER BY) 및 해시 조인에 대한 메모리 제한입니다. 이를 4MB에서 16MB로 늘리면 복잡한 쿼리 속도가 대폭 향상될 수 있습니다.
• maintenance_work_mem: VACUUM 및 CREATE INDEX와 같은 유지 관리 절차에 사용되는 최대 메모리 제한입니다. 128MB에서 512MB 사이로 설정하면 마이그레이션 속도가 빨라집니다.

4. 결론

데이터베이스 성능 튜닝은 항상 데이터 중심으로 이루어져야 합니다. 어떤 인덱스를 추가할지 추측하지 마세요. 데이터베이스 수정 전후에 EXPLAIN ANALYZE을 실행하여 실행 비용과 쿼리 시간이 개선되었는지 확인하세요.