NX_IN_TREE stored procedure does not scale on PG and mssql

The NX_IN_TREE is an SQL stored procedure used when the PathOptimizations are disabled, this procedure does not scale.

To get the list of children of a document the generated SQL query looks like:

SELECT id FROM hierarchy WHERE NX_IN_TREE(id, $1)

The results is O(n) where n is the number of document in the repository.
This makes pathOptimizations (materialized path) mandatory for large volume but pathOptimizations have downsides, like creating very big table and requires complex trigger logic.

The list of children is used:

• on deletion to remove all children from the VCS cache
• when using the NXQL STARTSWITH operator

A solution is to use a Common Table Expression (CTE or CONNECT BY PRIOR on Oracle this is already done in NXP-13707). This will make the implementation O(n) where n is the number of children which is acceptable at least for the deletion use case.

The STARTSWITH operation can be work around at higher level using Elasticsearch addon for instance.