[PostgreSQL] JSONB @> 컨테인먼트로 JSON 컬럼 조건검색을 인덱스 태우기

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

부제: 상품 속성을 통째로 jsonb 컬럼에 넣어뒀는데, "브랜드가 삼성인 것"처럼 JSON 안쪽 값으로 매번 필터링하니 풀스캔이 걸릴 때

-- @> 는 "왼쪽 JSON이 오른쪽 JSON을 통째로 포함하는가"
SELECT id, name FROM m4_products WHERE attrs @> '{"brand":"삼성"}';
-- 중첩도 그대로: specs 안의 ram 이 16
SELECT id, name FROM m4_products WHERE attrs @> '{"specs":{"ram":16}}';

 id |   name    
----+-----------
  1 | 갤럭시북4
  4 | 버즈3

 id |   name    
----+-----------
  1 | 갤럭시북4

attrs @> '{...}'는 "이 JSON 조각을 포함하느냐"를 묻는다. attrs->>'brand' = '삼성' 처럼 값을 꺼내 비교할 수도 있지만, 그 방식은 GIN 인덱스를 못 탄다. @>는 컬럼 전체에 GIN 인덱스 하나만 걸어두면 어떤 키/값 조합으로 물어도 인덱스가 받아준다. 함정: @>는 "포함"이라서 부등호(price > 100만)나 범위 검색에는 못 쓴다. 그건 값을 꺼내(->) 비교하거나 표현식 인덱스로 간다.

인덱스가 실제로 붙는지 EXPLAIN으로 확인한다. GIN 없을 때는 Seq Scan.

EXPLAIN ANALYZE SELECT count(*) FROM m4_prod_big WHERE attrs @> '{"tags":["고성능"]}';

 ->  Seq Scan on m4_prod_big  (cost=0.00..152.50 rows=2500 ...)
       Filter: (attrs @> '{"tags": ["고성능"]}'::jsonb)
       Rows Removed by Filter: 2500

CREATE INDEX idx_prod_attrs ON m4_prod_big USING gin (attrs);
EXPLAIN ANALYZE SELECT count(*) FROM m4_prod_big WHERE attrs @> '{"tags":["고성능"]}';

 ->  Bitmap Heap Scan on m4_prod_big  (cost=26.07..147.32 rows=2500 ...)
       Recheck Cond: (attrs @> '{"tags": ["고성능"]}'::jsonb)
       ->  Bitmap Index Scan on idx_prod_attrs (cost=0.00..25.44 ...)
             Index Cond: (attrs @> '{"tags": ["고성능"]}'::jsonb)

이렇게도 쓴다

배열 원소를 포함하는 행 찾기 — @>는 배열에도 그대로 통한다.

SELECT id, name FROM m4_products WHERE attrs @> '{"tags":["업무용"]}';

 

특정 키가 존재하는지 (? 연산자, 이것도 GIN이 받는다).

SELECT id, name FROM m4_products WHERE attrs ? 'instock' AND attrs @> '{"instock":false}';

 

specs 안에 여러 키 중 하나라도 있는 행 (?|).

SELECT id, name FROM m4_products WHERE attrs -> 'specs' ?| array['anc','cpu'];

 

검색을 @>로만 할 거면 jsonb_path_ops 인덱스가 더 작고 빠르다. (조합: 인덱스 옵션)

-- 기본 GIN 96kB → jsonb_path_ops 56kB (키 존재 ? 연산자는 못 쓰지만 @> 전용으로 최적)
CREATE INDEX idx_prod_pathops ON m4_prod_big USING gin (attrs jsonb_path_ops);

 

자주 쓰는 스칼라 한 개만 검색한다면 표현식 인덱스가 GIN보다 낫다. (조합: 표현식 인덱스)

CREATE INDEX idx_prod_brand ON m4_prod_big ((attrs->>'brand'));
SELECT count(*) FROM m4_prod_big WHERE attrs->>'brand' = '애플';