PostgreSQL이 인덱스 두고 Seq Scan 고르는 이유, 숫자로 따라가기
고객 문의 티켓을 분류별로 모아 보는 관리 화면을 만들던 때였다. 상담팀이 분류 하나를 고르면 그 분류의 티켓을 최근 순으로 띄우는 단순한 화면이다. category_id에 인덱스를 걸고 EXPLAIN을 돌렸더니 첫 줄에 Seq Scan on tickets가 찍혀 있었다. 인덱스 이름은 계획 어디에도 없었다.
처음엔 통계가 낡았거나 인덱스가 망가졌다고 생각했다. ANALYZE를 다시 돌리고 인덱스를 지웠다가 새로 만들었다. 결과는 그대로였다. 분류는 스무 개였고 한 분류가 전체의 5% 남짓이었다. 5%면 당연히 인덱스를 탈 거라고 믿었는데 PostgreSQL은 다르게 셈하고 있었다.
그 셈을 숫자로 따라가 본 기록이다. 인덱스를 두고 나오는 Seq Scan은 대개 플래너의 실수가 아니라 계산 결과다.
플래너가 비교하는 건 비용 숫자다
플래너는 가능한 실행 경로마다 비용을 매기고 가장 싼 것을 고른다. 비용 단위는 밀리초가 아니다. 페이지 한 장을 순서대로 읽는 일을 1로 놓은 상대값이다. 기준이 되는 상수와 기본값은 다음과 같다.
| 파라미터 | 기본값 | 뜻 |
|---|---|---|
seq_page_cost | 1.0 | 순서대로 읽는 페이지 한 장 |
random_page_cost | 4.0 | 건너뛰어 읽는 페이지 한 장 |
cpu_tuple_cost | 0.01 | 행 하나 처리 |
cpu_index_tuple_cost | 0.005 | 인덱스 항목 하나 처리 |
cpu_operator_cost | 0.0025 | 연산자나 함수 한 번 실행 |
눈여겨볼 값은 random_page_cost가 seq_page_cost의 네 배라는 점이다. 인덱스 스캔은 인덱스에서 찾은 위치를 따라 테이블 페이지를 여기저기 건너뛰며 읽는다. 플래너는 그렇게 읽는 한 장을 순서대로 읽는 한 장보다 네 배 비싸게 본다.
따라 해 볼 수 있게 비슷한 테이블을 만드는 스크립트를 붙인다. 행 너비가 환경마다 달라서 페이지 수는 아래 계산과 조금 다르게 나온다.
CREATE TABLE tickets (
id bigserial PRIMARY KEY,
category_id int NOT NULL,
created_at timestamptz NOT NULL,
body text
);
INSERT INTO tickets (category_id, created_at, body)
SELECT i % 20 + 1,
now() - (1000000 - i) * interval '1 minute',
repeat('x', 40)
FROM generate_series(1, 1000000) AS i;
CREATE INDEX ON tickets (category_id);
CREATE INDEX ON tickets (created_at);
ANALYZE tickets;Seq Scan 비용은 선택도와 상관없다
테이블 크기는 pg_class에서 본다.
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tickets';계산을 쉽게 하려고 행 100만 건, 페이지 12,500장이라고 하자. Seq Scan 비용은 페이지 수에 seq_page_cost를 곱한 값과 행 수에 cpu_tuple_cost를 곱한 값을 더한 것이다. WHERE 조건이 있으면 행마다 비교 한 번 몫인 cpu_operator_cost가 붙는다.
12,500 × 1.0 + 1,000,000 × 0.01 + 1,000,000 × 0.0025
= 12,500 + 10,000 + 2,500
= 25,000이 식에는 조건이 몇 건을 걸러 내는지가 들어 있지 않다. 1건을 찾든 50만 건을 찾든 Seq Scan은 25,000이다. 기준선은 고정돼 있고, 인덱스 쪽 비용이 이 선을 넘느냐 마느냐가 관건이다.
5%가 생각보다 비싼 이유
인덱스 스캔 비용은 선택도를 따라 올라간다. 5%면 5만 건이다. 그런데 비용을 정하는 건 행 수보다 그 행들이 흩어져 있는 페이지 수다.
값이 테이블 안에 뒤섞여 있으면 플래너는 Mackert와 Lohman이 제안한 근사식으로 읽을 페이지 수를 추정한다. 테이블이 effective_cache_size 안에 들어간다고 볼 때 식은 min(2TN / (2T + N), T)다. T는 테이블 페이지 수, N은 가져올 행 수다.
2 × 12,500 × 50,000 / (2 × 12,500 + 50,000) ≈ 16,667
min(16,667, 12,500) = 12,5005만 건을 가져오려면 테이블의 모든 페이지를 한 번씩 건드린다는 추정이다. 페이지 한 장에 행이 80개쯤 있고 원하는 행이 스무 개 중 하나꼴로 섞여 있으니, 거의 모든 페이지에 원하는 행이 네 개씩은 들어 있다. 그 12,500장을 이번에는 랜덤 읽기 값으로 센다.
12,500 × 4.0 = 50,000 (테이블 I/O만)인덱스 항목과 행 처리 비용 900 남짓을 더하기도 전에 Seq Scan의 두 배다. 같은 페이지를 같은 횟수만큼 읽는데 읽는 방식 때문에 값이 네 배로 매겨진 셈이다. 이 계산을 처음 손으로 해 봤을 때 5%라는 숫자가 별 의미가 없다는 걸 알았다. 볼 것은 행의 비율이 아니라 페이지의 비율이었다.
중간에 Bitmap Heap Scan이라는 선택지도 있다. 비트맵 스캔은 읽을 페이지를 물리 순서로 정렬해서 읽기 때문에, 건드리는 페이지가 늘수록 한 장당 비용이 순차 읽기 쪽으로 내려간다. 그래도 거의 모든 페이지를 읽어야 하면 인덱스를 훑는 비용까지 얹혀 Seq Scan을 이기기 어렵다.
correlation이 계산을 뒤집는다
같은 5%라도 행이 한쪽에 몰려 있으면 이야기가 달라진다. 플래너는 이를 pg_stats의 correlation으로 판단한다. 컬럼 값의 논리 순서와 행의 물리 순서가 얼마나 일치하는지를 -1에서 +1 사이로 나타낸 값이다.
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'tickets'
AND attname IN ('category_id', 'created_at');
costsize.c의 cost_index 주석에 이 부분이 잘 풀려 있다. 완전히 정렬된 경우 읽을 페이지는 선택도 × 테이블 페이지 수이고, 첫 장만 랜덤이며 나머지는 순차로 읽는다.
페이지 수 = 0.05 × 12,500 = 625
min_IO = 4.0 + 624 × 1.0 = 628플래너는 뒤섞인 경우의 50,000과 정렬된 경우의 628 사이를 correlation의 제곱으로 선형 보간한다.
I/O 비용 = max_IO + correlation² × (min_IO - max_IO)correlation이 0.9면 제곱이 0.81이라 약 10,009가 되고, 0.5면 0.25라 37,657이 된다. 제곱을 쓰기 때문에 어중간한 상관으로는 큰 덕을 보지 못한다.
티켓 테이블에서 created_at의 correlation은 0.99를 넘었다. 티켓은 생긴 순서대로 쌓이니 당연하다. 그래서 최근 일주일 조회는 인덱스를 잘 탔다. category_id는 0에 가까웠다. 한 테이블에서 한 인덱스는 쓰이고 다른 하나는 외면당한 이유가 이 숫자 하나에 있었다.
enable_seqscan=off로 강제해 봤다
그래도 플래너를 못 믿겠어서 세션에서 Seq Scan을 꺼 봤다.
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tickets WHERE category_id = 3;
RESET enable_seqscan;계획은 Bitmap Heap Scan으로 바뀌었고 실행 시간은 0.4초에서 1.7초로 늘었다. BUFFERS를 보니 건드린 테이블 페이지 수는 Seq Scan과 거의 같았고, 그 위에 인덱스 페이지까지 얹혀 있었다. 허탈했지만 플래너가 옳았다는 확신은 생겼다.
이 설정은 Seq Scan을 금지하는 스위치가 아니다. 공식 문서도 순차 스캔을 완전히 막을 수는 없고 다른 방법이 있을 때 쓰지 않도록 유도할 뿐이라고 적어 두었다. 예전 버전은 꺼 둔 노드에 100억이라는 벌점 비용을 더했다. PostgreSQL 18부터는 꺼 둔 노드 수를 따로 세서 그 수가 적은 계획을 먼저 고르고, 어쩔 수 없이 쓴 노드에는 EXPLAIN에서 Disabled: true를 붙인다. 어느 버전이든 실험용이지 운영 설정에 넣을 값은 아니다.
반대 방향으로 손댔다가 낭패를 본 적도 있다. 디스크가 SSD라는 이유만 보고 random_page_cost를 1.1로 내렸다. 그러면 위 계산의 50,000은 13,750으로 떨어지고, 5% 조회도 인덱스 쪽이 25,000보다 싸 보인다. 낮 시간 화면 조회는 데이터가 메모리에 올라와 있어서 빨라졌다. 그런데 캐시에 없는 오래된 데이터를 훑는 새벽 통계 배치가 인덱스를 타면서 오히려 느려졌다. 공식 문서도 이 값을 낮추는 근거로 디스크 종류보다 데이터가 캐시에 얼마나 올라와 있는지를 먼저 든다.
결제 테이블에서 다시 만났을 때
올봄 결제 내역 테이블에서 같은 증상을 다시 봤다. 이번에는 인덱스부터 의심하지 않고 pg_stats에서 그 컬럼의 correlation과 값 분포를 먼저 열었다. correlation은 0에 가까웠고, 위처럼 대충 손으로 셈해 보니 플래너와 같은 결론이 나왔다. 그래서 그대로 뒀다. 거기서 인덱스를 억지로 태워 봐야 같은 페이지를 더 비싸게 읽을 뿐이다.
정말 빨라야 하는 화면이면 인덱스보다 질문을 고친다. 티켓 화면도 결국 분류별 최근 20건만 보여 주면 되는 일이었다. (category_id, created_at DESC) 복합 인덱스를 만들고 ORDER BY created_at DESC LIMIT 20을 붙이자 5만 건이 아니라 20건만 읽게 됐다. CLUSTER로 물리 순서를 맞추는 방법도 생각했지만 한 번 정렬할 뿐이고 이후 들어오는 행은 순서를 따르지 않는다. 정렬하는 동안 테이블도 잠긴다.
random_page_cost는 캐시 적중률을 확인한 다음에야 만진다. 티켓 화면 때 밤늦게 cost 숫자를 계산기에 두드려 보던 게 버릇으로 남았다. 플래너와 싸우기 전에 플래너와 같은 계산을 한 번 해 본다. 대부분은 거기서 끝난다.