Improving CONTAINS and ENDWIDTH search performance
Currently searching using CONTAINS or ENDWITH on large tables is having a negative impact on database performance.
PostgreSQL provides the pg_trgm extension to allow creation of GIST and GIN indexes.
A requirement to make use of a GIN index, is that the SQL queries should use the ILIKE function instead of the LIKE function.
The good news is that Mendix is already generating ILIKE for contains searches, as explained below in this idea.
All that is needed to improve performance for such searches is the ability to be able to define GIN indexes in the Mendix Studio Pro.
Is it possible to implement this in the Domain Model functionality to assign a GIN index to an attribute and have the queries generate ILIKE in such case?
This would tremendously improve the performance for Contains and Endswith searches.
How a solution for this could look like:
- In the Domain Model / Entity Properties / Indexes, add a checkbox next to Ascending for Optimize for CONTAINS:
- Low code to handle the database setup (e.g. update the DDL as explained above when creating indexes on the db)
Background info / research
One of our team members (Thanks Ilya B.!) figured out that CONTAINS already generates an ILIKE based SQL statement:
Thanks @Chris de Gelder, that would be even better if the runtime handles this.
Right now the runtime is generating the right SQL syntax using the ILIKE operator, only there is no option to add the explained GIN index that would allow the database to deliver enormous performance improvement. And we don’t want to go into the grey area of manually adding this kind of indexes on the database level.
Nice Idea, but the runtime should take care of applying this instead of the consultant. In case a Contains is used on a regular base the runtime should add this index.