[SQL] 포스트 그레스 jsonb에 배열의 구조를 쿼리에 대한 적절한 지수는 무엇입니까?

포스트 그레스 jsonb에 배열의 구조를 쿼리에 대한 적절한 지수는 무엇입니까?

나는 포스트 그레스 9.4에서 포스트 그레스 jsonb 필드에 다음과 같은 값을 유지하는 실험을하고 있습니다 :

[{"event_slug":"test_1","start_time":"2014-10-08","end_time":"2014-10-12"},
 {"event_slug":"test_2","start_time":"2013-06-24","end_time":"2013-07-02"},
 {"event_slug":"test_3","start_time":"2014-03-26","end_time":"2014-03-30"}]

내가 좋아하는 쿼리를 실행하고 있습니다 :

SELECT * FROM locations
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(events) AS e
  WHERE (
    e->>'event_slug' = 'test_1' AND
    (
      e->>'start_time' >= '2014-10-30 14:04:06 -0400' OR
      e->>'end_time' >= '2014-10-30 14:04:06 -0400'
    )
  )
)

어떻게 활용할 수 위의 같은 쿼리에 대한 데이터에 대한 인덱스를 만들 것인가? 몇 백만 행이 소리 합리적인 설계는 각 해당 열의 ~ 10 이벤트가 포함 않습니다?

가치는 아직도으로 순차적 스캔을지고있어 보인다 것을주의 :

CREATE INDEX events_gin_idx ON some_table USING GIN (events);

이는 내가 추측하고있어 내가 쿼리에서하고 있어요 제일 먼저 JSON 배열 요소에 데이터를 변환되기 때문이다.

해결법

1. ==============================

   1.우선, 당신은 그렇게하지 액세스 JSON 배열 값을 수 있습니다. 주어진 JSON 값

   우선, 당신은 그렇게하지 액세스 JSON 배열 값을 수 있습니다. 주어진 JSON 값

   [{"event_slug":"test_1","start_time":"2014-10-08","end_time":"2014-10-12"},
    {"event_slug":"test_2","start_time":"2013-06-24","end_time":"2013-07-02"},
    {"event_slug":"test_3","start_time":"2014-03-26","end_time":"2014-03-30"}]
   

   첫 번째 배열 요소에 대해 유효한 테스트 할 것이다 :

   WHERE e->0->>'event_slug' = 'test_1'

   그러나 당신은 아마 배열의 첫 번째 요소로 검색을 제한하고 싶지 않아요. 포스트 그레스 9.4에서 jsonb 데이터 형식을 사용하면 추가로 사업자와 인덱스 지원합니다. 배열의 인덱스 요소로는 GIN 인덱스를해야합니다.

   내장 연산자 클래스 GIN 인덱스에 대해 "보다 적은" "보다 더"를 지원하지 않거나 연산자>> = <<=. 이뿐만 아니라, 두 개의 연산자 클래스를 선택할 수있는 jsonb 마찬가지입니다. 문서 별 :

   Name             Indexed Data Type  Indexable Operators
   ...
   jsonb_ops        jsonb              ? ?& ?| @>
   jsonb_path_ops   jsonb              @>
   

   당신은 평등 테스트를 커버 할 수있다 (jsonb_ops가. 기본되는)하지만, 둘 다 그 연산자> = 비교에 대한 귀하의 요구 사항을 다룹니다. 당신은 BTREE 인덱스가 필요합니다.

   인덱스와 동등 검사를 지원합니다 :

   CREATE INDEX locations_events_gin_idx ON locations
   USING gin (events jsonb_path_ops);
   
   SELECT * FROM locations WHERE events @> '[{"event_slug":"test_1"}]';
   

   필터가 선택적 충분한 경우에 충분한 수 있습니다. 우리는이 검사를 필요가 없습니다, END_TIME> = start_time을 가정. 단지 END_TIME 점검 저렴하고 동일합니다 :

   SELECT l.*
   FROM   locations l
        , jsonb_array_elements(l.events) e
   WHERE  l.events @> '[{"event_slug":"test_1"}]'
   AND   (e->>'end_time')::timestamp >= '2014-10-30 14:04:06 -0400'::timestamptz;
   

   활용 암시는 측면 가입. 세부 사항 (마지막 장) :

   서로 다른 데이터 유형 조심해! 어떤 술어가 시간대 리터럴와 타임 스탬프를 사용하는 동안, 타임 스탬프 등의 [시간대없이]를 JSON 값 외모에 있습니다. 주어진 timestamptz 리터럴 명시 적으로 timestamptz으로 캐스팅해야 반면, 타임 스탬프 값은 현재의 타임 존 설정에 따라 해석되거나 시간대는 무시 될 것입니다! 원하는대로 위 쿼리가 작동합니다. 상해:

   jsonb_array_elements에 대한 자세한 설명은 () :

   위가 충분이 아닌 경우에, 나는 구체화 된 뷰를 고려할 것 표준화 된 형태로 저장 관련 속성이. 이것은 일반 BTREE 인덱스를 할 수 있습니다.

   이 코드는 질문에 표시되는대로 JSON 값이 일관성있는 형식이 있다고 가정합니다.

   설정:

   CREATE TYPE event_type AS (
    , event_slug  text
    , start_time  timestamp
    , end_time    timestamp
   );
   
   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;
   

   jsonb_populate_recordset에 대한 관련 대답 () :

2. ==============================

   2.

   CREATE INDEX json_array_elements_index ON
       json_array_elements ((events_arr->>'event_slug'));
   

   당신이 올바른 방향으로 시작해야한다.

from https://stackoverflow.com/questions/26499266/whats-the-proper-index-for-querying-structures-in-arrays-in-postgres-jsonb by cc-by-sa and MIT license