PostgreSQL 실행 계획으로 인덱스 효과 확인하기

필터와 정렬이 있는 조회를 예로 추정 행 수, 실제 행 수, 복합 인덱스를 비교합니다.

인덱스를 추가했는데 조회가 빨라지지 않는다면 실행 계획에서 무엇을 읽고 버리는지 확인해야 합니다. 아래는 PostgreSQL의 주문 목록 조회를 설명하는 예제입니다. 운영 DB에서 그대로 실행할 변경 명령이나 성능 측정 결과는 아닙니다.

필터와 정렬을 같이 보기

EXPLAIN
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

customer_id로 범위를 줄인 뒤 시간과 ID 순서로 정렬합니다. 후보 인덱스는 (customer_id, created_at DESC, id DESC)처럼 조회 형태에 맞춰 검토할 수 있습니다. 하지만 분포, 테이블 크기, 다른 조회와 쓰기 비용을 함께 보지 않고 바로 추가하지 않습니다.

실행 계획에서 적을 세 가지

첫째, 어떤 스캔을 사용하는지 봅니다. 순차 스캔이 항상 잘못된 것은 아닙니다. 테이블이 작거나 대부분의 행을 읽는다면 합리적일 수 있습니다. 둘째, 추정 행 수와 실제 행 수의 차이를 봅니다. 통계가 현실을 반영하지 못하면 계획 선택에 영향을 줄 수 있습니다. 셋째, 정렬과 반복 루프에서 작업량이 커지는지 봅니다.

EXPLAIN만 사용하면 계획을 보여 주고, EXPLAIN ANALYZE는 실제 쿼리를 실행합니다. 특히 데이터 변경문에 사용할 때 부작용을 이해해야 합니다. 실제 실행이 필요한 비교는 복제된 테스트 데이터와 제한된 환경에서 수행합니다.

인덱스 전후 비교표

첫 실행과 캐시가 따뜻해진 실행을 혼합하지 않습니다. 인덱스는 저장 공간을 차지하고 쓰기 시 유지 비용이 생기므로 조회 하나만 빠르게 하는 것이 전체 서비스의 개선은 아닐 수 있습니다.

쿼리 개수도 함께 확인하기

목록의 각 행마다 추가 조회를 하는 N+1 문제라면 개별 조회가 빨라도 전체 요청이 느릴 수 있습니다. 한 요청에서 실행한 SQL 개수와 누적 시간을 확인합니다. 큰 offset이 원인이라면 커서 페이지네이션이 적합한지 검토합니다.

숫자가 있는 계획을 읽는 연습

다음은 설명을 위해 단순화한 가상 계획이며 실제 DB 실행 결과가 아닙니다. 실행 계획의 모든 출력 필드를 복제한 형식도 아닙니다.

Sort: created_at DESC, id DESC
  estimated rows: 50
  actual rows: 5000, loops: 1
  Seq Scan on orders
    Filter: customer_id = 42
    Rows Removed by Filter: 95000

읽을 행을 50개로 예상했는데 실제로는 5,000개라면 먼저 분포와 통계가 맞는지 의심할 수 있습니다. 95,000개를 필터로 버렸다는 사실은 이 조건이 전체 행 중 작은 부분을 선택한다는 단서입니다. 정렬까지 따로 한다면 고객 ID와 정렬 키를 포함한 인덱스 후보를 비교할 이유가 있습니다.

그렇다고 이 조각만 보고 인덱스가 반드시 필요하다고 결정하지는 않습니다. 조회 빈도가 낮거나 쓰기가 매우 많다면 유지 비용이 더 중요할 수 있습니다. 실제 EXPLAIN ANALYZE에서는 rows와 시간 값이 loops당 평균으로 나타나는 노드도 있으므로 반복 횟수를 같이 읽어야 합니다. 루프가 1,000인데 한 번의 시간만 보고 저렴하다고 판단하면 누적 작업을 놓칩니다.

첫 비교는 동일한 조회 조건에서 수행하고, 특정 고객 한 명에만 효과가 있는지도 확인합니다. 데이터 분포가 치우쳐 있으면 다른 고객 ID에서 선택하는 계획이 달라질 수 있습니다.

참고 자료

• PostgreSQL: EXPLAIN 사용하기
• PostgreSQL: 다중 컬럼 인덱스