postgresql - Postgres 分层 (jsonb) CTE 不必要地慢

Question

我的表中有一个 JsonB 列，其中包含分层信息。

MyTable (id uuid, indexes jsonb, content bytea)

现在如果我创建一个 CTE 说

WITH RECURSIVE hierarchy(pid, id, content) AS (
  --load first parents
  SELECT t.indexes ->> 'parentId' as pId, t.id, t.content FROM MyTable c
  JOIN MyTable t ON t.indexes ->> 'Id' = c.indexes ->> 'parentId' 
  WHERE c.Id = ANY('{..Some UUIDS}')
  UNION
  SELECT t.indexes ->> 'parentId' as pId, t.id, t.content
  FROM hierarchy h, MyTable t
  WHERE t.indexes ->> 'Id' = h.pid
) SELECT id, content from hierarchy

现在，从 30 万条记录的表中的 2 个节点构建父树的示例运行大约需要 10 秒。

现在如果我创建一个索引

CREATE INDEX MyIndex ON MyTable
USING btree
((indexes ->> 'Id')

这将时间减少到 4.5 秒。这产生了一个分析

    ->  Recursive Union  (cost=23.81..4528423.71 rows=80794929 width=1219) (actual time=0.188..1802.636 rows=5 loops=1)
          ->  Nested Loop  (cost=23.81..3150.15 rows=899 width=1219) (actual time=0.132..0.133 rows=1 loops=1)
                Output: (t.indexes ->> 'parentId'::text), t.id, t.content
                ->  Index Scan using "MyTable_pkey" on "TEST"."MyTable" c  (cost=0.42..8.44 rows=1 width=123) (actual time=0.053..0.053 rows=1 loops=1)
                      Output: c.id, c.content, c.indexes
                      Index Cond: (c.id = ANY ('{1c725f08-0324-41e9-b417-5ec885fb1cc9}'::uuid[]))
                ->  Bitmap Heap Scan on "TEST"."MyTable" t  (cost=23.39..3130.48 rows=899 width=1219) (actual time=0.066..0.066 rows=1 loops=1)
                      Output: t.id, t.content, t.indexes
                      Recheck Cond: (((t.indexes ->> 'Id'::text) = (c.indexes ->> 'parentId'::text)))
                      Heap Blocks: exact=1
                      ->  Bitmap Index Scan on "MyIndex"  (cost=0.00..23.17 rows=899 width=0) (actual time=0.055..0.055 rows=1 loops=1)
                            Index Cond: ((t.indexes ->> 'Id'::text) = (c.indexes ->> 'parentId'::text))

//UNION PART
          ->  Merge Join  (cost=770.60..290937.50 rows=8079403 width=1219) (actual time=360.467..360.476 rows=1 loops=5)
                Output: (t_1.indexes ->> 'parentId'::text), t_1.id, t_1.content
                Merge Cond: ((t_1.indexes ->> 'Id'::text) = h.pid)
                ->  Index Scan using "MyIndex" on "TEST"."MyTable" t_1  (cost=0.42..127680.55 rows=179742 width=1219) (actual time=0.019..288.168 rows=60478 loops=5)
                      Output: t_1.id, t_1.sourceid, t_1.content, t_1.indexes
                ->  Sort  (cost=770.18..792.65 rows=8990 width=32) (actual time=0.010..0.011 rows=1 loops=5)
                      Output: h.pid
                      Sort Key: h.pid
                      Sort Method: quicksort  Memory: 25kB
                      ->  WorkTable Scan on hierarchy h  (cost=0.00..179.80 rows=8990 width=32) (actual time=0.001..0.001 rows=1 loops=5)
                            Output: h.pid

现在我可以通过用 cte 中的函数替换索引 ->> 'parentId' 并在该函数上创建索引来获得巨大的速度改进。

CREATE FUNCTION "TEST"."MyFunction"(idarg uuid)
  RETURNS text AS
$BODY$ 
SELECT t.indexes ->> 'Id' as result FROM "TEST"."MyTable" t 
WHERE t.id = idarg 
$BODY$
LANGUAGE sql IMMUTABLE;

有索引

CREATE INDEX MyFunctionIndex ON MyTable
USING btree
(MyFunction(id))

现在执行查询需要 0.01 秒通过分析

->  Recursive Union  (cost=23.81..5333205.06 rows=80794929 width=1219) (actual time=0.163..0.291 rows=5 loops=1)
      ->  Nested Loop  (cost=23.81..3372.65 rows=899 width=1219) (actual time=0.082..0.084 rows=1 loops=1)
            Output: (t.indexes ->> 'parentId'::text), t.id, t.content, t.modified
            ->  Index Scan using "MyTable_pkey" on "TEST"."MyTable" c  (cost=0.42..8.44 rows=1 width=123) (actual time=0.019..0.019 rows=1 loops=1)
                  Output: c.id, c.sourceid, c.viewid, c.content, c.indexes, c.statekey, c.modified
                  Index Cond: (c.id = ANY ('{1c725f08-0324-41e9-b417-5ec885fb1cc9}'::uuid[]))
            ->  Bitmap Heap Scan on "TEST"."MyTable" t  (cost=23.39..3352.98 rows=899 width=1219) (actual time=0.037..0.037 rows=1 loops=1)
                  Output: t.id, t.content, t.indexes
                  Recheck Cond: (("TEST"."MyFunction"(t.id) = (c.indexes ->> 'parentId'::text)))
                  Heap Blocks: exact=1
                  ->  Bitmap Index Scan on "MyFunctionIndex"  (cost=0.00..23.17 rows=899 width=0) (actual time=0.025..0.025 rows=1 loops=1)
                        Index Cond: ("TEST"."MyFunction"(t.id) = (c.indexes ->> 'parentId'::text))

//UNION PART
          ->  Nested Loop  (cost=0.42..371393.38 rows=8079403 width=1219) (actual time=0.012..0.013 rows=1 loops=5)
                Output: (t_1.indexes ->> 'parentId'::text), t_1.id, t_1.content
                ->  WorkTable Scan on hierarchy h  (cost=0.00..179.80 rows=8990 width=32) (actual time=0.000..0.000 rows=1 loops=5)
                      Output: h.pid, h.id, h.content
                ->  Index Scan using "MyFunctionIndex" on "TEST"."MyTable" t_1  (cost=0.42..30.06 rows=899 width=1219) (actual time=0.010..0.010 rows=1 loops=5)
                      Output: t_1.id, t_1.content, t_1.indexes
                      Index Cond: ("TEST"."MyFunction"(t_1.id) = h.pid)

那么为什么索引的运行速度不如函数索引呢？
那里似乎有一种多余的东西。我不想只使用函数索引的原因是它是 IMMUTABLE，因此索引不会在 INSERT/UPDATE/DELETE 后自动更新。

PS 我不是在寻找架构更改建议。

Answer 1

看起来 Gin 索引表现良好。如果我在 indexes 列上创建一个 Gin 索引，然后将 Join 更改为

ON t.indexes @> jsonb_build_object('Id', c.indexes -> 'parentId')

去哪里

WHERE t.indexes @> jsonb_build_object('Id', h.pid)

它不如纯函数索引快，但至少它会动态更新，并且执行计划没有那种不必要的排序

可以通过添加 gin 索引标志 jsonb_path_ops 进一步提高性能