인덱스를 만들었는데 안 타는 이유 — 옵티마이저가 맞고 내가 틀린 경우들

-- 옵티마이저의 선택
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';

Seq Scan on orders  (cost=0.00..20117.00 rows=612430 width=88)
                    (actual time=0.014..184.221 rows=611884 loops=1)
  Filter: (status = 'completed')
  Rows Removed by Filter: 188116
  Buffers: shared hit=1211 read=10906
Execution Time: 231.774 ms

-- 인덱스를 강제한 결과
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';
RESET enable_seqscan;

Index Scan using idx_orders_status on orders
  (cost=0.42..68914.33 rows=612430 width=88)
  (actual time=0.061..1042.883 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Buffers: shared hit=8842 read=598112
Execution Time: 1094.201 ms

-- SSD 환경의 현실적인 값
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('created_at', 'user_id', 'status');

  attname   | n_distinct | correlation
------------+------------+-------------
 created_at |         -1 |    0.998304
 user_id    |      48210 |    0.003911
 status     |          6 |    0.412008

-- INCLUDE로 커버링 인덱스를 만든다 (PostgreSQL 11 이상)
CREATE INDEX CONCURRENTLY idx_orders_status_covering
  ON orders (status) INCLUDE (id, total, created_at);

Index Only Scan using idx_orders_status_covering on orders
  (actual time=0.038..142.118 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Heap Fetches: 1204

-- 안 탄다: 컬럼에 함수가 걸렸다
SELECT * FROM orders WHERE date(created_at) = '2026-07-26';

-- 탄다: 범위 조건으로 바꿔 컬럼을 그대로 둔다
SELECT * FROM orders
WHERE created_at >= '2026-07-26'
  AND created_at <  '2026-07-27';

-- 안 탄다: 컬럼 쪽에 연산
SELECT * FROM order_items WHERE price * quantity > 100000;

-- 탄다: 생성 컬럼이나 표현식 인덱스로 옮긴다
CREATE INDEX idx_items_amount ON order_items ((price * quantity));

CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 탄다
SELECT * FROM users WHERE lower(email) = 'a@example.com';

-- 안 탄다: 표현식이 다르다
SELECT * FROM users WHERE lower(trim(email)) = 'a@example.com';

-- code 컬럼이 varchar, 인덱스도 varchar 기준
EXPLAIN SELECT * FROM products WHERE code = 12345;

ERROR:  operator does not exist: character varying = integer

-- id는 bigint인데 파라미터가 numeric으로 들어온 경우
EXPLAIN SELECT * FROM orders WHERE id = 1001::numeric;

Seq Scan on orders  (cost=0.00..24117.00 rows=4001 width=88)
  Filter: ((id)::numeric = '1001'::numeric)

-- 탄다: 접두사가 고정되어 인덱스 범위 탐색이 가능하다
SELECT * FROM users WHERE name LIKE 'kim%';

-- 안 탄다: 시작점을 모르므로 전체를 봐야 한다
SELECT * FROM users WHERE name LIKE '%kim%';

-- pg_trgm: 부분 문자열 검색을 인덱스로 처리한다
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm
  ON users USING gin (name gin_trgm_ops);

EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%kim%';

Bitmap Heap Scan on users  (actual time=2.114..8.902 rows=412 loops=1)
  Recheck Cond: (name ~~ '%kim%'::text)
  ->  Bitmap Index Scan on idx_users_name_trgm  (actual time=1.884..1.884 rows=498 loops=1)
        Index Cond: (name ~~ '%kim%'::text)

CREATE INDEX idx_users_name_prefix
  ON users (name text_pattern_ops);

CREATE INDEX idx_orders_multi ON orders (user_id, status, created_at);

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '01012345678';

Bitmap Heap Scan on users  (actual time=0.061..0.064 rows=2 loops=1)
  Recheck Cond: ((email = 'a@b.com') OR (phone = '01012345678'))
  ->  BitmapOr  (actual time=0.052..0.052 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_users_email  (actual time=0.031..0.031 rows=1 loops=1)
        ->  Bitmap Index Scan on idx_users_phone  (actual time=0.019..0.019 rows=1 loops=1)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE cancelled_at IS NULL;

Index Scan using idx_orders_cancelled_at on orders
  (actual time=0.022..1.884 rows=304 loops=1)
  Index Cond: (cancelled_at IS NULL)

-- 마지막 ANALYZE 시점과 이후 변경량 확인
SELECT relname,
       n_live_tup,
       n_mod_since_analyze,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

 relname | n_live_tup | n_mod_since_analyze |    last_analyze     | last_autoanalyze
---------+------------+---------------------+---------------------+------------------
 orders  |     804112 |              611903 | 2026-07-19 03:12:44 | 2026-07-19 03:12:44

-- 큰 테이블은 테이블 단위로 임계값을 낮춘다
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

-- 배치 적재 직후에는 명시적으로 갱신한다
ANALYZE orders;

-- 미처리 주문만 조회하는 워크로드
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- 크기 비교
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders';

      indexrelname       |  size
-------------------------+---------
 idx_orders_status       | 42 MB
 idx_orders_pending      | 312 kB

SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

 table_name | index_name                 | idx_scan |  size
------------+----------------------------+----------+--------
 orders     | idx_orders_updated_at      |        0 | 88 MB
 events     | idx_events_legacy_type     |        0 | 41 MB

-- 통계 수집 시작 시점 확인
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- 삭제 대신 계획에서만 빼고 관찰하고 싶다면 (카탈로그 직접 수정, 운영 권장하지 않음)
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_orders_updated_at'::regclass;

SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_orders_updated_at';
DROP INDEX CONCURRENTLY idx_orders_updated_at;