db · 07

쿼리 실행 계획

DB쿼리실행 계획

db-03(인덱스)에서 인덱스가 어떻게 빠르게 찾는지를 봤다면, 이번엔 DB가 언제 인덱스를 실제로 쓰기로 결정하는지를 다룹니다. 인덱스가 있다고 항상 쓰이는 건 아닙니다 — DB의 쿼리 옵티마이저는 풀 스캔과 인덱스 스캔의 예상 비용을 계산해서 더 싼 쪽을 고릅니다. 그 선택 결과를 보여주는 것이 EXPLAIN입니다.

핵심 개념

쿼리 옵티마이저

같은 쿼리라도 실행할 수 있는 방법이 여러 가지입니다. 옵티마이저는 각 방법의 예상 비용을 계산해 가장 싼 것을 고릅니다.

핵심 개념

선택도 (Selectivity)

조건에 맞는 행이 전체에서 차지하는 비율입니다. 선택도가 낮을수록(희귀한 값일수록) 인덱스가 유리합니다. 자료에 따라 '선택도가 높다'를 반대 뜻으로 쓰기도 하므로 정의를 확인해야 합니다.

핵심 개념

인덱스 스캔의 숨은 비용

인덱스 스캔은 행 하나하나를 흩어진 위치에서 읽어옵니다(랜덤 접근). 일치하는 행이 많으면 이 비용이 풀 스캔보다 오히려 커집니다.

핵심 개념

EXPLAIN의 type

MySQL EXPLAIN 기준으로 ALL(풀 스캔), const·ref·range(인덱스 사용) 등으로 어떤 방식이 선택됐는지 보여줍니다.

핵심 개념

EXPLAIN의 key와 rows

key는 실제로 사용된 인덱스 이름(안 쓰였으면 NULL), rows는 옵티마이저가 예상한 스캔 행 수입니다.

핵심 개념

인덱스가 무시되는 흔한 이유

조건에 맞는 값이 테이블의 상당 부분을 차지할 때(예: 성별, 상태 플래그처럼 값의 종류가 적은 컬럼)는 인덱스가 있어도 옵티마이저가 풀 스캔을 선택합니다.

같은 인덱스, 다른 결정 — 이 시각화의 "선택도 높음"과 "선택도 낮음" 탭은 같은 user_grade 인덱스를 두고, WHERE 조건에 어떤 값이 오느냐에 따라 옵티마이저의 선택이 완전히 달라지는 것을 보여줍니다.

인덱스 스캔이 항상 이기지는 않습니다 — "선택도 높음" 탭에서 인덱스 스캔 비용(74)이 풀 스캔 비용(20)보다 오히려 큽니다. 일치하는 행이 전체의 90%나 되면, 인덱스를 타고 흩어진 행을 하나씩 찾아가는 것보다 그냥 순서대로 다 읽는 게 더 빠릅니다.

왜 이게 실무에서 중요한가 — "인덱스를 걸었는데 왜 안 타지?"라는 질문의 답 중 하나가 여기 있습니다. 컬럼 값의 분포(카디널리티)를 모르고 무작정 인덱스를 걸면 기대한 효과가 안 나올 수 있습니다.

이 시각화에서 확인할 것 — "EXPLAIN 읽기" 탭에서 type·key·rows의 의미를 먼저 익히고, "선택도 높음"·"선택도 낮음" 탭에서 같은 비용 공식으로 왜 정반대 결론이 나오는지 STEP으로 하나씩 비교하세요.

비용 기반 옵티마이저

관계형 DB는 쿼리를 어떻게 실행할지 사용자가 아니라 옵티마이저가 정합니다. 가능한 실행 계획마다 예상 비용을 계산해 가장 싼 계획을 고르는 비용 기반 방식은 IBM System R 연구의 접근 경로 선택 논문(Selinger 등, 1979)에서 널리 알려졌습니다. 비용 계산에는 테이블 행 수와 값의 분포 같은 통계가 쓰이므로, 통계가 실제와 다르면 계획이 어긋날 수 있습니다.

숫자로 확인하기

PostgreSQL의 기본 비용 가정 — 순차 페이지 읽기 비용을 1.0, 임의 페이지 읽기 비용을 4.0으로 두는 것이 기본 설정(seq_page_cost, random_page_cost)입니다. 임의 접근이 순차 접근의 4배라는 가정이라, 일치하는 행이 많으면 인덱스 스캔이 불리해집니다.

선택도 예시(설명용) — 100만 행 테이블에서 조건에 맞는 행이 0.1%면 1,000행이어서 인덱스로 찾는 쪽이 유리합니다. 90%면 90만 행을 흩어진 위치에서 하나씩 읽게 되어 순서대로 읽는 풀 스캔이 유리해질 수 있습니다.

