Postgres GinIndex doesn't improve performance

I'm trying to improve search query time by using postgres ginindex. but it doesn't do anything and query time is the same with or without index. It's the first time I'm using index and I'm not sure what I'm doing wrong.

models.py

class Book(models.Model):
    author = models.ManyToManyField(Author, related_name='books')
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True)
    title = models.CharField(max_length=1000)
    description = models.TextField(max_length=3000)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    publisher = models.CharField(max_length=1000)
    language = models.CharField(max_length=200)
    pages = models.PositiveSmallIntegerField()
    isbn = models.CharField(max_length=13, validators=[MaxLengthValidator(13)])
    cover_image = models.ImageField(upload_to='books/images', null=True)
    publish = models.BooleanField(default=True)
    favorite = models.ManyToManyField(settings.AUTH_USER_MODEL, related_name='favorite_books', blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = BookManager()

    def __str__(self):
        return self.title

    class Meta:
        indexes = [
            GinIndex(fields=['title'], name='book_title_idx')
        ]

views.py

    def get_queryset(self):
        query = self.request.query_params.get("search", None)

        if query:
            
            books = Book.objects.annotate(
                search=SearchVector("title", "author__name"),
            ).filter(search=query)
            
            print(books.explain(analyze=True))

            return books
        else:
            return self.queryset

I also added BtreeGinExtension to the migration file

Your index doesn't match your query. To support that query, the index would need to be on the expression:

to_tsvector(COALESCE("books_book"."title", '') || ' ' || COALESCE("books_author"."name", ''))

But that won't work, as to_tsvector with one argument is not immutable--it depends on the default_text_search_config setting. So instead you would need to provide 'english' (or whatever the suitable value is) as a first argument, but then need to make the query match that.

Back to Top