GIN 인덱스를 사용하여 Postgres JSONB 배열 쿼리를 최적화하는 방법은 무엇입니까?

Postgres에서 JSONB 배열과 함께 GIN 인덱스를 사용하는 방법

문제:
JSONB 배열을 쿼리할 때 events_gin_idx ON some_table USING을 사용하는 GIN 인덱스 Postgres 진(이벤트); 특정 배열 요소를 검색하는 쿼리는 최적화되지 않을 수 있습니다.

원인:
쿼리는 jsonb_array_elements(events) AS e를 사용하여 배열 요소를 JSON 배열 요소로 변환하고 있습니다. 인덱싱.

Postgres 12에 대한 적절한 인덱싱 또는 이후:
경로 표현식이 포함된 쿼리를 지원하는 GIN 인덱스를 생성하려면 jsonb_path_ops 연산자 클래스를 사용하세요.

CREATE INDEX locations_events_gin_idx ON locations USING gin (events jsonb_path_ops);

Postgres 12 이상에 대한 쿼리 구문:
@?를 사용하세요. 조건과 일치하는 배열 요소를 테스트하는 연산자:

SELECT *
FROM locations
WHERE events @? '$[*] ? (@.event_slug == "test_1")
                      ? (@.end_time.datetime() < "2014-10-13".datetime()'

이전 버전 Postgres의 쿼리 구문:
Postgres 12 이전 버전의 경우 @>를 사용할 수 있습니다. ; 부분 JSON 객체를 사용하는 연산자:

SELECT *
FROM locations
WHERE events @> '[{ "event_slug": "test_1"}]';

구체화된 뷰를 사용한 고급 솔루션:
기본 인덱싱 솔루션의 성능이 충분하지 않은 경우 구체화된 뷰 생성을 고려하세요.

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time  -- start_time not needed
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

구체화된 보기 쿼리:

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

위 내용은 GIN 인덱스를 사용하여 Postgres JSONB 배열 쿼리를 최적화하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!