Postgres:搜索 jsonb 数组字段很慢

Question

我们正在更改其中一个项目的DB(PostgreSQL 10.11(结构。其中一个变化是将类型为 uuid[](称为"areasoflawid"(的字段移动到 jsonb 字段(称为"data"(。

因此，我们有一个如下所示的表：

CREATE TABLE public.documents
(
id serial,
areasoflawid uuid[], --the field to be moved into the ‘data’
data jsonb,
….
)

我们不会更改数组或其结构的值。 即 documents.data->'metadata'->'areaoflawids' 包含与 documents.areasoflawids 相同的项目( 数据迁移后，存储在"数据"字段中的JSON具有以下结构：

{
...
"metadata": {
...
"areaoflawids": [
"e34e0ee5-78e0-4d92-9186-ac69c109408b",
"b3af9163-d910-4d19-8f40-0602b75c25b0",
"50dc7fd8-ebdf-4cd2-bcab-b8d755fe96e8",
"8955c062-363f-4a1a-ac3c-d1c2ffe96c9b",
"bdb79f9f-4539-45f5-ac82-92baaf915f6c"
],
....
},
...
}

因此，在迁移数据后，我们开始对 jsonb 字段相关查询进行基准测试，并发现搜索数组字段文档.data->'metadata'->'areaoflawids' 比搜索 uuid[] 字段文档.areasoflawid 花费的时间要长得多。

以下是查询：

--search over jsonb array field, takes 6.2 sec, returns 13615 rows
SELECT id FROM documents WHERE data->'metadata'->'areaoflawids' @> '"e34e0ee5-78e0-4d92-9186-ac69c109408b"'
--search over uuid[] field, takes 600ms, returns 13615 rows
SELECT id FROM documents WHERE areasoflawid @> ARRAY['e34e0ee5-78e0-4d92-9186-ac69c109408b']::uuid[]

以下是 jsonb 字段上的索引：

CREATE INDEX test_documents_aols_gin_idx
ON public.documents
USING gin
(((data -> 'metadata'::text) -> 'areaoflawids'::text) jsonb_path_ops);

这是执行计划：

EXPLAIN ANALYZE SELECT id FROM documents WHERE data->'metadata'->'areaoflawids' @> '"e34e0ee5-78e0-4d92-9186-ac69c109408b"'

"Bitmap Heap Scan on documents  (cost=6.31..390.78 rows=201 width=4) (actual time=2.297..5859.886 rows=13614 loops=1)"
"  Recheck Cond: (((data -> 'metadata'::text) -> 'areaoflawids'::text) @> '"e34e0ee5-78e0-4d92-9186-ac69c109408b"'::jsonb)"
"  Heap Blocks: exact=4859"
"  ->  Bitmap Index Scan on test_documents_aols_gin_idx  (cost=0.00..6.30 rows=201 width=0) (actual time=1.608..1.608 rows=13614 loops=1)"
"        Index Cond: (((data -> 'metadata'::text) -> 'areaoflawids'::text) @> '"e34e0ee5-78e0-4d92-9186-ac69c109408b"'::jsonb)"
"Planning time: 0.133 ms"
"Execution time: 5862.807 ms"

通过 jsonb 字段的其他查询以可接受的速度工作，但此特定搜索比在分隔字段上的搜索慢约 10 倍。我们预计它会慢一点，但还不错。我们考虑将此"areasoflawid"字段保留为单独的字段的选项，但我们肯定更愿意将其移动到 json 中。我一直在使用不同的索引和操作(也使用？和？|(，但搜索仍然很慢。任何帮助不胜感激！

Answer 1

在索引中查找 13,614 个候选匹配项非常快(1.608 毫秒(。 缓慢的部分是从表本身读取所有这些行。 如果您打开track_io_timing，请EXPLAIN (ANALYZE, BUFFERS)，我相信您会发现您正在等待IO。 如果连续多次运行查询，它是否会变得更快？

我认为您在这里做一个不平等的基准测试，其中一个表已经在缓存中，而备用表不在缓存中。 但也可能是新表太大而无法实际放入缓存中。

Answer 2

感谢您的回复！我们想出了从这篇文章中获取的另一个解决方案： https://www.postgresql.org/message-id/[e-mail] .查询现在大约需要 600-800 毫秒才能执行。 因此，这是解决方案：

CREATE OR REPLACE FUNCTION aol_uuids(data jsonb) RETURNS TEXT[] AS
$$
SELECT 
array_agg(value::TEXT) as val
FROM 
jsonb_array_elements(case jsonb_typeof(data) when 'array' then data else '[]' end)
$$ LANGUAGE SQL IMMUTABLE;

SELECT id FROM documents WHERE aol_uuids(data->'metadata'->'areaoflawids')@>ARRAY['"e34e0ee5-78e0-4d92-9186-ac69c109408b"']