EXPLAIN ANALYZE로 슬로우 쿼리 읽기 - 타입 불일치가 만든 10,000번의 반복 조회

LLM 시대에도 실행계획을 직접 읽어야 하는 이유

슬로우 쿼리 분석도 자동화되는 시대

요즘은 LLM을 이용해 슬로우 쿼리 분석을 자동화하려는 시도들이 많아지고 있다. 실행 로그를 모으고, SQL을 추적하고, EXPLAIN 결과를 해석하고, 개선안을 제안하는 흐름 자체가 점점 자동화의 대상이 되고 있다.¹

그렇다면 개발자가 직접 실행계획을 읽는 능력은 덜 중요해질까? 나는 오히려 반대라고 생각한다. 자동화된 분석 결과를 신뢰하려면, 결국 사람이 그 판단을 검증할 수 있어야 한다. LLM이 “이 인덱스를 추가하세요”라고 말했을 때, 그 제안이 왜 맞는지, 혹은 왜 위험한지 판단하려면 실행계획을 읽는 감각이 필요하다.

특히 MySQL의 EXPLAIN ANALYZE는 단순한 추정 계획이 아니라 실제 실행 중 처리된 행 수, 반복 횟수, 수행 시간을 보여준다. 이 값들을 함께 보면 쿼리가 어디서 시간을 쓰는지, 어떤 조인이 반복 비용을 만들고 있는지, 인덱스가 정말 원하는 방식으로 사용되고 있는지를 추적할 수 있다.

권한 조회는 왜 실행계획이 복잡해지는가

이 글에서는 HR 도메인의 권한 조회 쿼리를 예시로 EXPLAIN ANALYZE 결과를 단계적으로 읽어본다. HR 도메인에서는 인가된 사용자만 특정 데이터에 접근할 수 있어야 한다. 예를 들어 팀장은 소속 팀원의 근태 기록을 조회할 수 있지만, 일반 팀원들끼리는 서로의 기록을 볼 수 없어야 한다.

이런 요구사항은 보통 조회 쿼리에 권한 조건을 함께 넣어 해결한다. 요청자가 누구인지, 어떤 대상을 볼 수 있는지, 해당 권한이 아직 유효한지를 쿼리 시점에 평가하고, 조건을 통과한 데이터만 반환하는 방식이다.

문제는 이 권한 필터가 단순한 WHERE 조건 하나로 끝나지 않는다는 점이다. 권한 테이블과의 조인, 유효기간 조건, 테넌트 조건, 대상자 조건이 함께 얽히면서 실행계획이 복잡해진다. SQL만 보고는 병목을 단정하기 어렵고, 인덱스가 있어도 기대한 방향으로 사용되지 않을 수 있다.

이 글에서 다루는 사례

실제로 운영 환경에서도 권한 조회 비용이 예상보다 크게 발생했다. 원인을 확인하기 위해 EXPLAIN ANALYZE로 실행계획을 분석했고, 그 과정에서 컬럼 타입 불일치가 조인 방향과 인덱스 탐색 방식에 영향을 주고 있음을 확인했다.

이 글에서는 운영 데이터 대신 같은 원인이 드러나는 최소 재현 환경을 만들고, 실행계획을 단계적으로 읽어가며 병목을 좁혀본다. 처음에는 권한 테이블에서 10,000건의 후보를 읽고 근태 기록을 반복 조회하던 쿼리가, 타입 불일치를 제거한 뒤 700건 중심의 조회로 바뀌면서 실행 시간이 80.8ms에서 2.52ms로 줄어드는 과정을 살펴본다.

핵심은 “어떤 인덱스를 추가했는가”보다, actual time, rows, loops를 통해 반복 비용이 어디서 만들어지는지 추적하는 과정이다.

슬로우 쿼리가 발생하는 구조 재현

재현 환경은 4개의 테이블로 구성했다.

EMPLOYEES는 요청자와 조회 대상 직원이 될 수 있으며, ATTENDANCE_RECORDS는 해당 직원들의 근태 기록을 나타낸다. 예를 들어, 출근 시각 정보가 될 수 있다.

VISIBILITY_GRANTS는 특정 직원이 다른 직원의 근태 정보를 볼 수 있는지 저장하는 권한 테이블이다. subject_id는 권한을 가진 직원이고, target_id는 조회 대상 직원이다.

