검색 쿼리의 인덱스는 실행계획에서 무엇을 확인한 뒤 고를까

병원별로 노출 중인 프로그램을 최신순 50개 조회한다고 하자. 다음 SQL에 display_status 인덱스가 있으면 충분할까.

SELECT id, hospital_id, title, display_status, updated_at
FROM program
WHERE hospital_id = :hospitalId
  AND display_status = 'VISIBLE'
  AND deleted_at IS NULL
ORDER BY updated_at DESC, id DESC
LIMIT 50;

이 조회는 상태만 찾는 작업이 아니다. 병원 범위를 제한하고 삭제된 행을 제외한 뒤, 같은 수정 시각을 가진 행까지 일정하게 정렬한다. 상태 인덱스가 있더라도 병원 필터와 정렬에 많은 작업이 남을 수 있다.

인덱스를 고르려면 위 조회에서 어떤 행과 페이지를 읽고 버리는지 실행계획으로 확인한다. 그 비용을 병원별 데이터 분포와 정렬 조건에 연결하면 인덱스 후보를 좁힐 수 있다.

SQL이 같아도 입력과 분포가 다르다

hospital_id=?만 있는 로그로는 느린 조건을 재현하기 어렵다. 데이터가 많은 병원과 적은 병원, 노출 데이터 비율이 다른 병원에서 같은 SQL의 비용이 달라질 수 있다. 최종 SQL과 바인딩 값에 테이블 크기, 조건별 분포, 반환 컬럼, 정렬, 페이지 위치를 연결한다.

한 요청이 같은 조회를 반복하는지도 확인한다. 쿼리 한 번을 줄이는 문제와 호출 횟수를 줄이는 문제는 원인과 대안이 다르다.

계획의 추정과 실제 실행을 나란히 읽는다

먼저 EXPLAIN으로 계획을 확인한다. 대표 데이터가 준비된 격리 환경에서는 다음처럼 실제 실행과 버퍼 사용을 볼 수 있다.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT id, hospital_id, title, display_status, updated_at
FROM program
WHERE hospital_id = 42
  AND display_status = 'VISIBLE'
  AND deleted_at IS NULL
ORDER BY updated_at DESC, id DESC
LIMIT 50;

ANALYZE는 SQL을 실행한다. 쓰기 SQL의 변경이나 함수의 외부 효과가 없어지는 옵션이 아니다. 읽기 조회도 많은 행을 읽으면 부하를 만든다. PostgreSQL EXPLAIN 문서의 실행 범위를 확인하고 대상 데이터와 환경을 정한다.

계획에서는 추정 행 수와 실제 행 수의 차이, 필터로 제거한 행, 하위 노드의 loops, 정렬의 디스크 사용, 버퍼 읽기를 먼저 본다. 반복 노드의 시간과 행 수는 실행당 평균이므로 loops를 함께 읽는다. 상위 노드 시간에는 하위 작업이 포함될 수 있어 모든 시간을 더하는 방식도 피한다.

그림에서 실제 측정은 안전성을 확인한 뒤에 위치한다. Index Scan이라는 이름보다 어느 단계에서 행을 많이 만들거나 버리는지 찾는 것이 다음 선택의 근거다.

추정이 어긋났다면 인덱스보다 통계를 먼저 본다

특정 병원에 노출 프로그램이 몰려 있으면 병원과 상태 조건은 독립적이지 않을 수 있다. 추정과 실제가 크게 다를 때는 통계 수집 시점과 컬럼의 상관관계를 확인한다. 필요한 조합에는 다음 통계를 후보로 검토한다.

CREATE STATISTICS st_program_hospital_status (dependencies, mcv)
ON hospital_id, display_status
FROM program;

ANALYZE program;

이 예시는 두 컬럼의 관계를 planner가 추정하는 데 보탬을 주기 위한 것이다. 통계를 늘렸다는 사실만으로 좋은 계획이 선택됐다고 판단하지 않는다. 실제 추정이 달라졌는지와 수집 비용을 확인한다. Planner 통계 문서를 기준으로 적용할 조합을 좁힌다.

조건과 정렬이 반복되는 범위에 후보를 만든다

노출 중이고 삭제되지 않은 행을 병원별 최신순으로 자주 읽는다면 다음 부분 인덱스를 검토할 수 있다.

CREATE INDEX CONCURRENTLY idx_program_hospital_visible_updated
ON program (hospital_id, updated_at DESC, id DESC)
WHERE display_status = 'VISIBLE'
  AND deleted_at IS NULL;

병원 조건으로 범위를 좁히고 그 안의 정렬을 지원하려는 후보다. 상태가 자주 바뀌거나 다른 상태도 빈번히 조회한다면 비용과 재사용성이 달라진다. 컬럼 순서는 선택도 하나만으로 정하지 않고, 동등 조건·범위·정렬·다른 조회의 사용을 함께 본다. 다중 컬럼 인덱스와 부분 인덱스의 조건을 확인한다.

CONCURRENTLY는 쓰기를 계속할 수 있게 인덱스를 만들지만 추가 스캔과 대기, 실패 후 invalid 인덱스 확인이 필요하다. 실제 추가 작업은 CREATE INDEX 문서의 해당 버전 절차로 계획한다.

뒤 페이지에서도 같은 조회 계약을 유지한다

OFFSET 50000은 앞의 행을 찾아 건너뛰는 비용을 남긴다. 다음 페이지 방식이 허용되면 마지막 정렬값 이후를 조회하는 조건을 사용할 수 있다.

WHERE hospital_id = :hospitalId
  AND display_status = 'VISIBLE'
  AND deleted_at IS NULL
  AND (updated_at, id) < (:cursorUpdatedAt, :cursorId)
ORDER BY updated_at DESC, id DESC
LIMIT 50;

updated_at과 id를 함께 비교해 동점 행의 경계를 만든다. 두 컬럼이 NULL이 아니고 커서 값과 정렬 방향이 일치한다는 전제가 필요하다. 수정 시각이 바뀌는 데이터에서는 페이지 사이 이동도 별도로 다룬다. 임의 페이지 점프가 필요하면 커서 방식의 제약을 제품 요구와 비교한다.

후보를 비교할 때는 같은 필터·정렬·반환 행과 페이지 의미를 유지한다. 데이터 분포, 바인딩, 캐시 상태, 반복 조건을 기록하고 지연뿐 아니라 읽은 페이지, 호출 수, 쓰기 지연과 저장 공간을 함께 본다.

인덱스 선택의 결론은 계획 이름을 바꾸는 데 두지 않는다. 대표 조건에서 불필요하게 읽는 범위를 줄였는지 확인하고, 다음에는 데이터가 몰린 병원과 뒤 페이지에서도 같은 판단이 성립하는지 검사한다.