Test a multicolumn GIN index on ElementPath
When accessing documents_elementpath, we are always using both the element_id and the path columns: when looking for children, we will filter on the whole path (recursive) or on path__last (non-recursive) and join using element_id, and when looking for parents, we filter on element_id and join using path.
Instead of just having GIN (path), we could have a multicolumn GIN (element_id, path) or a GIN (path) INCLUDE (element_id). This might require the btree_gin extension to support an UUID in the index. This means that instead of performing an Index Scan, many queries could instead use an Index Only Scan, which only reads from an index without reading from the whole table. This could avoid an extra step in the queries.
Edited by Erwan Rouchet