[PostgreSQL] 인덱스가 있는데 안 타는 이유 — 형변환·함수·OR·낮은 선택도

설치·접속: PostgreSQL 설치와 접속

부제: 분명히 인덱스를 만들었는데 EXPLAIN을 보면 Seq Scan이다 — 왜 안 먹히는지 네 가지 전형

-- 컬럼에 형변환/함수를 씌우면 인덱스가 죽는다
SELECT * FROM demo_users WHERE id::text = '12345';   -- Seq Scan
SELECT * FROM demo_users WHERE id = 12345;            -- Index Scan

-- id::text = '12345'  (형변환): 인덱스 무시
 Gather
   ->  Parallel Seq Scan on demo_users
         Filter: ((id)::text = '12345'::text)
-- id = 12345  (원본 그대로): 인덱스 사용
 Index Scan using idx_dusers_id_txt on demo_users
   Index Cond: (id = 12345)

인덱스는 컬럼 원본값을 정렬해 둔다. 그래서 컬럼 쪽에 손을 대는 순간(형변환 id::text, 함수 date_trunc(created_at), 연산 price*0.9) 저장값과 매칭이 깨져 인덱스를 버린다. 규칙은 "가공은 컬럼이 아니라 상수 쪽에서" — id::text='12345' 대신 id=12345로 쓴다. 나머지 세 전형: (1) LIKE '%...'처럼 앞이 와일드카드면 정렬로 좁힐 수 없어 Seq Scan, (2) 서로 다른 두 컬럼의 OR는 한 인덱스로 못 걸러 Seq Scan, (3) 조건에 맞는 행이 너무 많으면(예: status='done'이 85%) 플래너가 일부러 Seq Scan을 고른다 — 어차피 대부분을 읽을 거라 인덱스 왕복이 더 비싸기 때문이다. 즉 "안 탄다"가 항상 버그는 아니고, 낮은 선택도에선 정상 판단이다.

이렇게도 쓴다

함수를 컬럼에 써야 한다면 표현식 인덱스로 되살린다. (조합: expression index)

CREATE INDEX idx_month ON demo_orders(date_trunc('day', created_at));

 

앞 와일드카드가 아닌 prefix 검색은 text_pattern_ops로 태운다. (조합: 연산자 클래스)

CREATE INDEX idx_email_pat ON demo_users(email text_pattern_ops);
-- 이러면 WHERE email LIKE 'User123%' 가 Bitmap Index Scan

 

같은 컬럼의 OR는 IN으로 바꾸면 BitmapOr로 인덱스를 쓴다. (조합: IN)

SELECT * FROM demo_orders WHERE user_id IN (100, 200);

 

다른 컬럼의 OR는 UNION으로 쪼개 각자 인덱스를 태운다. (조합: UNION)

SELECT * FROM demo_orders WHERE user_id=100
UNION SELECT * FROM demo_orders WHERE amount=500;

 

인덱스가 실제 안 쓰이는 게 맞는지 EXPLAIN으로 확인한다. (조합: EXPLAIN)

EXPLAIN (COSTS OFF) SELECT * FROM demo_users WHERE id::text='12345';

 

통계가 낡아 선택도를 잘못 봤을 수 있으니 다시 수집한다. (조합: ANALYZE)

ANALYZE demo_orders;