Named queries can include multiple linked entities. Those can be available through different paths depending on the defined model.
Depending on those different paths, execution time can be very different.
Even though the difference in path is not that significant.
This troubleshooting article will help you explore a lead to decrease this execution duration on PostgreSQL.
Issue
With PostgreSQL, little differences in named queries configuration related to modeling can lead to very significant differences in execution time.
Cause
Even though the tool generates the most optimized possible query based on the named query configuration, PostgreSQL has a native optimizer whose algorithm tries to rewrite the join order.
And when the form clause includes several instances of the same table, this rewrite can be very inefficient. Thus leading to lengthy execution times.
Solution
Setting the session parameter join_collapse_limit to 1 will prevent PostgreSQL from rewriting the initial query provided by Semarchy.
To know more about this parameter, here is the link to PostgreSQL's documentation.
Detailed solution
On the database side
Run below instruction to reset the session parameter:
set join_collapse_limit=1;
On Semarchy side
For this parameter to be considered by Semarchy, it is required to add it in the data source configuration:
- Browse to Configuration > Datasources > Advanced > Connection Initialization Query
- Add below instruction:
set join_collapse_limit=1;commit
