Mendix upgraded the platform PostgreSQL version from 15 to 17, causing complex queries to run slowly. The performance issues were due to a major change to how the query planner executes queries introduced in PostgreSQL 16 and present in PostgreSQL 17. 

To understand the root cause, it is important to consider how both the Mendix query engine and the PostgreSQL query planner work together. Mendix translates XPath expressions into SQL queries, allowing applications to retrieve and manipulate data without requiring developers to write SQL directly. PostgreSQL then analyzes these generated queries and determines the most efficient execution plan by evaluating factors such as index usage, JOIN orders, and JOIN methods. The overall performance therefore depends not only on the XPath logic defined in Mendix, but also on how PostgreSQL optimizes and executes the resulting SQL queries.

Query performance is heavily influenced by the complexity of the generated SQL. Modelling in Studio Pro without adhering to the recommended best practices can lead to increased levels of abstraction. The main contributors to this are JOINS, NULL values, sub-queries, indexes, and entity access.

Starting with PostgreSQL 16, the query planner can choose additional parallel execution strategies that were not allowed in older PostgreSQL versions, exposing non-optimal complex queries. In PostgreSQL 15, the query planner would not generate partial parallel hash JOIN plans for RIGHT JOIN and FULL JOIN operations. However, in newer PostgreSQL versions, this restriction was removed, allowing the optimizer to consider parallel hash joins for these join types as well. Since PostgreSQL can internally rewrite a LEFT JOIN into an equivalent RIGHT JOIN queries that previously could not use this execution strategy in older versions may now do so in PostgreSQL 16 and later.

This fundamentally changes the processing of queries. For example, a key aspect of this behavior is how parallel hash joins handle rows containing NULL values in the join key. For RIGHT and FULL JOIN, PostgreSQL must retain unmatched rows from one side of the join, which means rows with NULL JOIN keys can be inserted into the hash table. Internally, all NULL values are consistently assigned to the same partition during repartitioning.

As a result, if a query is executed using a new parallel JOIN plan that involves rows containing empty (NULL) values in the JOIN key, applications experience substantial slowdowns, resulting in increased database load and reduced responsiveness.