Postgresql — стратегия индексирования поля даты в столбце jsonb

Есть ли наилучшая практика для работы с полями даты в столбцах jsonb? В следующем сценарии я пытаюсь найти «Пациентов» по ​​дате их рождения.

SELECT P.resource ->> 'id' ID, P.resource -> 'birthDate' DoB, CONCAT(P.resource -> 'name' -> 0 -> 'given', ' ', P.resource -> 'name' -> 0 -> 'family') "name"
FROM recorditems P
WHERE  P.resource ->> 'resourceType' = 'Patient'
    AND (P.resource ->> 'birthDate')::date BETWEEN '1975-01-01'::date AND '1995-01-01'::date;

Индекс gin может пригодиться для первого предиката, если я перепишу его с помощью оператора @>. Есть ли предложение индекса для второго предиката? Не будет ли преобразование строки в дату влиять на производительность?

explain analyze говорит следующее:

 Gather  (cost=1000.00..229569.96 rows=39 width=96) (actual time=6.178..1205.812 rows=699 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on recorditems p  (cost=0.00..228566.06 rows=16 width=96) (actual time=13.747..1172.495 rows=233 loops=3)
         Filter: (((resource ->> 'resourceType'::text) = 'Patient'::text) AND (((resource ->> 'birthDate'::text))::date >= '1975-01-01'::date)
AND (((resource ->> 'birthDate'::text))::date <= '1995-01-01'::date))
         Rows Removed by Filter: 524492
 Planning Time: 0.068 ms
 JIT:
   Functions: 12
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.296 ms, Inlining 0.000 ms, Optimization 0.891 ms, Emission 16.132 ms, Total 19.320 ms
 Execution Time: 1206.889 ms
(12 rows)

Time: 1207.337 ms (00:01.207)

postgresql jsonb
person Mahesh V S    schedule 18.06.2020    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Идеальным индексом для этого запроса будет:

CREATE INDEX ON recorditems (
   (resource ->> 'resourceType'),
   ((resource ->> 'birthDate')::date)
);

Это не работает, потому что приведение от text к date не является IMMUTABLE (попробуйте SELECT 'today'::date;).

Если вы знаете, что хранятся только даты ISO, вы можете обойти это с помощью пользовательской функции:

CREATE FUNCTION text_to_date(text) RETURNS date
   IMMUTABLE LANGUAGE sql AS
'SELECT CAST($1 AS date)';

Затем вы должны использовать эту функцию в своем запросе и индексе:

CREATE INDEX ON recorditems (
   (resource ->> 'resourceType'),
   (text_to_date(resource ->> 'birthDate'))
);
person Laurenz Albe    schedule 18.06.2020
comment
Как насчет преобразования типов? Не будет ли это вообще проблемой - с точки зрения использования индекса и накладных расходов на выполнение? - person Mahesh V S; 18.06.2020
comment
Индекс @MaheshVS создается после преобразования даты рождения в тип данных date. - person Arjun Vachhani; 18.06.2020
comment
@ArjunVachhani - я имел в виду преобразование в запросе. Исходя из моего опыта работы с MSSQL, когда вы применяете преобразование к полю, индекс не будет использоваться - stackoverflow.com/questions/8122821/ Я хотел бы знать, относится ли это и к Postgres. - person Mahesh V S; 18.06.2020
comment
Если вы используете приведение типа в своем запросе, то вам также необходимо использовать приведение типа в определении индекса, как я сделал в своем ответе. Вы пробовали? - person Laurenz Albe; 18.06.2020
comment
@LaurenzAlbe Я не могу создать индекс - он говорит ОШИБКА: функции в выражении индекса должны быть помечены как IMMUTABLE - person Mahesh V S; 18.06.2020
comment
О, я не подумал об этом. Я предложил обходной путь в моем обновленном ответе. - person Laurenz Albe; 18.06.2020
comment
@LaurenzAlbe Я обновил новый план запроса в вопросе для справки. Похоже, что план запроса не использует поле даты для сканирования индекса, а выполняет фильтр по дате после сканирования типа ресурса. - person Mahesh V S; 18.06.2020
comment
Давайте продолжим обсуждение в чате. - person Mahesh V S; 18.06.2020
comment
Да. Как я сказал в своем ответе, вы также должны изменить запрос: он должен использовать функцию, а не приведение. Индекс можно использовать только в том случае, если одна сторона оператора точно соответствует индексированному выражению. - person Laurenz Albe; 18.06.2020