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.