Issue

After the database upgrade to PostgreSQL version 17, complex queries run slower and cause application performance issues.

Environment

Studio Pro (all versions)

PostgreSQL 17

Cause

In PostgreSQL 17, the query planner can select additional parallel execution strategies that were not available in previous versions. In particular, parallel hash JOIN can now be used for RIGHT JOIN and FULL JOIN operations and, due to internal query rewrites, may also affect queries involving LEFT JOINs. While these plans can improve performance in some scenarios, they may expose inefficiencies in complex queries.

These inefficiencies are manifested through modelling decisions made concerning:

• Security columns 
• Association tables
• View entities
• Entity access (Inheritance and Specializations)
• Indexes

Solution / Workaround

To optimise queries, several implementations can be made. These are divided by topics below.

Security Columns 

In Studio Pro version 10.24 and newer, the runtime setting DataStorage.OptimizeSecurityColumns is available. The setting optimises duplicate security subqueries in the SELECT clause of the SQL. Activation of the setting can be done through the following steps:

1. From the Mendix Portal, go to the Environments page of the desired application.
2. Click Details on the environment to enable the setting.

3. In the Runtime tab > Custom Runtime Settings, click on Add.

4. Enter DataStorage.OptimizeSecurityColumns in the Setting field 
5. Set the value to True. 
6. Redeploy or restart the application to apply the changes.  

Direct Associations 

In Studio Pro version 10.24.6 and newer, the association storage type in the model can be set to direct. Direct associations reduce the number of joins per association from two to one for many-to-one associations. This contributes to reducing the complexity of queries and can improve query plan generation. 

We recommend reading the information on prerequisites before switching to direct associations.

View Entities 

Inheritance and Specializations

The use of two or more levels of inheritance and specializations for entity access rules increases the complexity of queries, as an XPath is added per specialisation access rule, and leads to slow queries. 

Instead, the following is recommended:

1. Combine attributes in one entity and add an enumeration instead of setting the generalisation.
2. Create entities with a one-to-one association.
3. Create a non-persistable entity that inherits from an outcome of the application logic.

Indexes 

Indexes improve the speed of retrieving objects if the indexed attributes are used in a search field, the XPath constraint of a data grid or template grid, or a WHERE clause of an OQL query. It is recommended to have an index if an attribute is used in the following circumstances: 

1. A sort column in a data grid

2. A filter in a data grid

3. An XPath constraint of a data source, retrieve action, etc.

4. An XPath constraint of an access rule

There are some performance considerations to evaluate before deciding whether or not to use indexes. 

Additional information

• Mendix documentation:

  • Recommendations from Best Practice Recommender
  • Runtime Customization 
  • Association Storage Options
  • OQL
  • View Entities
  • Generalization vs One-to-One Associations
  • Indexes