JSONB 컬럼에서 특정 키로 검색하는데 데이터 늘어나니 너무 느려요

Question

상품 테이블에 속성들을 attributes 라는 jsonb 컬럼에 몰아넣고 있는데, 특정 속성으로 필터링하는 쿼리가 데이터 늘면서 확 느려졌어요.

SELECT * FROM products
WHERE attributes @> '{"category":"laptop"}';
-- 40만건에서 1.8초, EXPLAIN 보면 Seq Scan

일반 컬럼이면 인덱스 걸면 되는데 jsonb는 어떻게 인덱스를 걸어야 할지 모르겠어요. 그냥 attributes에 btree 인덱스를 걸어봤는데 전혀 안 먹더라고요. jsonb 검색은 인덱스가 원래 안 되는 건가요, 아니면 방법이 따로 있나요?

김영삼 · Accepted Answer

jsonb는 btree로는 검색이 안 잡히고, GIN 인덱스를 써야 해요. @>(containment) 연산자는 GIN이 지원합니다.

CREATE INDEX ON products USING gin (attributes);

이거 하나만 걸어도 @> 쿼리가 seq scan에서 인덱스 스캔으로 바뀝니다. 여기서 조금 더 최적화하려면, 검색을 @> 같은 containment 위주로만 한다면 jsonb_path_ops 옵션을 주는 게 좋아요.

CREATE INDEX ON products USING gin (attributes jsonb_path_ops);

기본 GIN보다 인덱스 크기가 작고 containment 검색이 더 빠릅니다. 대신 지원하는 연산자가 @> 계열로 좁아지는 트레이드오프가 있어요. 지금 rows가 40만이면 이 정도로 충분히 빨라질 겁니다. 인덱스 만들 때 테이블 크면 CREATE INDEX CONCURRENTLY로 거는 것도 잊지 마시고요.

jsonb_gyeong · Answer

GIN 걸고 나니 1.8초짜리가 순식간에 끝나네요. 그런데 솔직히 실제 코드에선 @> 보다 attributes->>'brand' = '삼성' 이런 식의 equality를 훨씬 많이 씁니다. 이 경우에도 jsonb_path_ops GIN이 도움이 되나요?

김영삼 · Answer

아쉽지만 그건 안 됩니다. jsonb_path_ops GIN은 @>/containment 계열만 지원하지, ->>'brand' = 값 같은 화살표 추출 연산은 못 타요. 그래서 지금 쿼리 형태론 방금 만든 인덱스가 안 먹습니다.

두 가지 길이 있어요. 첫째, equality를 containment로 바꿔 쓰는 방법. attributes @> '{"brand":"삼성"}' 로 쓰면 의미가 같으면서 GIN 인덱스를 탑니다. 조건을 코드에서 jsonb로 조립하면 돼요. 둘째, 항상 특정 키 하나(brand)로만 조회한다면 그 키에 대한 표현식 btree 인덱스를 따로 만드는 것도 좋습니다.

CREATE INDEX ON products ((attributes->>'brand'));

이건 attributes->>'brand' = 값 형태에 딱 맞아서 equality/정렬에 잘 듣습니다. 정리하면, 여러 키를 유연하게 검색하면 @>로 통일해서 GIN을 태우고, 고정된 한두 키만 자주 조회하면 그 키에 표현식 인덱스를 거는 식으로 쿼리 패턴 보고 고르시면 됩니다.

jsonb_gyeong · Answer

자주 쓰는 brand는 표현식 인덱스 걸고, 나머지 유동적인 필터는 @>로 바꿔서 GIN 태우는 쪽으로 정리했어요. 화살표 추출은 GIN이 못 탄다는 걸 몰라서 계속 헛돌 뻔했네요. 아 이거였네요, 감사합니다!

댓글 4