인덱스와 실행 계획으로 느린 쿼리 읽기

결론부터

인덱스의 개수보다 쿼리가 읽는 행과 페이지가 얼마나 줄었는지를 확인한다.

작업 세 개에서는 어떤 조회도 빨라 보인다. 성능을 이해하려면 더 많은 행에서 “특정 프로젝트의 최근 작업 20개”를 읽는 과정을 비교해야 한다.

이 장에서 처음 나오는 말3개

인덱스Index

조건에 맞는 행을 찾기 위한 별도 자료구조. 조회를 돕지만 저장 공간과 쓰기 비용이 든다.

실행 계획Query plan

DB가 행을 찾고 결합·정렬·집계하기 위해 선택한 작업 순서.

통계Planner statistics

값 분포와 행 수를 추정해 실행 방법을 선택하는 데 쓰는 정보.

비교할 데이터 준비

로컬 psql 한 연결에서 순서대로 실행한다. 임시 테이블은 원래 public.tasks와 별개이며 이 연결이 끝나면 사라진다.

CREATE TEMP TABLE task_perf AS
SELECT g::bigint AS id,
       (g % 1000)::bigint AS project_id,
       '작업 ' || g AS title
FROM generate_series(1, 100000) AS g;
ANALYZE task_perf;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM task_perf
WHERE project_id = 42
ORDER BY id DESC LIMIT 20;

인덱스가 없으므로 보통 전체를 읽어 조건에 맞는 100행을 찾고 정렬한다. 정확한 시간·cost 값은 환경마다 다르다. 계획의 이름과 읽은 행, 제외한 행을 먼저 본다. (EXPLAIN 읽기)

조건과 정렬을 함께 지원하기

CREATE INDEX task_perf_project_recent_idx
ON task_perf (project_id, id DESC);
ANALYZE task_perf;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM task_perf
WHERE project_id = 42
ORDER BY id DESC LIMIT 20;

이 데이터에서는 project_id = 42 범위를 찾고 ID 내림차순으로 필요한 행을 읽는 계획을 기대한다. 첫 결과 ID는 99042, 그다음은 98042다. 전체 스캔과 정렬이 줄었는지 확인한다. 플래너 선택은 통계·설정·데이터에 따라 달라지므로 “항상 Index Scan”을 성공 조건으로 삼지는 않는다.

복합 B-tree 인덱스는 컬럼 순서가 중요하다. 이 예제는 프로젝트를 고정한 뒤 그 안에서 최신 순서를 읽는다. 다른 쿼리인 “모든 프로젝트의 최근 작업”에 같은 효과를 보장하지 않는다. (복합 인덱스)

계획에서 읽을 숫자

항목	읽는 방법
추정 rows와 actual rows	큰 차이가 있으면 통계·조건의 상관관계를 의심한다
loops	같은 작업이 반복된 횟수. 행·시간이 반복당 값인지 함께 읽는다
Rows Removed by Filter	읽고 나서 버린 행이 많으면 접근 경로를 검토한다
Buffers	캐시 적중과 읽기 등 페이지 작업량. 임시 테이블은 local buffer로 표시될 수 있다
Sort	정렬의 대상 행 수와 메모리·디스크 사용을 본다

Seq Scan 자체는 오류가 아니다. 작은 테이블이나 대부분의 행을 읽는 쿼리에서는 합리적일 수 있다. 인덱스를 더 만들면 INSERT·UPDATE·DELETE도 그 인덱스를 관리해야 한다.

ANALYZE는 실제 실행한다

EXPLAIN은 계획을 보여 주고, EXPLAIN ANALYZE는 쿼리를 실제로 실행한다. UPDATE·DELETE에 붙이면 데이터도 바뀐다. SELECT라도 무거운 작업이나 부수 효과가 있는 함수를 실행할 수 있다. 먼저 실습 DB에서 확인하고 운영에서는 대상과 비용을 판단한다.

DROP TABLE task_perf;

이해 확인: 인덱스를 만들었는데 느리면 무엇을 더 볼까? 실행 계획뿐 아니라 잠금 대기, 반환 데이터 양, 디스크와 CPU, 연결 대기를 구분한다. 느림을 전부 인덱스 부재로 설명하지 않는다.