主页 / dba / 问题 / 149832
Accepted
shadowtalker
Asked: 2016-09-16 12:03:43 +0800 CST

PostgreSQL 过滤 JSON 中的数组长度

  • 772

我有一个table带有 JSONB 字段的表data，其中包含一个可变长度数组，例如

{"label": "xyz", "items": [ ... ]}

"items"我在元素的长度上创建了一个索引：

CREATE INDEX n_items ON table ( JSONB_ARRAY_LENGTH(data->'items') )

但是当我过滤时，当我尝试对其进行过滤时，我仍然会得到顺序扫描：

EXPLAIN ANALYZE SELECT COUNT(*) FROM table WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=2565655.67..2565655.68 rows=1 width=8)
   ->  Seq Scan on table (cost=0.00..2535256.19 rows=12159794 width=8)
         Filter: (jsonb_array_length((table.data -> 'items'::text)) = 2)
 Planning time: 0.121 ms
 Execution time: 482891.694 ms

过滤大约需要 8 分钟！我在这里做错了什么，或者这是 PostgreSQL 没有保留 JSON(B) 对象的统计信息的结果？应该可以展平这个data专栏，但我想确定这是我在开始工作之前需要做的。

编辑：这些数组长度变化不大。目前数据中只有 4 个不同的值，我预计不会有更多。在这种情况下，索引不是很有用，还是我可以通过其他方式改进过滤？

postgresql index

1 个回答

  1. Best Answer

    在不知道您的数据的情况下，我只能猜测您的索引的选择性很低（如果数组的长度变化不大，就会发生这种情况）。

    克服这个问题的一个技巧可能是稍微改变查询并创建一个覆盖索引。为此，选择一NOT NULL列（例如，表的主键）进行计数，然后将该列包含在索引中：

    CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id) 
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

    这将有望变成仅索引扫描（我对此进行了测试，省略了该jsonb部分，但您将能够判断它是否有效）。

    • 15

相关问题