자주 하는 오해

EXPLAIN은 쿼리를 실행해서 결과를 보여 준다 — 그냥 EXPLAIN은 실행 계획만 보여 주고 쿼리를 실행하지 않습니다. PostgreSQL의 EXPLAIN ANALYZE는 실제로 실행하므로, INSERT·UPDATE·DELETE에 쓰면 데이터가 바뀝니다.

cost 숫자는 밀리초다 — PostgreSQL 문서에 따르면 비용은 임의의 단위이고, 보통 순차 페이지 읽기 비용을 1로 두고 상대적으로 매깁니다. 시간으로 바꿀 수 있는 값이 아닙니다.

rows는 정확한 행 수다 — rows는 통계로 계산한 예상 값입니다. 실제 행 수와 크게 다르면 통계가 오래되었을 수 있고, EXPLAIN ANALYZE로 실제 값을 비교할 수 있습니다.

풀 스캔은 항상 나쁘다 — 테이블이 작거나 조건에 맞는 행이 많을 때는 풀 스캔이 더 빠를 수 있어서 옵티마이저가 일부러 고릅니다.

면접에서 이렇게 물어요

면접 질문

인덱스를 만들었는데 쿼리가 인덱스를 안 타는 이유는 무엇인가요?

조건에 맞는 행이 많아 풀 스캔이 더 싸다고 판단했거나, 컬럼에 함수를 씌우는 등 조건 형태가 인덱스와 맞지 않거나, 통계가 오래되어 비용을 잘못 추정했을 수 있습니다. EXPLAIN으로 선택된 계획을 먼저 확인합니다.

면접 질문

EXPLAIN과 EXPLAIN ANALYZE의 차이는 무엇인가요?

EXPLAIN은 예상 계획과 예상 비용만 보여 주고, ANALYZE를 붙이면 실제로 실행해 실제 시간과 행 수를 함께 보여 줍니다. 쓰기 쿼리에 붙이면 실제로 실행된다는 점을 조심해야 합니다.

면접 질문

옵티마이저는 통계를 어떻게 얻나요?

테이블의 행 수와 컬럼 값의 분포 같은 통계를 수집해 두고 비용을 추정합니다. PostgreSQL은 ANALYZE 명령으로 통계를 갱신하고, 자동 청소(autovacuum) 데몬이 이를 자동으로 실행하기도 합니다.

면접 질문

조인에는 어떤 알고리즘이 있나요?

PostgreSQL 문서는 중첩 루프 조인, 해시 조인, 병합 조인 세 가지를 설명합니다. 옵티마이저가 테이블 크기와 정렬 여부 등을 보고 비용이 낮은 쪽을 고릅니다.

실제 시스템에서는

PostgreSQL — EXPLAIN (ANALYZE, BUFFERS)로 실제 실행 시간과 읽은 버퍼 수까지 볼 수 있습니다. 계획에는 Seq Scan, Index Scan 같은 노드 이름이 나옵니다.

MySQL — EXPLAIN 출력의 type, key, rows 열로 접근 방식과 사용된 인덱스, 예상 행 수를 확인합니다. 이 토픽 시각화의 EXPLAIN 설명도 이 형식을 따릅니다.

통계 갱신 — 대량 적재나 삭제 뒤에는 통계가 실제와 달라질 수 있으므로 PostgreSQL에서는 ANALYZE로 갱신한 뒤 계획을 다시 확인합니다.

함께 보면 좋은 토픽

인덱스 — B-Tree가 값을 찾는 방식과 쓰기 비용을 봅니다.

SQL JOIN — 조인 알고리즘이 계획에 어떻게 나타나는지 이어서 봅니다.

RDBMS 기초 — 테이블, 키, 제약 조건의 기본 개념을 봅니다.

참고 자료

EXPLAIN 사용 — Using EXPLAIN — PostgreSQL Documentation

플래너 통계 — Statistics Used by the Planner — PostgreSQL Documentation

비용 상수 — Query Planning — PostgreSQL Documentation

원 논문 — Selinger, Astrahan, Chamberlin, Lorie, Price, "Access Path Selection in a Relational Database Management System", ACM SIGMOD, 1979

SQL — 같은 인덱스, 다른 EXPLAIN 결과

-- 선택도 낮은 값(희귀값) 조회: 인덱스가 유리 → type=ref
EXPLAIN SELECT * FROM users WHERE user_grade = 'VIP';
-- id | select_type | table | type | key             | rows | Extra
-- 1  | SIMPLE      | users | ref  | idx_user_grade  | 2    |

