The CreateElementChildren validation does not use indexes
The validation code for CreateElementChildren (#1816 (closed)) runs a query that is supposed to use the GIN index on ElementPath to check whether an element has any children. However, depending on the query planner's whims, it may just decide to use a sequential scan instead, thinking that it is much faster even when there are millions of paths in the DB:
Index Scan using documents_element_pkey on documents_element (cost=0.56..65.44 rows=2 width=18)
Index Cond: (id = ANY ('{adbe8bd3-0be3-4a42-a3be-97ddb45bcf45,7852c221-6fc9-40b9-b4ba-70aa8af979dd}'::uuid[]))
SubPlan 1
-> Seq Scan on documents_elementpath u0 (cost=0.00..453093.06 rows=100494 width=0)
Filter: (path && ARRAY[documents_element.id])
When using both the GIN index with path__overlap
and the B-tree index on path__last
, the estimated cost is higher, but the execution time is far lower since it uses an index:
Index Scan using documents_element_pkey on documents_element (cost=0.56..2222.06 rows=5 width=18)
Index Cond: (id = ANY ('{adbe8bd3-0be3-4a42-a3be-97ddb45bcf45,7852c221-6fc9-40b9-b4ba-70aa8af979dd}'::uuid[]))
SubPlan 1
-> Bitmap Heap Scan on documents_elementpath u0 (cost=14.17..857.50 rows=2 width=0)
Recheck Cond: (path[array_length(path, 1)] = documents_element.id)
Filter: (path && ARRAY[documents_element.id])
-> Bitmap Index Scan on element_path_last (cost=0.00..14.17 rows=214 width=0)
Index Cond: (path[array_length(path, 1)] = documents_element.id)
This issue may also be affecting has_children
on element lists, so this needs to be investigated further.