Postgres hierarchical (jsonb) CTE unnecessarily sl

问题:

I have a JsonB column in my table which holds hierarchical information.

MyTable (id uuid, indexes jsonb, content bytea)

Now if I create a CTE say

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

Now an example run of building a parent tree from 2 nodes within a table of 300K records takes approximately 10s.

Now if I create an index

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

This reduces the time to 4.5s. This produces an analysis of

    ->  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

Now I can get massive speed impovements by replacing indexes ->> 'parentId' with a function in the cte, and creating an index on the function.

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;

With an index

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

This now takes 0.01s to execute the query With Analysis

->  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)

So why cant the index run as fast as the functionindex?
There seems to be a superfluous sort in there. And the reason I dont want to just use the function index is that it is IMMUTABLE so the index wont auto-update after INSERT/UPDATE/DELETE.

PS I am not looking for schema change suggestions.

回答1:

Looks like Gin indexes behave well. If I create a Gin index on the indexes column, and then change the Join to

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

And the Where to

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

Its not as fast as the purely function index, but it least it will update dynamically, and the execution plan doesnt have that unnecessary sort

Further performance improvements can be made by adding the gin index flag jsonb_path_ops