如何优化 jsonb 数组上的 Postgres 查询以提高性能？

使用 Postgres jsonb 查询数组中的结构

Jsonb 数组可以在 Postgres 中存储结构化数据，方便使用嵌套对象进行查询。但是，使用顺序索引访问数组值可能会导致顺序扫描。

适当的索引以提高查询性能

要优化涉及 jsonb 数组比较的查询，特别是像提供了一种（检查特定时间范围内的事件），可以执行以下步骤采取：

• 使用 jsonb_path_ops 运算符类： 这确保了涉及大于或小于运算符的复杂 jsonb 比较的高效匹配。

基本方法（Postgres 12 和稍后）

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

利用物化视图的高级方法

如果复杂查询仍然导致性能不佳，请考虑创建具有规范化相关属性的物化视图：

• 创建事件数据类型：

  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;

• 索引物化视图：

  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;

  通过使用正确的运算符类并考虑物化视图等高级方法，您可以为涉及 jsonb 数组比较的查询实现最佳性能数据。

  以上是如何优化 jsonb 数组上的 Postgres 查询以提高性能？的详细内容。更多信息请关注PHP中文网其他相关文章！