인덱스를 만들고 계획이 바뀌는지 확인하기

목표

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

왜 중요한가

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

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

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

단계

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

단계 8개

1. 인덱스가 없을 때의 계획 남기기
2. 기본 B 트리 인덱스 만들기
3. 인덱스를 타는 계획 남기기
4. 복합 인덱스의 컬럼 순서 정하기
5. 부분 인덱스로 크기 줄이기
6. 표현식 인덱스 만들기
7. 커버링 인덱스로 힙 방문 줄이기
8. Index Only Scan 을 관찰하기