[PostgreSQL] JSON 컬럼에 특정 값만 추출해서 Index를 걸어보자

 

JSON 데이터를 PostgreSql에 저장할 때 data type을 json으로 하여 저장을 합니다. 프로젝트를 진행 하던 중 json 데이터의 특정 값만 찾아서 Index를 걸어야 하는 경우가 발생하여 처리 방법을 작성하고 성능까지 비교해보겠습니다.

---

JSON 연산자

PostgreSql에서 data type이 json인 컬럼에 where절을 사용할 때 아래와 같이 쿼리를 사용합니다.

-- info column(json data) : { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}

-- json data에서 items의 product가 Diaper인 데이터 조회 쿼리
SELECT info ->> 'customer' AS customer
FROM orders
WHERE info -> 'items' ->> 'product' = 'Diaper';

json 컬럼에 연산자 ->, ->> 를 통해 데이터를 추출하거나, where절, order by절 등을 사용하게 됩니다.

-> 연산자는 추출 된 값이 json 타입을 가집니다.

->> 연산자는 추출 된 값이 text 타입을 가집니다.

---

JSON 연산자를 사용하여 추출하는 값을 Index하는 방법

위의 예제에서 orders 테이블의 WHERE 절에 사용하는 info -> 'items' ->> 'product' 값에 index를 걸어보겠습니다.

create index index_name on orders((info -> 'items' ->> 'product'));

위와 같이 create index 문을 사용하여 index를 만들게 되면, orders 테이블에 저장되어 있는 info 컬럼에 있는 json 에서 info -> 'items' ->> 'product' 까지 값을 추출하여 index 데이터를 생성하게 됩니다. orders 테이블에 새로운 row가 insert 될 때에도 마찬가지로 'product'까지 값을 추출하여 index를 구성합니다.

---

성능 비교

index 생성 전과 생성 후의 where절 처리 cost를 비교해보겠습니다. 성능 비교는 EXPLAIN(명령문 실행 계획) 쿼리를 이용하여 테이블 row 스캔 cost(수행 비용 범위)를 조사해 보았습니다.

* orders의 row 수는 약 30,000건 정도 있는 상태입니다.

index 생성 전

EXPLAIN
    SELECT info ->> 'customer' AS customer
    FROM orders
    WHERE info -> 'items' ->> 'product' = 'Diaper';

Index 생성 전 cost(수행 비용 범위)는 0.00 ~ 936.03 입니다.

index 생성 후

-- index 생성
create index index_name on orders((info -> 'items' ->> 'product'));

-- 실행계획
EXPLAIN
    SELECT info ->> 'customer' AS customer
    FROM orders
    WHERE info -> 'items' ->> 'product' = 'Diaper';

Index 생성 후 cost(수행 비용 범위)는 5.47 ~ 304.63 입니다.

최소 수행 비용은 소폭 상승한 반면, 최대 수행 비용이 크게 줄어 들었습니다.

---

응용

Index를 할 때 아래와 같이 function을 사용해서 색인 할 수 도 있습니다.

create index index_name on orders(upper(replace(info -> 'items' ->> 'product', '-', '')));

---

주의점

• Index(색인)을 한다고 무조건 성능이 좋아지는 것은 아니다.
• where절에 json의 값에서 조회해야하는데, 성능을 중요시 한다면 값을 미리 뽑아놓아서 색인해 놓는게 좋지 않을까 하는 의문이 든다.