postgresql - 为什么 Postgres 对 jsonb 列的查找如此缓慢？

Question

我有一个表 targeting，其中有一列 marital_status 类型为 text[] 和另一列 data类型为 jsonb。这两列的内容是一样的，只是格式不同(只是为了演示)。示例数据:

 id |      marital_status      |                        data                       
----+--------------------------+---------------------------------------------------
  1 | null                     | {}
  2 | {widowed}                | {"marital_status": ["widowed"]}
  3 | {never_married,divorced} | {"marital_status": ["never_married", "divorced"]}
...

表中有69万多条记录，随机组合。

查找 text[] 列

EXPLAIN ANALYZE SELECT marital_status
FROM targeting
WHERE marital_status @> '{widowed}'::text[]

没有索引

通常需要 < 900 毫秒而不创建任何索引:

Seq Scan on targeting  (cost=0.00..172981.38 rows=159061 width=28) (actual time=0.017..840.084 rows=158877 loops=1)
  Filter: (marital_status @> '{widowed}'::text[])
  Rows Removed by Filter: 452033
Planning time: 0.150 ms
Execution time: 845.731 ms

带索引

使用索引通常需要 < 200 毫秒(改进 75%):

CREATE INDEX targeting_marital_status_idx ON targeting ("marital_status");

结果:

Index Only Scan using targeting_marital_status_idx on targeting  (cost=0.42..23931.35 rows=159061 width=28) (actual time=3.528..143.848 rows=158877 loops=1)"
  Filter: (marital_status @> '{widowed}'::text[])
  Rows Removed by Filter: 452033
  Heap Fetches: 0
Planning time: 0.217 ms
Execution time: 148.506 ms

在 jsonb 列上查找

EXPLAIN ANALYZE SELECT data
FROM targeting
WHERE (data -> 'marital_status') @> '["widowed"]'::jsonb

没有索引

通常需要 < 5,700 毫秒而不创建任何索引(慢 6 倍以上!):

Seq Scan on targeting  (cost=0.00..174508.65 rows=611 width=403) (actual time=0.095..5399.112 rows=158877 loops=1)
  Filter: ((data -> 'marital_status'::text) @> '["widowed"]'::jsonb)
  Rows Removed by Filter: 452033
Planning time: 0.172 ms
Execution time: 5408.326 ms

带索引

使用索引通常需要 < 3,700 毫秒(改进 35%):

CREATE INDEX targeting_data_marital_status_idx ON targeting USING GIN ((data->'marital_status'));

结果:

Bitmap Heap Scan on targeting  (cost=144.73..2482.75 rows=611 width=403) (actual time=85.966..3694.834 rows=158877 loops=1)
  Recheck Cond: ((data -> 'marital_status'::text) @> '["widowed"]'::jsonb)
  Rows Removed by Index Recheck: 201080
  Heap Blocks: exact=33723 lossy=53028
  ->  Bitmap Index Scan on targeting_data_marital_status_idx  (cost=0.00..144.58 rows=611 width=0) (actual time=78.851..78.851 rows=158877 loops=1)"
        Index Cond: ((data -> 'marital_status'::text) @> '["widowed"]'::jsonb)
Planning time: 0.257 ms
Execution time: 3703.492 ms

问题

• 为什么 text[] 列的性能如此之高，即使不使用索引也是如此？
• 为什么向 jsonb 列添加索引只会将性能提高 35%？
• 是否有更高效的方法来查找 jsonb 列？

Answer 1

这似乎是个简单的问题。本质上你是在问为什么，

CREATE TABLE foo ( id int, key1 text );

比

快

CREATE TABLE bar ( id int, jsonb foo );

@Craig 在评论中回答了这个问题

GIN indexing is generally less efficient than a b-tree, so that much is expected.

该架构中的空值也应显示为

SELECT jsonb_build_object('marital_status',ARRAY[null]);
     jsonb_build_object     
----------------------------
 {"marital_status": [null]}
(1 row)

而不是{}。 PostgreSQL 使用许多快捷方式来快速更新 jsonb 对象，并使索引空间高效。

如果这些都没有意义，请查看此伪表。

CREATE TABLE foo ( id int, x text, y text, z text )
CREATE INDEX ON foo(x);
CREATE INDEX ON foo(y);
CREATE INDEX ON foo(z);

这里我们在表上有三个 btree 索引。让我们看一个类似的表..

CREATE TABLE bar ( id int, junk jsonb );
CREATE INDEX ON bar USING gin (junk);
INSERT INTO bar (id,junk) VALUES (1,$${"x": 10, "y": 42}$$);

要使 bar 像 foo 一样执行，我们需要两个 btree，它们都比我们拥有的单个 GIN 索引大。如果你这样做了

INSERT INTO bar (id,junk) VALUES (1,$${"x": 10, "y": 42, "z":3}$$);

我们必须在 z 上有另一个 btree 索引，这又将是巨大的。你可以看到我要去哪里。 jsonb 很棒，但索引和模式建模的复杂性与数据库不相称。您不能只将数据库缩减为 jsonb 列，发出 CREATE INDEX 并期望获得相同的性能。