Скачать или смотреть How to Optimize Your Django Queries with GIN Trigram Indexes for Case-Insensitive Searches

Ensure your Django app performs efficiently by utilizing GIN Trigram indexes for case-insensitive queries and avoid costly sequential scans.
---
This video is based on the question https://stackoverflow.com/q/65516427/ asked by the user 'raratiru' ( https://stackoverflow.com/u/2996101/ ) and on the answer https://stackoverflow.com/a/68887781/ provided by the user 'for_all_intensive_purposes' ( https://stackoverflow.com/u/4107349/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: GIN trigram index not used when case-insensitive query is performed

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Optimize Your Django Queries with GIN Trigram Indexes for Case-Insensitive Searches

When working with databases in Django, you might notice performance issues, especially with case-insensitive queries using icontains. This could lead to sequential scans, resulting in significant slowdowns compared to queries that utilize an index via contains or trigram_similar. In this guide, we'll explore the reasons behind this performance drop and how you can effectively optimize your queries to ensure they're fast and efficient.

Understanding the Problem

Imagine you have a table in your database and need to search for names using Django’s ORM. You have set up a GIN trigram index on the name_en field to improve performance for specific queries. However, when using the icontains filter to perform a case-insensitive search, you might notice that instead of using the index, Django performs a slow sequential scan. This can be illustrated by comparing the execution times:

Using contains or trigram_similar: 3ms (index used)

Using icontains: 240ms (sequential scan)

The reason behind this behavior is that Django applies the UPPER() function to perform case-insensitive matches, which prevents the index from being utilized effectively.

The Solution

To resolve this performance bottleneck, you can create an index that matches the way Django handles case-insensitive queries. By using an index that also applies UPPER(), you allow PostgreSQL to leverage that index when processing icontains queries.

Steps to Create the Case-Insensitive Index

Create a New Index for Upper Case Values:
You will need to run a SQL command to create the new index on your table. The command would look like this:

[[See Video to Reveal this Text or Code Snippet]]

This SQL statement creates a GIN index on the col_name column of table_name, storing its uppercase representation. By doing this, PostgreSQL can use this index when executing a query with UPPER().

Modify Your Django Query:
After creating the index, you can run your original filter as follows:

[[See Video to Reveal this Text or Code Snippet]]

With the new index in place, Django will utilize it for processing the UPPER(name_en) portion of the query, significantly improving performance.

Benefits of Using Case-Insensitive Indexes

Faster Query Execution: Leveraging the index reduces the execution time from milliseconds to microseconds.

Reduced Server Load: Efficiently handling searches decreases the database load, allowing it to serve more requests promptly.

Improved User Experience: Enhancements in speed lead to a smoother and more responsive application for end-users.

Additional Considerations

Regularly Monitor Query Performance: Always keep an eye on the query execution plans and adjust the indexes as necessary based on your application's needs.

Consider Storage Costs: Adding indexes increases storage use, so ensure that they provide enough performance benefits relative to their storage cost.

Conclusion

By creating a GIN trigram index that applies the UPPER() function, you can dramatically improve the performance of case-insensitive searches in your Django applications. Not only does this change lead to faster query execution times, but it also substantially reduces the server load and enhances the overall user experience. Implement this optimization in your application today to reap the performance benefits!