default TREE_CHILDREN querymodel generates a very expensive complex JOIN when browsing folders with many documents (>5000)

When using the core sql search with the querymodel based tree manager, the default TREE_CHILDREN querymodel with definition:

<queryModel name="TREE_CHILDREN">
<pattern>
SELECT * FROM Document WHERE ecm:parentId = ?
AND ecm:isProxy = 0
AND ecm:mixinType = 'Folderish'
AND ecm:mixinType != 'HiddenInNavigation'
AND ecm:isCheckedInVersion = 0
AND ecm:currentLifeCycleState != 'deleted'
</pattern>
<sortable value="true" defaultSortColumn="dc:title"
defaultSortAscending="true" />
<max>50</max>
</queryModel>

Generates the following SQL request in the backend:

SELECT DISTINCT "_nxhier"."id", "dublincore"."title" FROM "hierarchy" "_nxhier" LEFT JOIN "proxies" ON "proxies"."id" = "_nxhier"."id" JOIN "hierarchy" ON ("hierarchy"."id" = "_nxhier"."id" OR "hierarchy"."id" = "proxies"."targetid") LEFT JOIN "dublincore" ON "hierarchy"."id" = "dublincore"."id" LEFT JOIN "versions" ON "_nxhier"."id" = "versions"."id" LEFT JOIN "misc" ON "hierarchy"."id" = "misc"."id" WHERE "hierarchy"."primarytype" IN ('NewFolder', 'Workspace', 'Section', 'SectionExt', 'OrderedFolder', 'FolderTemplate', 'WorkspaceRoot', 'Domain', 'TemplateRoot', 'TextoFolder', 'GestionRoot', 'SectionOrg', 'Folder', 'SectionRoot') AND ("_nxhier"."parentid" = 'fa59bf57-d89c-4bb6-92bd-6838f54e3ace') AND ("versions"."id" IS NULL) AND ("misc"."lifecyclestate" <> 'deleted') AND NX_ACCESS_ALLOWED("_nxhier"."id", '

{administrators,Administrator,Everyone}

', '

{Browse,Ecriture,Gestion,Lecture,Read,ReadProperties,ReadRemove,ReadWrite,Everything}

') ORDER BY"dublincore"."title";

This request takes more than 20s on a folder with 20000 documents. The culprit is the LEFT JOIN on the proxies table which is useless to compute the tree nodes since folders cannot be proxies in the default implementation of Nuxeo.

Adding the predicate "ecm:isProxy = 0" to the TREE_CHILDREN definition make the same request last around 200ms.