В моей таблице есть столбец 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

Теперь примерный прогон построения родительского дерева из 2-х узлов в таблице записей по 300 КБ занимает примерно 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

Теперь я могу добиться значительных улучшений в скорости, заменив индексы - >> 'parentId' на функцию в cte и создав индекс для функции.

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 Я не ищу предложений по изменению схемы.