느린 SELECT 쿼리, EXPLAIN ANALYZE로 원인 찾는 법 — 실행계획 읽기 가이드

작년까지 멀쩡하던 목록 API가 어느 날부터 8초씩 걸린다. 코드는 한 줄도 안 바꿨는데 말이다. 이쯤 되면 팀 채팅에 "DB가 느려요"라는 말이 올라오고, 누군가는 서버 사양 얘기를 꺼낸다. 그런데 정작 느린 쿼리 하나를 열어 실행계획을 뽑아보면, 원인은 서버가 아니라 그 쿼리가 테이블을 통째로 훑고 있었다는 데 있는 경우가 대부분이다.

문제는 이 "통째로 훑는다"를 눈으로는 볼 수 없다는 점이다. SQL만 봐서는 빠른지 느린지 알 수 없고, 데이터가 1만 건일 때와 500만 건일 때 완전히 다르게 동작한다. 그래서 DB가 실제로 어떤 경로로 데이터를 꺼내는지 보여주는 도구가 EXPLAIN 이다.

EXPLAIN과 EXPLAIN ANALYZE는 뭐가 다른가

둘 다 실행계획을 보여주지만 결정적인 차이가 하나 있다. 실제로 쿼리를 돌리느냐 마느냐다.

EXPLAIN 은 쿼리를 실행하지 않고 플래너(planner)가 세운 계획만 보여준다. 예상 비용, 예상 행 수 같은 추정치다. 빠르고 안전하지만 어디까지나 "예상"이다.

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

EXPLAIN ANALYZE 는 쿼리를 실제로 실행한 뒤 걸린 시간과 실제 행 수를 함께 보여준다. 추정과 현실을 나란히 놓고 비교할 수 있어서, 진짜 원인을 찾을 때는 이쪽을 쓴다.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

여기서 한 가지 주의. ANALYZE는 쿼리를 정말로 돌린다. SELECT야 괜찮지만 UPDATE·DELETE 에 붙이면 데이터가 실제로 바뀐다. 운영 DB에서 변경 쿼리를 분석할 땐 트랜잭션으로 감싸고 마지막에 ROLLBACK 하는 습관을 들이는 게 안전하다. I/O가 의심되면 EXPLAIN (ANALYZE, BUFFERS) 로 디스크를 얼마나 읽었는지까지 볼 수 있다.

실행계획, 어디부터 읽어야 하나

처음 실행계획을 보면 외계어 같다. 읽는 순서만 알면 절반은 끝난다. 계획은 가장 깊이 들여쓰기된 안쪽 노드부터 바깥으로, 아래에서 위로 읽는다.

