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.