Какой правильный индекс для запросов структур в массивах в Postgres jsonb?

Ответ 1

Прежде всего, вы не можете получить доступ к таким значениям массива 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'

Но вы, вероятно, не хотите ограничивать свой поиск первым элементом массива. С типом данных jsonb в Postgres 9.4 у вас есть дополнительные операторы и поддержка индексов. Чтобы индексировать элементы массива, вам нужен индекс 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;

Использование неявного JOIN LATERAL. Подробности (последняя глава):

• PostgreSQL unsest() с номером элемента

Осторожно с различными типами данных! То, что у вас есть в значении JSON, выглядит как timestamp [without time zone], в то время как ваши предикаты используют литералы timestamp with time zone. Значение timestamp интерпретируется в соответствии с текущей настройкой часового пояса, в то время как данные timestamptz литералы должны быть приведены к timestamptz явно или часовой пояс будет проигнорирован! Выше запрос должен работать по желанию. Подробное объяснение:

• Игнорирование часовых поясов вообще в Rails и PostgreSQL

Больше объяснений для jsonb_array_elements():

• Присоединение PostgreSQL с использованием JSONB

Расширенное решение

Если выше не достаточно, я бы рассмотрел MATERIALIZED VIEW, в котором хранятся соответствующие атрибуты в нормализованных форма. Это позволяет использовать простые индексы 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():

• Как преобразовать jsonb-тип PostgreSQL 9.4 в float

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

Также включите location_id, чтобы разрешить просмотр только по индексу. (См. справочная страница и Postgres Wiki.)

Query:

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

Или, если вам нужны полные строки из базовой таблицы locations:

SELECT l.*
FROM  (
   SELECT DISTINCT location_id
   FROM   loc_event
   WHERE  event_slug = 'test_1'
   AND    end_time  >= '2014-10-30 14:04:06 -0400'::timestamptz
   ) le
JOIN locations l USING (location_id);

Ответ 2

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

Если вы начнете в правильном направлении.