インデックスを作り計画が変わるか確かめる

한국어 원문으로 표시합니다.

목표

여러 종류의 인덱스를 직접 만들고, 실행 계획이 어떻게 달라지는지 눈으로 확인합니다. 인덱스를 만드는 법보다 인덱스가 언제 쓰이고 언제 무시되는지를 아는 것이 이 실습의 목적입니다.

왜 중요한가

인덱스를 만들었는데 계획이 그대로일 때 가장 흔한 조언은 강제로 태우라는 것입니다. 이것은 진단이 아니라 증상 억제이고, 강제로 태우면 오히려 느려지는 경우가 실제로 많습니다. 인덱스 스캔은 매칭된 행마다 힙 페이지를 랜덤하게 방문하므로, 반환할 행이 많으면 표 전체를 랜덤 순서로 읽는 꼴이 되기 때문입니다.

그래서 인덱스가 안 탈 때 던져야 할 질문은 두 가지로 갈립니다. 옵티마이저가 옳아서 안 타는 것인가, 내 쿼리가 인덱스를 쓸 수 없는 형태여서 못 타는 것인가. 전자는 인덱스를 바꾸거나 포기해야 하고 후자는 쿼리를 고쳐야 합니다. 대응이 정반대이므로 구분이 먼저입니다.

이 실습에서는 부분 인덱스와 표현식 인덱스, 커버링 인덱스까지 만들어 보며 "인덱스는 하나가 아니라 여러 종류"라는 감각을 얻습니다.

단계

EXPLAIN SELECT count(*) FROM orders WHERE channel = 'partner'; 의 출력을 /root/exp 가 아니라 /root/plan_seq.txt 에 저장합니다. 순차 스캔이 나와야 합니다.
orders (ordered_at) 에 idx_orders_ordered_at 이라는 이름의 B 트리 인덱스를 만듭니다.
ordered_at 에 범위 조건을 건 조회의 EXPLAIN 출력을 /root/plan_idx.txt 에 저장합니다. 계획에 idx_orders_ordered_at 이 나와야 합니다.
order_items 에 idx_order_items_order_product 라는 이름으로 (order_id, product_id) 순서의 복합 인덱스를 만듭니다.
orders (ordered_at) 에 status = 'pending' 조건을 붙인 부분 인덱스 idx_orders_pending 을 만듭니다.
customers 에 lower(email) 에 대한 표현식 인덱스 idx_customers_email_lower 를 만듭니다.
products 에 키 컬럼은 (category, price), INCLUDE 컬럼은 name 인 커버링 인덱스 idx_products_cat_price 를 만듭니다.
순차 스캔을 끈 상태에서 ordered_at 조건으로 count(*) 를 구하는 EXPLAIN 출력을 /root/plan_only.txt 에 저장합니다. Index Only Scan 이 나와야 합니다.

참고

인덱스 정의 확인: \d orders 또는 SELECT indexdef FROM pg_indexes WHERE tablename='orders';
인덱스 크기 비교: SELECT pg_size_pretty(pg_relation_size('인덱스이름'));
순차 스캔 잠시 끄기: SET enable_seqscan = off; — 진단용으로만 씁니다. 운영 설정으로 두면 안 됩니다.
흔한 실수 1: 인덱스 이름을 다르게 지으면 채점되지 않습니다. 지문의 이름을 그대로 쓰세요.
흔한 실수 2: 표현식 인덱스는 쿼리가 정확히 같은 표현식을 써야 탑니다. lower(email) 인덱스는 lower(trim(email)) 조건에는 쓰이지 않습니다.

인덱스가 없을 때의 계획 남기기

EXPLAIN SELECT count(*) FROM orders WHERE channel = 'partner'; 의 출력을 /root/exp 가 아니라 /root/plan_seq.txt 에 저장합니다. 순차 스캔이 나와야 합니다.

EXPLAIN 은 쿼리 앞에 붙이기만 하면 됩니다. 아직 인덱스가 없는 컬럼을 조건으로 써야 순차 스캔이 나옵니다.

기본 B 트리 인덱스 만들기

orders (ordered_at) 에 idx_orders_ordered_at 이라는 이름의 B 트리 인덱스를 만듭니다.

CREATE INDEX 이름 ON 테이블 (컬럼) 형태입니다. 인덱스 이름을 정확히 맞춰야 채점됩니다.

인덱스를 타는 계획 남기기

ordered_at 에 범위 조건을 건 조회의 EXPLAIN 출력을 /root/plan_idx.txt 에 저장합니다. 계획에 idx_orders_ordered_at 이 나와야 합니다.

범위가 좁은 조건을 걸어야 옵티마이저가 인덱스를 고릅니다. 컬럼에 함수를 씌우면 인덱스를 못 씁니다.

복합 인덱스의 컬럼 순서 정하기

order_items 에 idx_order_items_order_product 라는 이름으로 (order_id, product_id) 순서의 복합 인덱스를 만듭니다.

등호 조건으로 자주 쓰이는 컬럼이 앞에 와야 합니다. 순서가 바뀌면 다른 인덱스입니다.

부분 인덱스로 크기 줄이기

orders (ordered_at) 에 status = 'pending' 조건을 붙인 부분 인덱스 idx_orders_pending 을 만듭니다.

CREATE INDEX ... WHERE 조건 형태로 일부 행만 담을 수 있습니다. 크기를 비교해 보세요.

표현식 인덱스 만들기

customers 에 lower(email) 에 대한 표현식 인덱스 idx_customers_email_lower 를 만듭니다.

컬럼이 아니라 계산 결과로 인덱스를 만들 수 있습니다. 괄호로 감싸야 하고, 쿼리도 똑같은 표현식을 써야 탑니다.

커버링 인덱스로 힙 방문 줄이기

products 에 키 컬럼은 (category, price), INCLUDE 컬럼은 name 인 커버링 인덱스 idx_products_cat_price 를 만듭니다.

탐색에 쓰이는 키 컬럼과, 결과에만 필요한 컬럼을 구분해 정의할 수 있는 절이 있습니다.

Index Only Scan 을 관찰하기

순차 스캔을 끈 상태에서 ordered_at 조건으로 count(*) 를 구하는 EXPLAIN 출력을 /root/plan_only.txt 에 저장합니다. Index Only Scan 이 나와야 합니다.

표가 작으면 옵티마이저가 순차 스캔을 고릅니다. 진단 목적으로 순차 스캔을 잠시 막고 계획을 다시 뽑아 보세요.