Poor Performance When Trigram Similarity And Full-text-search Were Combined With Q Ind Django Using Postgres

I'm creating a web application to search people with their properties such as education, experience, etc. I can't use full-text-search for all the fields, because, some has to be a

Solution 1:

Without the class code it's difficult to find the better way to optimize your query.

You can add a Gin or Gist index to speed up the trigram similarity.

You can build an annotation with the SearchVector as below:

from django.contrib.postgres.aggregates import StringAgg
from django.contrib.postgres.search import SearchQuery, SearchVector

search_vectors = (
    SearchVector('vision_expertise') +
    SearchVector('bio_description') +
    SearchVector(StringAgg('experiences__description', delimiter=' ')) +
    SearchVector(StringAgg('educations__description', delimiter=' ')) +
    SearchVector(StringAgg('publications__description', delimiter=' '))
)

Profile.objects.annotate(
    search=search_vectors
).filter(
    Q(search=SearchQuery(search_term)) |
    Q(first_name__trigram_similar=search_term) |
    Q(last_name__trigram_similar=search_term) |
    Q(educations__degree__trigram_similar=search_term) |
    Q(educations__field_of_study__trigram_similar=search_term) |
    Q(educations__school__trigram_similar=search_term) |
    Q(experiences__title__trigram_similar=search_term) |
    Q(experiences__company__trigram_similar=search_term) |
    Q(publications__title__trigram_similar=search_term) |
    Q(certification__certification_name__trigram_similar=search_term) |
    Q(certification__certification_authority__trigram_similar=search_term)
)

You can speed-up the full-text search using a SearchVectorField

To find out about full-text search and trigram you can read the article I wrote on the subject:

"Full-Text Search in Django with PostgreSQL"

Solution 2:

Add Trigram indexes in postgres to improve performance, ie for users.first_name and users.last_name indexing:

CREATE INDEX index_users_full_name
             ON users usinggin((first_name || ' ' || last_name) gin_trgm_ops);