14.3 인덱스 안티패턴

-- 안티패턴
CREATE INDEX ON orders(user_id);
CREATE INDEX ON orders(status);
CREATE INDEX ON orders(created_at);
CREATE INDEX ON orders(country);
CREATE INDEX ON orders(amount);
CREATE INDEX ON orders(payment_method);
...

-- pg_stat_statements로 WHERE 패턴 추출
SELECT query FROM pg_stat_statements WHERE query ILIKE '%FROM orders%' ORDER BY total_exec_time DESC;

-- idx_scan = 0이지만 drop 안 함

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;

CREATE INDEX ON orders(user_id);
CREATE INDEX ON orders(user_id, status);
-- 둘 다 user_id 룩업에 사용 가능 — 첫 번째 중복

-- 안티패턴 — selectivity 낮은 컬럼이 앞
CREATE INDEX ON orders(status, user_id);
-- status는 'paid'가 90% — selectivity 낮음
-- user_id로 시작했어야

CREATE INDEX ON orders(user_id, status);

-- 안티패턴
CREATE TABLE order_items (
  id bigserial PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders(id)   -- 인덱스 없음!
);

CREATE INDEX ON order_items(order_id);

-- 안티패턴 — 100 row 테이블
CREATE TABLE settings (...);   -- 50 row
CREATE INDEX ON settings(key);

-- 안티패턴
CREATE INDEX ON orders(status);   -- 'paid' 90%, 'pending' 8%, 'cancelled' 2%

CREATE INDEX ON orders(user_id) WHERE status = 'pending';

-- 안티패턴
CREATE INDEX ON users(deleted_at);
-- 99% NULL

CREATE INDEX ON users(deleted_at) WHERE deleted_at IS NOT NULL;

-- 안티패턴
-- 코드에서 INSERT 전 SELECT로 중복 검사
SELECT count(*) FROM users WHERE email = 'a@b.com';
-- if 0: INSERT

CREATE UNIQUE INDEX ON users(email);
-- INSERT가 충돌 시 ERROR — application이 잡기

-- 안티패턴
CREATE INDEX ON events(id)
  WHERE created_at > now() - interval '1 day';
-- now()는 인덱스 생성 시점으로 고정됨

-- 안티패턴
CREATE INDEX ON users(my_func(col));
-- my_func이 IMMUTABLE이 아니면 안전하지 않음

CREATE OR REPLACE FUNCTION my_func(t text) RETURNS text
  AS $$ SELECT ... $$
  LANGUAGE sql IMMUTABLE;

CREATE INDEX CONCURRENTLY ON orders(user_id, status);
-- ANALYZE 안 함

CREATE INDEX CONCURRENTLY ON orders(user_id, status);
ANALYZE orders;

-- 안티패턴 — ETL에서
INSERT INTO orders ... (1000만 row);
CREATE INDEX ON orders(user_id);
-- 인덱스 빌드 매우 느림

DROP INDEX idx_orders_user_id;
INSERT INTO orders ...;
CREATE INDEX CONCURRENTLY ON orders(user_id);

-- 안티패턴
CREATE INDEX ON orders(user_id);   -- SHARE lock, 트래픽 막힘

CREATE INDEX CONCURRENTLY ON orders(user_id);

CREATE INDEX ON orders(user_id);
-- 쿼리는 항상 WHERE user_id = X AND status = 'Y'
-- composite (user_id, status)가 더 좋음

CREATE INDEX ON articles(body);   -- body가 1MB

CREATE INDEX ON articles(md5(body));
-- 또는
CREATE INDEX ON articles USING gin (body gin_trgm_ops);

-- 안티패턴
SET enable_seqscan = off;   -- 전역

BEGIN;
SET LOCAL enable_seqscan = off;
SELECT ...;
COMMIT;