Huge OQL performance drop in Mendix 9

[Edit: solved. The WHERE constraint used a direct reference to the association, which has worked since the early Mendix days, apparently as an ondocumented feature, but no more. Easily replaceable by an inner join of course]   We noticed a severe performance drop in the execution of an OQL between Mendix 8.18 en Mendix 9.24.10. When we feed Core.retrieveOQLDataTable the (somewhat simplified) OQL below, this used to take not even a second, while now it takes an hour or more. We don't see this yet with other queries. Database indexes haven't changed. With a very small database it still works locally (PostgreSQL 13). Does anyone have an explanation? Maybe something changed in Mendix internals for retrieveOQLDataTable, e.g. with regard to sorting, subqueries, case statements? Or does Mendix use an updated PostgreSQL in Cloudv4 that may execute this differently? Is there a way to debug this further, like setting some log node levels? I tried DataStorage_QueryHandling, which gives me the OQL that I already know, but not much else.   Our OQL:   SELECT AliasOne/ID as AliasOne FROM ModuleOne.MainObject AS AliasOne WHERE AliasOne/ModuleOne.MainObject_Parent = '34191371353572909'
1 answers

As stated in the edited OP: the WHERE constraint on association caused this. The constraint was like:

WHERE AliasOne/ModuleOne.MainObject_Parent = '34191371353572909'


In the resulting SQL query an inner join is added automatically. Between Mx8 en Mx9 there are two differences:

- aliases are CamelCase instead of lowercase (not important)

- the inner join used to be put at the beginning of the joins, right after the FROM statement, while in Mx9 it's appended at the end of the joins, just before the WHERE part


The order of the joins apparently has a big impact on performance, at least for our query (which also has a lot of LEFT OUTER JOIN lines).


It's easy to prevent all this by writing your own INNER JOIN, preferably as the first join, instead of relying on Mendix/Postgres to add it automatically based on the WHERE part.