[PostgreSQL] 배열 검색 = ANY / && / @> 와 GIN 인덱스

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

부제: 게시글 tags text[] 컬럼에서 '리눅스'나 'DB' 태그가 붙은 글을 조인 테이블 없이 빠르게 찾을 때

CREATE TABLE post (id int PRIMARY KEY, title text, tags text[]);
-- ... 5000행 적재, id<=20 은 tags = {리눅스,DB,보안}

CREATE INDEX idx_post_tags ON post USING GIN (tags);

-- 하나라도 겹치면(OR): 리눅스 또는 DB
SELECT count(*) FROM post WHERE tags && ARRAY['리눅스','DB'];

-- 다 포함하면(AND): 리눅스 그리고 DB
SELECT count(*) FROM post WHERE tags @> ARRAY['리눅스','DB'];

-- 단일 원소: '리눅스'가 배열 안에 있나
SELECT count(*) FROM post WHERE '리눅스' = ANY(tags);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM post WHERE tags && ARRAY['리눅스','DB'];

-- && (겹침) 결과
 count 
-------
  1265
-- @> (포함) 결과
 count 
-------
   435
-- '리눅스' = ANY(tags) 결과
 count 
-------
   643

-- EXPLAIN: GIN 인덱스를 Bitmap Index Scan 으로 탄다
 Bitmap Heap Scan on post (actual rows=1265 loops=1)
   Recheck Cond: (tags && '{리눅스,DB}'::text[])
   Heap Blocks: exact=58
   ->  Bitmap Index Scan on idx_post_tags (actual rows=1265 loops=1)
         Index Cond: (tags && '{리눅스,DB}'::text[])

배열 검색은 연산자 세 개로 갈린다. &&는 두 배열이 원소를 하나라도 공유하면 참(태그 OR 필터), @>는 왼쪽이 오른쪽을 통째로 포함하면 참(태그 AND 필터), = ANY(tags)는 스칼라 하나가 배열에 들어있는지다. 세 연산자 모두 USING GIN (tags) 인덱스를 타서, EXPLAIN을 보면 Seq Scan 대신 Bitmap Index Scan → Bitmap Heap Scan으로 풀린다. GIN은 배열 원소마다 포스팅 리스트를 만들어 두기 때문에 별도 post_tag 조인 테이블을 안 만들어도 태그 필터가 빠르다.

함정은 = ANY와 서브쿼리 IN을 헷갈리는 것이다. x IN (1,2,3)은 내부적으로 x = ANY(ARRAY[1,2,3])인데, 여기서 배열은 "찾을 값들의 목록"이다. 반면 '리눅스' = ANY(tags)는 배열이 테이블 컬럼이고 스칼라가 상수다. 방향이 정반대라, IN 감각으로 tags IN (...)처럼 쓰면 타입이 안 맞아 에러가 난다. 배열 컬럼을 뒤질 땐 = ANY / && / @>를 쓴다.

이렇게도 쓴다

<@ 는 @> 의 반대 방향: 내 배열이 주어진 집합의 부분집합인가.

SELECT count(*) FROM post WHERE tags <@ ARRAY['리눅스','DB','보안','네트워크'];

 

특정 태그가 없는 글만. (NOT)

SELECT id FROM post WHERE NOT (tags && ARRAY['보안']);

 

배열을 행으로 펼쳐 태그별 집계. (조합: unnest + GROUP BY)

SELECT tag, count(*) FROM post, unnest(tags) AS tag GROUP BY tag ORDER BY 2 DESC;

 

빈 배열/NULL 방어: && 는 빈 배열과는 항상 거짓.

SELECT '{}'::text[] && ARRAY['리눅스'] AS empty_overlap;  -- f

 

플래너가 정말 GIN을 고르는지 확인. (조합: EXPLAIN)

EXPLAIN (COSTS OFF) SELECT id FROM post WHERE tags @> ARRAY['리눅스','DB'];

 

원소 개수로 필터. (조합: cardinality)

SELECT id FROM post WHERE cardinality(tags) >= 3;