У меня есть столбец 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)
Так почему же индекс не может работать так же быстро, как индекс функций?
Кажется, там есть лишняя сортировка. И причина, по которой я не хочу просто использовать индекс функции, заключается в том, что он НЕИЗМЕНЕН, поэтому индекс не будет автоматически обновляться после INSERT/UPDATE/DELETE.
PS Я не ищу предложений по изменению схемы.
IMMUTABLE
волатильностью функции. Попробуйте изменить его наSTABLE
и посмотрите, изменится ли ситуация и как. Если вы объявите его какVOLATILE
, все улучшения исчезнут. Также, как писал a_horse_with_no_name, опубликуйте полный план выполнения, чтобы иметь полную картину. - person Eggplant   schedule 07.01.2016