时间戳

首页 > 解决方案 > Postgresql - jsonb 列中日期字段的索引策略

postgresql - 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)

标签： postgresqljsonb

解决方案

该查询的完美索引是：

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

那是行不通的，因为从texttodate的转换不是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'))
);

推荐阅读