SQL 튜닝 (PostgreSQL 우선)
무언가를 변경하기 전에 먼저 측정하십시오. 변경 전/후 계획과 실행 시간 없이 하는 튜닝은 추측에 불과합니다.
절차
- 대상 찾기: 단일 가장 느린 호출이 아닌 총 비용 기준으로 순위를 매깁니다(트라이지 루프 참조).
- 기준선 확보: 프로덕션 규모의 데이터로
EXPLAIN (ANALYZE, BUFFERS) <query>를 실행합니다. 작은 개발 데이터는 다른 계획을 선택합니다. - 시간 또는 행 수가 잘못된 노드를 찾습니다(아래 실행 계획 읽기 규칙 참조). 해당 노드만 수정합니다.
- 동일한 계획 캡처를 다시 실행합니다. 시간과 버퍼 모두에서 개선된 경우에만 변경을 유지합니다.
- 인덱스나 쿼리 재작성 내용과 그 이유를 스키마 마이그레이션 옆에 기록합니다.
INSERT/UPDATE/DELETE의 EXPLAIN ANALYZE는 BEGIN; ... ROLLBACK;으로 감싸십시오. ANALYZE는 실제로 명령문을 실행하기 때문입니다.
실행 계획 읽기
- 아래에서 위로, 내부 노드부터 읽습니다. 노드별
actual time은 루프당 시간입니다. 실제 비용은loops를 곱하여 계산합니다. rows(추정치)와actual rows를 비교합니다. 10배 이상 차이가 나는 경우, 누락된 인덱스보다 훨씬 더 자주 근본 원인이 됩니다. 이 경우ANALYZE <table>을 실행하거나, 컬럼 통계 대상을 높이거나, 상관된 컬럼에 대해 확장 통계(CREATE STATISTICS ... (dependencies, ndistinct))를 추가하십시오.Rows Removed by Filter가 반환된 행보다 훨씬 크다면, 인덱스(또는 인덱스 부재)가 현재 조건에 대해 선택적이지 않다는 뜻입니다.Buffers: shared read는 디스크 또는 OS 캐시를 의미하고,shared hit는 shared_buffers를 의미합니다. 반복 실행에서 읽기가 많다면 작업 세트가 메모리에 맞지 않는 것입니다.Sort Method: external merge Disk:는 work_mem이 이 정렬에 비해 너무 작았다는 뜻입니다. 전역이 아닌 세션별 또는 역할별로 증가시키십시오. 연결별 정렬 노드마다 곱해지기 때문입니다.- 큰 내부
loops수와 인덱싱되지 않은 내부 측을 가진Nested Loop는 고전적인 느린 조인입니다.Hash Join이 넘치는 경우(Batches: >1)도 동일한 work_mem 문제입니다. - Seq Scan은 쿼리가 테이블의 큰 부분을 읽을 때 올바릅니다. 행의 20%를 반환하는 Seq Scan을 제거하기 위해 인덱스를 추가하지 마십시오.
- Index Only Scan에서
Heap Fetches가 발생하면 가시성 맵이 오래된 것입니다. 해당 테이블에 VACUUM을 실행하십시오.
인덱스 선택
측정한 조건, 조인, 또는 ORDER BY에 대해서만 인덱스를 생성하십시오. 모든 인덱스는 쓰기 시간, WAL, 그리고 vacuum 작업을 증가시킵니다.
- btree: 동등 조건, 범위, 정렬에 기본적으로 사용합니다. 컬럼 순서: 동등 조건 컬럼을 먼저, 그 다음 범위 또는 정렬 컬럼을 배치합니다. PG 18의 스킵 스캔은 앞선 컬럼이 누락된 일부 경우에 도움이 되지만, 이를 기준으로 설계하지 마십시오.
- Partial (
WHERE status = 'open'): 쿼리가 작고 안정적인 데이터 조각(열린 항목, 아직 처리되지 않은 행)을 대상으로 할 때 사용합니다. 쿼리의 WHERE 절이 인덱스 조건을 포함해야 합니다. - Covering (
INCLUDE (col)): 인덱스 전용 스캔이 핫 쿼리의 힙 페치를 제거할 때 사용합니다. 포함된 컬럼은 검색 가능한 키가 아닙니다. - Expression (
ON (lower(email)),ON ((payload->>'id'))): 쿼리가 컬럼에 함수를 적용할 때 사용합니다. 쿼리는 동일한 표현식을 사용해야 합니다. - BRIN: 컬럼이 물리적 순서(타임스탬프, 시리얼 ID)와 상관관계가 있는 대규모 추가 전용 테이블에 사용합니다. 작은 인덱스, 거친 프루닝.
pg_stats.correlation이 1에 가까운지 확인하십시오. 낮으면 BRIN이 프루닝하지 않습니다. - GIN: jsonb 포함, 배열, 전문 검색. GiST: 범위, 지리, 제외 제약 조건.
- 운영 중인 테이블에는
CREATE INDEX CONCURRENTLY로 생성하십시오. 이 명령은 트랜잭션 블록 내에서 실행할 수 없으며, 실패하면 삭제해야 할 INVALID 인덱스가 남습니다. - 사용하지 않는 인덱스는 전체 비즈니스 주기(금융 워크로드의 경우 월말) 동안
pg_stat_user_indexes.idx_scan을 확인하고, 복제본에서도 확인한 후에만 삭제하십시오.
Sargability (검색 조건 인덱스 활용 가능성)
조건절이 인덱스를 사용하려면 인덱스 컬럼이 한쪽에 단독으로 있어야 합니다.
WHERE extract(year from created_at) = 2026또는date_trunc(...) = ...대신WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'을 사용하십시오.WHERE col = $1과 일치하는 타입을 사용하십시오.numeric_col = '5'::text스타일의 캐스트는 컬럼 측에서 인덱스를 차단합니다. 매개변수를 캐스팅하고 컬럼은 캐스팅하지 마십시오.- 선행 와일드카드
LIKE '%x'는 피하고pg_trgmGIN 또는 전문 검색을 사용하십시오. - 서로 다른 컬럼에 대한
OR는 종종 인덱스를 사용하지 못하게 합니다. 두 개의 인덱스 분기를UNION ALL로 시도해 보십시오. NOT IN (서브쿼리)는 NULL에 취약하고 느리므로NOT EXISTS를 사용하십시오.- 세미 조인에는
DISTINCT보다EXISTS를 선호하십시오. 첫 번째 일치에서 멈추고 행을 중복시키지 않기 때문입니다.
윈도우 함수 vs. 셀프 조인
같은 집합 내 관련 행에서 행별 값을 얻는 것이 목표일 때(누적 합계, 순위, lag/lead, "키별 최신 행" (row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1))는 윈도우 함수를 사용하십시오. 한 번의 스캔과 한 번의 정렬로 테이블 셀프 조인을 대체합니다.
셀프 조인 또는 LATERAL은 두 번째 측이 자체 필터나 인덱스 프로브가 필요할 때만 사용하십시오(대규모 테이블에서 키별 상위 N개: JOIN LATERAL (SELECT ... ORDER BY ts DESC LIMIT 3)와 (k, ts DESC) 인덱스 사용).
DISTINCT ON (k) ... ORDER BY k, ts DESC는 키별 최신 값을 얻는 간단한 PostgreSQL 형식입니다.
CTE
PG 12부터, 비재귀적이고 부수 효과가 없으며 한 번만 참조되는 CTE는 서브쿼리처럼 인라인됩니다. 기본적으로 가독성을 위해 작성하십시오.
- CTE가 비싸고 여러 번 참조되거나, 플래너가 나쁜 조건을 CTE 안으로 밀어넣는 것을 막아야 할 때
AS MATERIALIZED를 추가하십시오. - 여러 번 참조되는 CTE에 외부 필터가 저렴하게 만들 때 인라인을 강제하려면
AS NOT MATERIALIZED를 추가하십시오. - 데이터를 수정하는 CTE는 항상 한 번 실행됩니다. 최적화되어 사라지지 않습니다.
pg_stat_statements 트라이지 루프
shared_preload_libraries에 pg_stat_statements가 있어야 하며(재시작 필요), CREATE EXTENSION이 필요합니다.
total_exec_time DESC순으로 정렬합니다(shared_blks_read,temp_blks_written,rows도 확인). 5ms 쿼리가 4천만 번 호출되면 30초 야간 쿼리보다 더 중요합니다.- 상위 3개를 가져옵니다. 각각에 대해 실제 매개변수 집합을 구하고(통계는 정규화됨) 절차의 2단계를 실행합니다.
- 수정, 배포한 후 해당 쿼리에 대해서만
SELECT pg_stat_statements_reset(<userid>, <dbid>, <queryid>)를 실행하고, 대표적인 기간 동안 기다린 후 비교합니다. - 상위 항목이 정당한 비용이 될 때까지 반복합니다. 거기서 멈춥니다.
재현할 수 없는 쿼리의 계획을 잡기 위해
auto_explain(log_min_duration,log_analyze는 절약하여 사용)과 함께 사용하십시오.
PL/pgSQL은 가치가 있을 때만 사용
함수나 프로시저는 집합 기반으로 처리할 수 없는 행 단위 로직에 대한 왕복을 제거하거나, 트리거 불변 속성을 강제하거나, 다중 명령문 트랜잭션을 캡슐화할 때 사용하십시오. 각 행에 대해 하나의 UPDATE를 발행하는 루프에 사용하지 마십시오. 하나의 UPDATE ... FROM 또는 INSERT ... SELECT로 다시 작성하십시오. 순수 함수에 IMMUTABLE/STABLE을 올바르게 표시하십시오(잘못된 레이블은 잘못된 캐시 결과를 생성하거나 인라인을 차단합니다). 가능한 경우 플래너가 인라인할 수 있도록 일반 SQL 함수를 선호하십시오.
N+1 및 페이지네이션
- N+1: 애플리케이션이나 ORM 코드에서 부모 행당 하나의 쿼리를 실행합니다. 단일
WHERE id = ANY($1)배치, 조인, 또는 ORM의 eager-load 옵션으로 수정하십시오. pg_stat_statements를 통해 감지하십시오. 하나의 정규화된 명령문의calls가 부모 행 수에 가까운 경우입니다. - OFFSET 페이지네이션은 건너뛴 모든 행을 읽고 버리므로, N페이지의 비용은 O(N)입니다. keyset을 사용하십시오:
WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50와(created_at DESC, id DESC)인덱스 사용. 연결 고리 컬럼은 필수입니다. 그렇지 않으면 행이 건너뛰거나 중복됩니다. Keyset은 N페이지로 바로 이동할 수 없습니다. OFFSET은 작고 제한된 집합에만 사용하십시오. - 큰 테이블의
count(*)는 전체 스캔입니다. 근사치(reltuples)를 보여주거나SELECT count(*) FROM (SELECT 1 ... LIMIT 10001)로 제한하십시오.
대량 ETL 참고 사항
행 삽입보다 COPY(또는 INSERT ... SELECT)로 배치 로드하십시오. 대규모 초기 로드의 경우에만 보조 인덱스를 드롭하거나 연기하고, 로드 후 ANALYZE를 실행하십시오. 대규모 UPDATE는 테이블을 bloating시킵니다. 키 범위로 배치 처리하고, 배치마다 커밋하며, autovacuum이 따라잡을 수 있게 하십시오.
기타 방언 (간략)
- BigQuery: 비용은 바이트 청구 기준이지 행 수가 아닙니다. 날짜/타임스탬프 컬럼으로 파티셔닝하고 리터럴 또는 매개변수로 필터링하여 프루닝이 발생하도록 하십시오. 다음으로 많이 필터링되는 컬럼으로 클러스터링하십시오.
SELECT *는 모든 컬럼에 대해 비용이 청구됩니다. 컬럼을 명시하십시오. 실행 전에 바이트를 확인하려면 드라이 런을 사용하십시오.LIMIT는 청구되는 바이트를 줄이지 않습니다. - Snowflake: 프루닝은 마이크로파티션의 최소/최대 메타데이터를 사용합니다. 로드 시 값이 자연스럽게 정렬되는 컬럼으로 필터링하십시오. 쿼리 프로필에서 "파티션 스캔 vs 전체"를 확인하십시오. 매우 큰 테이블에 안정적인 필터 패턴이 있는 경우에만 클러스터링 키를 추가하십시오. 리클러스터링은 크레딧을 소비하기 때문입니다. 넘침(프로필의 로컬/리모트 스필)에 대해서는 웨어하우스 크기를 늘리고, 큐잉/동시성에 대해서는 스케일 아웃(멀티 클러스터)하십시오. 오토 서스펜드는 짧게 설정하십시오.
현재 버전 및 문서 링크: references/landscape-2026-11.md. 실제 사용 사례: references/gotchas.md.