-- 선택도 높은 값(흔한값) 조회: 풀 스캔이 더 쌈 → type=ALL, key=NULL
EXPLAIN SELECT * FROM users WHERE user_grade = '일반';
-- id | select_type | table | type | key  | rows | Extra
-- 1  | SIMPLE      | users | ALL  | NULL | 20   |

idx_user_grade 인덱스는 두 쿼리 모두에 똑같이 존재하지만, 두 번째 쿼리에서는 key가 NULL입니다 — 인덱스가 있어도 옵티마이저가 "안 쓰는 게 더 빠르다"고 판단한 것입니다.
이 시각화의 "선택도 높음" 탭에서 인덱스 스캔 비용(74)이 풀 스캔 비용(20)보다 큰 것과 정확히 같은 이유입니다.

SQL — 옵티마이저의 판단을 확인하고 필요하면 개입하기

-- 예상이 아니라 실제 실행 결과(시간 포함)로 확인
EXPLAIN ANALYZE
SELECT * FROM users WHERE user_grade = '일반';

-- (드물게) 옵티마이저의 선택이 틀렸다고 확신할 때만 강제
SELECT * FROM users FORCE INDEX (idx_user_grade)
WHERE user_grade = 'VIP';

-- 옵티마이저의 비용 계산은 테이블 통계에 기반한다 — 통계가 오래되면 판단도 부정확해짐
ANALYZE TABLE users;

EXPLAIN은 "예상" 비용이고, EXPLAIN ANALYZE는 실제로 실행해서 "진짜" 걸린 시간과 실제 스캔 행 수를 보여줍니다 — 예상과 실제가 크게 다르면 통계가 낡았다는 신호입니다.
FORCE INDEX는 최후의 수단입니다. 대부분의 경우 옵티마이저의 판단이 이 시각화에서 본 것처럼 합리적인 비용 계산에 근거하므로, 함부로 강제하면 오히려 느려질 수 있습니다.

Java — 실제로 어떤 쿼리가 실행됐는지 확인하기

## application.yml — Hibernate가 생성한 실제 SQL을 로그로 확인
spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        # 통계까지 보고 싶다면
        generate_statistics: true

JPQL이나 @Query로 작성한 쿼리가 실제로 어떤 SQL로 나가는지 먼저 확인한 뒤, 그 SQL을 그대로 EXPLAIN 앞에 붙여 실행하면 이 시각화에서 본 것과 같은 방식으로 실행 계획을 읽을 수 있습니다.
운영 환경에서는 슬로우 쿼리 로그(slow_query_log)를 켜서, 어떤 쿼리가 예상과 다르게 풀 스캔으로 빠지고 있는지 주기적으로 확인하는 것이 좋습니다.

PostgreSQL의 EXPLAIN이 보여주는 것은 무엇입니까?

EXPLAIN은 실행 전 "예상" 계획을 보여줍니다. 실제 실행 시간까지 보려면 EXPLAIN ANALYZE를 씁니다.

PostgreSQL의 EXPLAIN은 옵티마이저가 선택한 노드(Seq Scan, Index Scan, Bitmap Heap Scan 등)와 예상 비용(cost), 예상 행 수(rows)를 보여줍니다.

선택도(Selectivity)가 낮다는 것은 어떤 의미입니까?

VIP 등급처럼 희귀한 값일수록 선택도가 낮습니다.

선택도 = 조건에 맞는 행 수 / 전체 행 수. 값이 작을수록(희귀할수록) Index Scan이 유리해집니다. 반대로 선택도가 높으면(흔한 값) Seq Scan이 더 쌀 수 있습니다.

PostgreSQL EXPLAIN 결과에서 Seq Scan이 나타나는 경우로 가장 적절한 것은?

인덱스가 있어도 조건에 맞는 행이 너무 많으면(선택도가 높으면) 옵티마이저가 Seq Scan을 선택할 수 있습니다.

Seq Scan은 "인덱스가 없다"는 뜻이 아닙니다. 인덱스가 있어도 조건에 맞는 행 비율(선택도)이 높으면, 여기저기 흩어진 행을 인덱스로 찾아가는 것보다 테이블을 처음부터 순서대로 읽는 게 더 빠르다고 옵티마이저가 판단한 것입니다.

PostgreSQL에서 옵티마이저 판단이 의심될 때, 통계를 최신 상태로 갱신하는 방법은?

옵티마이저의 비용 계산은 테이블 통계(pg_statistic)에 기반합니다.

PostgreSQL은 MySQL의 FORCE INDEX 같은 인덱스 힌트 문법을 지원하지 않습니다(옵티마이저 판단을 신뢰하는 설계 철학). 대신 ANALYZE users;로 통계를 갱신해 옵티마이저가 더 정확히 판단하도록 돕는 것이 정석입니다.