Seq Scan on orders  (cost=0.00..18334.00 rows=1 width=67)
                    (actual time=0.015..82.400 rows=1 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 999999
Planning Time: 0.1 ms
Execution Time: 82.5 ms

• Seq Scan: 테이블을 처음부터 끝까지 순차적으로 다 읽었다는 뜻이다. 100만 건짜리 테이블에서 한 건 찾자고 전부 훑은 것이다. 반대로 Index Scan 이 뜨면 인덱스로 바로 찾아갔다는 신호다.
• cost: 플래너의 예상 비용이다. 시작비용..총비용 형태이고, 밀리초가 아니라 상대적인 점수다. 절대값보다 노드끼리 비교하는 용도로 본다.
• rows=1(추정) vs actual rows=1(실제): 이 둘이 크게 벌어지면 통계가 틀렸다는 신호다. 추정이 1인데 실제가 50만이면 플래너가 엉뚱한 계획을 고른다.
• actual time: 실제 걸린 시간(밀리초)이다. loops 가 여러 번이면 시간 × loops가 그 노드의 총합이다. 이 값이 큰 노드가 병목이다.
• Rows Removed by Filter: 999999: 99만 건을 읽어서 버렸다는 자백이다. 인덱스가 있었다면 이 숫자가 0에 가까웠을 것이다.

느린 쿼리, 범인은 대개 이 다섯

실행계획을 몇 번 뜯어보면 반복되는 패턴이 보인다.

1. 인덱스가 없다. 가장 흔하다. WHERE·JOIN·ORDER BY에 자주 쓰는 컬럼에 인덱스가 없으면 Seq Scan으로 직행한다.
2. 인덱스를 무효화하는 조건. 인덱스가 있어도 컬럼을 함수나 형변환으로 감싸면 못 쓴다. WHERE DATE(created_at) = '2026-01-01' 이나 WHERE user_id::text = '42' 가 대표적이다. 컬럼은 가공하지 말고 상수 쪽을 맞춰야 한다 — created_at >= '2026-01-01' AND created_at < '2026-01-02' 처럼.
3. 앞에 와일드카드가 붙은 LIKE. LIKE '%검색어' 는 B-tree 인덱스를 못 탄다. 뒤쪽 와일드카드('검색어%')는 탈 수 있다. 앞 매칭이 꼭 필요하면 트라이그램(pg_trgm) 인덱스나 전문검색을 쓴다.
4. N+1. 실행계획 하나하나는 멀쩡한데 전체가 느린 경우다. 목록 100건을 그리면서 건마다 상세 쿼리를 날리는 패턴이다. 단일 쿼리로는 안 보이고, 쿼리 로그에 같은 모양의 쿼리가 수백 번 찍히는 걸로 드러난다. JOIN이나 IN으로 묶어야 한다.
5. 통계가 낡았다. 대량 INSERT 직후처럼 데이터 분포가 확 바뀌면 플래너의 추정이 현실과 어긋난다. 앞서 말한 추정 rows와 actual rows가 크게 벌어져 있으면 이 경우를 의심한다.

그래서 어떻게 고치나

원인별로 처방은 의외로 단순하다.

인덱스가 없으면 만든다. 자주 함께 조건에 걸리는 컬럼들은 복합 인덱스로 묶는다.

CREATE INDEX idx_orders_user_id ON orders (user_id);

함수로 감싸야만 하는 조건이라면 표현식(functional) 인덱스를 만들거나, 위에서처럼 범위 조건으로 쿼리를 다시 쓴다. 통계가 낡았다면 수동으로 갱신한다.

ANALYZE orders;

인덱스를 추가한 뒤에는 반드시 다시 EXPLAIN ANALYZE 를 돌려 Seq Scan이 Index Scan으로 바뀌었는지, actual time이 실제로 줄었는지 확인한다. 인덱스를 만들었는데도 Seq Scan이 그대로라면, 테이블이 너무 작아서 전체 스캔이 더 싸다고 판단했거나 조건이 여전히 인덱스를 무효화하고 있다는 뜻이다.

여기서 현실적인 이야기를 하나 덧붙이고 싶다. 국내 실무에서 마주치는 느린 쿼리의 상당수는 "인덱스를 안 만들어서"가 아니라 "만들어둔 인덱스를 조건이 못 타게 짜서" 생긴다. 특히 ORM을 쓰면 날짜를 DATE() 로 자르거나 숫자 컬럼에 문자열을 비교하는 코드가 무심코 나가기 쉽다. 그래서 느린 API를 받으면 서버 사양부터 의심하기 전에 그 쿼리에 EXPLAIN ANALYZE 를 먼저 붙여보는 편이다 — 열에 아홉은 Seq Scan과 거대한 Rows Removed by Filter가 범인으로 나온다. 그리고 인덱스는 공짜가 아니라는 점도 기억하면 좋다. 읽기는 빨라지지만 쓰기마다 갱신 비용이 붙고 저장공간도 먹으니, 조회가 거의 없는 컬럼에 습관적으로 다는 건 오히려 손해다. 어디에 걸어야 할지는 결국 실행계획이 알려준다.

MySQL에도 EXPLAIN 과 EXPLAIN ANALYZE(8.0.18 이상)가 있는데, 출력이 표 형태이고 type 컬럼(ALL이 풀스캔, ref·range가 인덱스 사용)으로 읽는 방식이 조금 다를 뿐 원리는 같다.

체크 요약

• 느린 쿼리는 추측하지 말고 EXPLAIN ANALYZE 로 실행계획부터 뜬다.
• Seq Scan + 큰 Rows Removed by Filter = 인덱스 후보.
• 추정 rows와 actual rows 차이가 크면 ANALYZE 로 통계를 갱신한다.
• 컬럼을 함수·형변환으로 감싸면 인덱스가 죽는다. 상수 쪽을 맞춘다.
• 고친 뒤 다시 실행계획을 떠서 정말 빨라졌는지 눈으로 확인한다.

느린 쿼리를 잡는 건 감이 아니라 독해다. 실행계획을 읽을 줄 알게 되면 "DB가 느려요"라는 막연한 말이 "이 쿼리의 이 조건이 인덱스를 못 타고 있어요"라는 구체적인 진단으로 바뀐다. 여러분은 느린 쿼리를 만나면 가장 먼저 무엇을 확인하는가? EXPLAIN 말고 자주 쓰는 진단 루틴이 있다면 댓글로 공유해 달라.