erDiagram
    TENANTS ||--o{ EMPLOYEES : has
    TENANTS ||--o{ ATTENDANCE_RECORDS : owns
    EMPLOYEES ||--o{ ATTENDANCE_RECORDS : recorded_for
    EMPLOYEES ||--o{ VISIBILITY_GRANTS : subject
    VISIBILITY_GRANTS }o..|| EMPLOYEES : "target_id -> id"

    TENANTS {
        BIGINT id PK
    }

    EMPLOYEES {
        BIGINT id PK
        BIGINT tenant_id FK
        VARCHAR team_code
    }

    ATTENDANCE_RECORDS {
        BIGINT id PK
        BIGINT tenant_id FK
        BIGINT employee_id FK
        VARCHAR team_code
    }

    VISIBILITY_GRANTS {
        BIGINT id PK
        BIGINT tenant_id FK
        VARCHAR permission_type
        VARCHAR subject_type
        BIGINT subject_id FK
        VARCHAR target_type
        VARCHAR target_id
    }

표 1. 재현 데이터 규모

표 2. visibility_grants 구성. 각 권한이 분석 쿼리의 어떤 조건에 걸러지는지 함께 표시했다.

이 구조에서 핵심은 VISIBILITY_GRANTS.target_id와 ATTENDANCE_RECORDS.employee_id의 관계다. 두 값은 같은 직원 ID를 가리키지만 타입이 다르다.

분석 대상 쿼리

아래 쿼리는 특정 요청자(requester)가 갖는 권한을 기준으로 대상자 직원(target)을 필터링해서 근태기록을 조회한다.

SELECT DISTINCT ar.id
FROM employees requester

-- 1. 요청자와 같은 테넌트의 근태 기록
JOIN attendance_records ar
  ON ar.tenant_id = requester.tenant_id

-- 2. 요청자가 볼 수 있는 직원인지 권한 테이블로 확인
JOIN visibility_grants vg
  ON vg.tenant_id = ar.tenant_id
 AND vg.permission_type = 'ATTENDANCE_READ'
 AND vg.subject_type = 'EMPLOYEE'
 AND vg.subject_id = requester.id
 AND vg.target_type = 'EMPLOYEE'

-- 3. 권한 대상 직원과 근태 기록의 대상 직원 비교
 AND vg.target_id = ar.employee_id

-- 4. 권한 유효 기간 확인
 AND vg.started_at <= NOW()
 AND vg.ended_at > NOW()

-- 5. 요청자와 조회 범위 제한
WHERE requester.tenant_id = 1
  AND requester.login_id = 'requester-1'
  AND requester.deleted_at IS NULL
  AND ar.team_code = 'team-a'
  AND ar.deleted_at IS NULL;

변경 전 인덱스

아래 인덱스는 개선이 이뤄지기 이전에 존재했던 인덱스 상태이다. idx_visibility_grants_legacy 인덱스는 4가지 컬럼으로 구성된다.

CREATE UNIQUE INDEX uk_employees_tenant_login
  ON employees (tenant_id, login_id);

CREATE INDEX idx_visibility_grants_legacy
  ON visibility_grants (
    tenant_id,
    permission_type,
    target_id,
    subject_id
  );

CREATE INDEX idx_attendance_records_tenant_team
  ON attendance_records (tenant_id, team_code);

CREATE INDEX idx_attendance_records_tenant_employee
  ON attendance_records (tenant_id, employee_id);

변경 전 실행계획

앞에서 본 쿼리를 재현 환경에서 EXPLAIN ANALYZE로 확인했다. 아래 실행계획은 운영 환경에서 관찰한 문제 원인이 드러나도록 구성한 재현 결과다.²

-> Table scan on <temporary>  (cost=128..128 rows=0.132) (actual time=80.7..80.8 rows=700 loops=1)
    -> Temporary table with deduplication  (cost=126..126 rows=0.132) (actual time=80.7..80.7 rows=700 loops=1)
        -> Nested loop inner join  (cost=126 rows=0.132) (actual time=0.18..80.7 rows=700 loops=1)
            -> Filter: ((vg.target_type = 'EMPLOYEE') and (vg.subject_type = 'EMPLOYEE') and (vg.started_at <= <cache>(now())) and (vg.ended_at > <cache>(now())))  (cost=121 rows=1.33) (actual time=0.167..13.2 rows=10000 loops=1)
                -> Index lookup on vg using idx_visibility_grants_legacy (tenant_id=1, permission_type='ATTENDANCE_READ'), with index condition: (vg.subject_id = '1')  (cost=121 rows=11994) (actual time=0.163..10.7 rows=12000 loops=1)
            -> Filter: ((ar.team_code = 'team-a') and (ar.deleted_at is null))  (cost=2.49 rows=0.0993) (actual time=0.00665..0.00667 rows=0.07 loops=10000)
                -> Index lookup on ar using idx_attendance_records_tenant_employee (tenant_id=1, employee_id=vg.target_id), with index condition: (cast(vg.target_id as double) = cast(ar.employee_id as double))  (cost=2.49 rows=9.93) (actual time=0.00521..0.00592 rows=10 loops=10000)

처음 이 실행계획을 마주했을 때 어떻게 분석해야 할지 막막했다. 이 고민하는 과정에서 여러 레퍼런스들³을 종합적으로 살펴보고 시도를 해봤다. 실행계획을 단계별로 읽는 워크시트를 만들었고, 그 틀을 따라가며 병목을 좁힐 수 있었다. 아래에서는 그 과정에서 얻은 분석 방법을 재현 사례에 적용해 정리한다.

EXPLAIN과 EXPLAIN ANALYZE의 차이

EXPLAIN과 EXPLAIN ANALYZE의 차이점은 MySQL 블로그⁴에 설명되어 있다. EXPLAIN ANALYZE는 EXPLAIN FORMAT=TREE의 확장으로 볼 수 있으며, 각 노드의 추정치가 아닌 실제 처리된 행 수와 소요시간을 측정한다. 아래 인용구에서 EXPLAIN ANALYZE 결과를 분석할 때 도움 되는 휴리스틱을 제공한다.

• If you wonder what it’s taking so long, look at the timing. Where does the executor spend its time?
• If you wonder why the optimizer chose that plan, look at the row counters. A large difference (i.e., a couple of orders of magnitude or more) between the estimated number of rows and the actual number of rows is a sign that you should look closer at it. The optimizer chooses its plan based on the estimate, but looking at the actual execution may tell you that another plan would have been better.

인용구에서 알 수 있듯이 수행시간은 분석하는 데 중요한 단서가 되며 actual time으로 표기된다. actual time에는 두 가지가 있는데 앞의 수행시간은 첫 row를 가져오기까지의 시간이며, 뒤의 수행시간은 모든 rows를 가져오기까지의 시간이다.⁵

1단계: 전체(Root) 수행시간 확인

가장 먼저 루트 노드의 수행시간을 확인한다. 실행계획은 트리 구조이고 루트 노드는 하위 노드들의 결과를 받아 최종 결과를 반환하는 지점이다. 따라서 루트 노드의 actual time을 보면 쿼리 전체가 결과를 반환하기까지 걸린 시간을 먼저 파악할 수 있다.

-> Table scan on <temporary>  (cost=128..128 rows=0.132) (actual time=80.7..80.8 rows=700 loops=1)

여기서 actual time=80.7..80.8는 첫 행을 반환한 시점과 마지막 행을 반환한 시점이 모두 약 81ms였다는 뜻이다. actual time은 loops당 평균 시간으로 표시되는데, 이 예시는 loops=1이므로 한 번 실행했을 때의 시간으로 볼 수 있다. rows=700은 최종적으로 700행을 반환했다는 의미다.

2단계: 단계별 수행시간 계산하기

실행계획은 트리 구조이기 때문에 루트에서 아래로 내려가며 읽을 수 있다. 이때 각 노드는 크게 두 가지 형태로 이어진다. 하나의 자식 노드만 갖는 1:1 구간이거나, 여러 자식 노드로 나뉘는 분기점이다. 1단계에서 루트 노드의 전체 수행시간이 80.8ms라는 것을 확인했다. 루트에서 Nested loop inner join까지는 1:1 구조를 가지며, 그 이후는 분기를 하게 된다. 이를 아래 코드블럭에 나타냈다.

  Table scan on <temporary>          80.8ms
  └─ Temporary table deduplication   80.7ms
     └─ Nested loop inner join       80.7ms

이제 양쪽 자식의 rows, loops, actual time을 비교해본다. 이때 아래와 같은 워크시트를 만들어서 채우면 도움이 된다. 전체 결과는 부록 A. 단계별 수행시간 계산 결과 테이블에 있다.

표 3. Nested loop inner join 분기점의 노드별 수행시간 비교

actual time은 loops당 평균 시간이므로 반복 실행되는 노드는 한 번의 시간이 작아보여도 전체 비용은 커질 수 있다. attendance_records Filter 노드의 환산 총 시간을 계산할 때는 actual time인 0.00667ms에 loops 수인 10,000만큼 곱한 결과(= 66.7ms)를 기록해야 한다. 이는 actual time은 해당 노드의 전체 수행시간과 총 loops를 고려하여 1 loop의 평균 수행시간을 역산한 결과이기 때문이다.

단, 이 환산값은 각 노드의 비용 감각을 잡기 위한 값이지, 모든 노드를 단순 합산하기 위한 값은 아니다. 부모 노드의 시간에는 자식 노드 수행 시간이 포함될 수 있으므로, 같은 경로의 부모-자식 시간을 더하면 중복 계산이 된다.

정리해보면 이 Nested loop inner join에서는 드라이빙 테이블인 visibility_grants가 바깥쪽 입력을 제공하고, 그 결과를 기준으로 드리븐 테이블인 attendance_records 조회가 반복된다. visibility_grants는 한 번 실행되어 10,000건을 반환했고, 이 수가 attendance_records의 반복 횟수(loops=10,000)로 이어졌다.

숫자로만 보면 구조가 잘 드러나지 않기 때문에, 같은 내용을 다음과 같이 시각화하였다:

flowchart TD
    A["Table scan (temporary)<br/>80.8 ms"]:::inherited
    B["Temporary table dedup<br/>80.7 ms"]:::inherited
    C["Nested loop inner join<br/>80.7 ms"]:::inherited
    D["visibility_grants Filter<br/>13.2 ms<br/>10,000 rows"]:::cool
    E["visibility_grants Index lookup<br/>idx_visibility_grants_legacy<br/>10.7 ms"]:::cool
    F["attendance_records Filter<br/>0.00667 ms × 10,000 ≈ 66.7 ms"]:::hot
    G["attendance_records employee lookup<br/>0.00592 ms × 10,000 ≈ 59.2 ms"]:::hot

    A --> B --> C
    C --> D
    D --> E
    C --> F
    F --> G

    subgraph Legend [범례]
        L1["낮은 비중 ( < 20% )"]:::cool
        L2["병목 ( > 70% )"]:::hot
        L4["상위 집계 노드"]:::inherited
    end

    classDef hot fill:#ff6b6b,color:#fff,stroke:#b02a37,stroke-width:2px
    classDef cool fill:#b2f2bb,color:#000,stroke:#2f9e44,stroke-width:1px
    classDef inherited fill:#f1f3f5,color:#495057,stroke:#adb5bd,stroke-width:1px

그림 1. Nested loop inner join 분기점의 실행 구조. visibility_grants에서 활성 권한 10,000건이 attendance_records 조회 반복으로 이어진다.

플로우 차트를 보면 분기된 두 노드는 독립된 조회 결과를 가져와 합치는 메커니즘으로 동작한다고 오해할 수 있다. 예를 들어, visibility_grants에서 10,000건을 모두 메모리에 적재한 뒤 attendance_records와 조인하는 것은 아니다. 실제 동작은 집계 함수나 정렬같은 블로커가 없으면 스트리밍 방식으로 동작한다.⁶ 현재 쿼리의 DISTINCT도 블로커인데 유일성을 보장받기 위해 모든 레코드를 모아 판단해야하기 때문이다.

스트리밍 동작 방식의 이해

스트리밍 동작 방식은 다음과 같다.

먼저, vg 테이블의 idx_visibility_grants_legacy 인덱스를 이용하여 인덱스 엔트리를 읽는다. 여기서 바로 실제 테이블 행을 읽지 않고 인덱스 컬럼만으로 평가할 수 있는 조건을 스토리지 엔진으로 내려보내(Pushdown) 인덱스 엔트리를 검사하는 최적화 기법을 사용한다.

걸러진 인덱스 엔트리에서 가장 첫 번째 행에 해당하는 실제 레코드에 접근하여 가져온다. 가져온 레코드는 조인 동작의 가장 바깥쪽의 iterator 역할을 하고 조인 조건에 따라 드리븐 테이블의 인덱스 엔트리 시작점을 찾게 된다. 일치하는 인덱스 범위 내에서 0개 이상의 행을 반환하여 조인하게 된다. 이러한 원리 때문에 INNER JOIN은 조건에 맞지 않으면 사라지거나 여러 행으로 증폭될 수 있다.

아래에 머릿속에서 상상하는 스트리밍 처리 방식의 멘탈모델을 시각화해서 표현했다. 개별 호출의 실제 시작과 종료 시각을 재현한 것은 아니며 간소화한 부분도 있다.

gantt
    title Nested loop 평균 처리 과정 (시뮬레이션 · 100,000배 확대)
    dateFormat x
    axisFormat %L
    tickInterval 100millisecond

    section Nested loop
    전체 실행 구간 :active, nl, 0, 1614ms

    section vg subtree
    다음 vg 행 반환 1 :v1, 0, 132ms
    다음 vg 행 반환 2 :v2, 807, 132ms

    section ar subtree
    해당 ar 조회 1 :a1, 132, 667ms
    해당 ar 조회 2 :a2, 939, 667ms

    section 부모 iterator 비용
    루프 제어·결과 전달 1 :j1, 799, 8ms
    루프 제어·결과 전달 2 :j2, 1606, 8ms

그림 2. 실측 누적 시간을 호출당 평균으로 환산해 100,000배 확대한 Nested loop 처리 과정(시뮬레이션). 개별 호출의 실제 시작·종료 시각을 재현한 것은 아니다.

3단계: 병목 식별

10,000건의 후보가 만든 반복 조회

도식화된 멘탈 모델을 바탕으로 다시 표 3을 살펴본다. Nested loop inner join 노드의 rows 수는 700이고, visibility_grants Filter 노드의 rows 수는 10,000이다. 즉, 10,000을 꺼내서 조인을 했지만 결국 반환하게 되는 rows 수는 비효율적인 700건에 불과하다.

이 비효율성도 attendance_records Filter의 rows 수에서도 드러난다. 0.07에 10,000을 곱한 결과값인 ~700이기 때문이다. 다시 말해 후보는 많았지만 조건에 맞는 당선자는 적었기 때문에 이 후보를 좁히는 것이 핵심이 될 수 있다는 것을 파악할 수 있다.

CAST로 드러난 조인 키의 타입 불일치

오른쪽 노드의 ICP 조건에서는 드라이빙 테이블의 VARCHAR 컬럼인 vg.target_id와 드리븐 테이블의 BIGINT 컬럼인 ar.employee_id를 DOUBLE로 타입 변환하게 된다. 찾아보기 쉽게 해당 노드를 발췌해서 아래에 변경 없이 가져왔다.

-> Index lookup on ar using idx_attendance_records_tenant_employee (tenant_id=1, employee_id=vg.target_id), with index condition: (cast(vg.target_id as double) = cast(ar.employee_id as double))  (cost=2.49 rows=9.93) (actual time=0.00521..0.00592 rows=10 loops=10000)

개인적으로 주목했던 부분은 여기 (tenant_id=1, employee_id=vg.target_id)에서 employee_id가 인덱스 lookup으로 활용된 부분이다. 지금까지 CAST가 발생하는 컬럼은 인덱스로 활용할 수 없다고 이해해왔기 때문이다. 하지만 이는 반쪽짜리 설명임을 노드 분석을 통해 깨달을 수 있었다.

BIGINT가 인덱스 컬럼으로 있을 때 VARCHAR 외부 값을 숫자 탐색 키 1개로 변환해서 이 실행계획에서는 BIGINT 인덱스 lookup이 가능했다. 즉, 매 loop마다 구체적인 vg.target_id 값이 하나 주어지므로 ar 인덱스 탐색 키를 만들 수 있다. 반대로 BIGINT 외부 값으로 VARCHAR 인덱스를 탐색해야 한다면 무슨 일이 발생할까? 타입 변환을 다루는 MySQL 공식 문서에서 아래 인용문을 확인할 수 있다.

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly.⁷

숫자 1로 변환될 수 있는 문자열은 '1', ' 1', '1a'처럼 여러 개이므로 BIGINT 값 하나만으로 VARCHAR 인덱스의 단일 탐색 키를 정할 수 없다. 즉, BIGINT 탐색 키를 가진 ar을 드라이빙 테이블로 두고 기존 VARCHAR 타입인 vg.target_id 문자열 인덱스를 lookup하려는 경우가 이 제약에 해당한다. 참고로 빠른 index lookup이 어렵더라도 옵티마이저가 index scan을 선택할 수 있으며, 필요한 컬럼이 모두 포함된 covering index라면 테이블 접근 없이 처리할 수도 있다.

4단계: 타입을 맞추고 조인 방향을 뒤집기

개선 방향: 반복 조회의 출발점 바꾸기

3단계에서 확인한 병목은 visibility_grants에서 10,000건의 권한 후보를 먼저 읽고, 그 결과로 attendance_records를 10,000번 반복 조회하는 구조였다. 최종 결과는 700건이었으므로, 개선 방향은 권한 후보를 먼저 넓게 읽는 것이 아니라 조회 범위가 작은 근태 기록 700건을 먼저 좁히는 것이었다.

기존 구조를 단순화하면 다음과 같다.

변경 전:
visibility_grants 10,000건
  -> attendance_records 10,000 loops
  -> 최종 결과 700건

조회 범위가 작은 테이블을 먼저 읽도록 만들기

개선 방향은 명확했다. attendance_records에서 조회 범위인 team-a 근태 기록 700건을 먼저 좁히고, 각 근태 기록에 대해 권한이 존재하는지만 빠르게 확인할 수 있어야 했다.

그러려면 visibility_grants에서도 직원 ID를 숫자 타입으로 탐색할 수 있어야 한다. 기존 target_id는 VARCHAR였기 때문에, attendance_records.employee_id를 기준으로 visibility_grants.target_id 문자열 인덱스를 빠르게 lookup하기 어려웠다.

기존 target_id 컬럼은 그대로 두었다. 이 컬럼은 여러 target_type을 담기 위한 폴리모픽 구조였기 때문이다. 대신 직원 대상 권한에 대해서만 사용할 수 있는 BIGINT 컬럼을 추가했다.

ALTER TABLE visibility_grants
  ADD COLUMN target_id_num BIGINT NULL AFTER target_id;

UPDATE visibility_grants
   SET target_id_num = CAST(target_id AS SIGNED)
 WHERE target_type = 'EMPLOYEE'
   AND target_id_num IS NULL;

CREATE INDEX idx_visibility_grants_type_target
  ON visibility_grants (tenant_id, permission_type, target_id_num, subject_id);

target_id_num IS NULL 조건을 함께 둔 이유는 백필 쿼리를 재실행해도 이미 채워진 값을 다시 건드리지 않기 위해서다.

조인 조건은 한 줄만 바꾼다

스키마와 인덱스를 준비한 뒤, 쿼리의 조인 조건은 한 줄만 변경했다.

-- 변경 전
AND vg.target_id = ar.employee_id

-- 변경 후
AND vg.target_id_num = ar.employee_id

겉으로 보면 작은 변경처럼 보인다. 하지만 실행계획 관점에서는 의미가 크다. 이제 visibility_grants는 문자열 컬럼이 아니라 숫자 컬럼인 target_id_num으로 직원 ID를 탐색할 수 있다.

즉, attendance_records를 먼저 읽고, 각 근태 기록의 employee_id에 대응되는 권한을 visibility_grants에서 바로 찾아갈 수 있는 길이 열린다.

개선 후 실행계획

변경 후 실행계획은 다음과 같다.

-> Table scan on <temporary>  (cost=210..211 rows=3.5) (actual time=2.48..2.52 rows=700 loops=1)
    -> Temporary table with deduplication  (cost=209..209 rows=3.5) (actual time=2.48..2.48 rows=700 loops=1)
        -> Nested loop inner join  (cost=209 rows=3.5) (actual time=0.0792..2.39 rows=700 loops=1)
            -> Filter: (ar.deleted_at is null)  (cost=182 rows=70) (actual time=0.0484..0.441 rows=700 loops=1)
                -> Index lookup on ar using idx_attendance_records_tenant_team (tenant_id=1, team_code='team-a')  (cost=182 rows=700) (actual time=0.048..0.407 rows=700 loops=1)
            -> Limit: 1 row(s)  (cost=0.271 rows=0.05) (actual time=0.00261..0.00263 rows=1 loops=700)
                -> Filter: ((vg.target_type = 'EMPLOYEE') and (vg.subject_type = 'EMPLOYEE') and (vg.started_at <= <cache>(now())) and (vg.ended_at > <cache>(now())))  (cost=0.271 rows=0.05) (actual time=0.00253..0.00253 rows=1 loops=700)
                    -> Index lookup on vg using idx_visibility_grants_type_target (tenant_id=1, permission_type='ATTENDANCE_READ', target_id_num=ar.employee_id, subject_id='1')  (cost=0.271 rows=1.08) (actual time=0.00223..0.00223 rows=1 loops=700)

드라이빙 테이블이 바뀌었다

가장 먼저 볼 지점은 드라이빙 테이블이 바뀌었다는 점이다.

변경 전에는 visibility_grants가 먼저 읽혔다. 요청자의 활성 권한 10,000건을 가져온 뒤, 각 권한 대상자에 대해 attendance_records를 반복 조회했다.

변경 후에는 attendance_records가 먼저 읽힌다. idx_attendance_records_tenant_team 인덱스를 이용해 tenant_id = 1이고 team_code = 'team-a'인 근태 기록 700건을 먼저 좁힌다.

변경 후:
attendance_records 700건
  -> visibility_grants 700 loops
  -> 최종 결과 700건

반복 횟수가 10,000번에서 700번으로 줄었다. 이 변화가 실행시간 감소의 핵심이다.

권한 테이블을 정확한 키로 조회한다

visibility_grants 조회 방식도 달라졌다. 개선 후 실행계획에서는 다음 조건들이 인덱스 lookup 키로 함께 사용된다.

• tenant_id=1,
• permission_type=‘ATTENDANCE_READ’,
• target_id_num=ar.employee_id,
• subject_id=‘1’

변경 전에는 권한 테이블에서 넓은 후보를 읽은 뒤 필터링하는 구조에 가까웠다. 변경 후에는 특정 직원에 대한 특정 요청자의 권한을 바로 찾아가는 구조가 되었다.

실행계획에 Limit: 1 row(s)가 나타나는 것도 이 변화와 연결된다. 권한 조회에서는 조건을 만족하는 권한이 존재하는지만 확인하면 충분하다. 따라서 한 건을 찾으면 더 이상 같은 대상자의 권한 후보를 읽을 필요가 없다.

이 쿼리는 최종적으로 SELECT DISTINCT ar.id만 반환하므로, 같은 근태 기록에 대해 여러 권한 행이 매칭되더라도 결과는 하나로 합쳐진다. 따라서 옵티마이저는 조건을 만족하는 권한 행이 존재하는지만 확인하는 형태의 실행계획을 선택할 수 있다.

개선 결과

결과적으로 조회 구조는 다음처럼 바뀌었다.

변경 전:
visibility_grants 10,000건
  -> attendance_records 10,000 loops

변경 후:
attendance_records 700건
  -> visibility_grants 700 loops

실행 시간은 80.8ms에서 2.52ms로 줄었다. 약 32배 개선된 결과다. 이번 개선은 단순히 인덱스 추가를 넘어, 타입을 맞추면서 옵티마이저가 더 나은 조인 방향을 선택할 수 있게 했다. 그 결과 반복 조회의 규모 자체가 줄어든 것이다.

마치며

이번 사례에서 얻은 교훈은 세 가지다.

1. 실행계획은 actual time만 보면 부족하다. 반복 실행되는 노드는 rows와 loops를 함께 봐야 실제 비용이 보인다.

2. 타입 불일치는 단순히 CAST 비용만 만드는 문제가 아니다. 조인 방향과 인덱스 탐색 가능성 자체를 제한할 수 있다.

3. LLM이 슬로우 쿼리 개선안을 제안할 수는 있지만, 그 제안이 맞는지 판단하려면 사람이 실행계획을 읽을 수 있어야 한다. 자동화는 수동 분석을 대체한다기보다, 수동 분석 위에 올라가는 계층에 가깝다.

부록

A. 단계별 수행시간 계산 결과 테이블

참고한 자료