Create an Index and Check Whether the Plan Changes

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

목표

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

왜 중요한가

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

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

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

단계

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 이 나와야 합니다.

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