Critical Description
Archetype used as collection and custom menu marking for example "internal" users takes forever to load table with users (or timeouts).
Therefore this UI customization is not usable. Is it possible to optimize such query? Query for oracle looks like this:
SELECT
ruser0_.oid AS col_0_0_,
ruser0_2_.fullobject AS col_1_0_
FROM
m_user ruser0_
INNER JOIN m_focus ruser0_1_ ON ruser0_.oid = ruser0_1_.oid
INNER JOIN m_object ruser0_2_ ON ruser0_.oid = ruser0_2_.oid
WHERE
ruser0_.oid IN(
SELECT DISTINCT
ruser1_.oid
FROM
m_user ruser1_
INNER JOIN m_focus ruser1_1_ ON ruser1_.oid = ruser1_1_.oid
INNER JOIN m_object ruser1_2_ ON ruser1_.oid = ruser1_2_.oid
LEFT OUTER JOIN m_reference archetyper2_ ON ruser1_.oid = archetyper2_.owner_oid
AND
(archetyper2_.reference_type = 11)
WHERE
archetyper2_.targetoid = :1
AND
(archetyper2_.relation IN(:2,
:3,
:4))
)
FETCH FIRST :5 ROWS ONLY
Index range scan is used on IREFERENCETARGETTYPERELATION. Archetype is assigned to 500k users. We also need to assign another archetype to 12mil users.