Postgres GinIndex doesn't improve performance-CodePudding

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

result of explain

 [{"Plan": {"Node Type": "Nested Loop", "Parallel Aware": false, "Join Type": "Left", "Startup Cost": 2146.87, "Total Cost": 2411.83, "Plan Rows": 56, "Plan Width": 464, "Actual Startup Time": 22.696, "Actual Total Time": 20
5.744, "Actual Rows": 26, "Actual Loops": 1, "Inner Unique": true, "Shared Hit Blocks": 1803, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dir
tied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0,

"Plans": [{"Node Type": "Hash Join", "Parent Relationship": "Outer", "Parallel Aware": false, "Join Type": "Left", "Startup Cost": 2146.72,
 "Total Cost": 2387.93, "Plan Rows": 56, "Plan Width": 331, "Actual Startup Time": 22.661, "Actual Total Time": 205.19, "Actual Rows": 26, "Actual Loops": 1, "Inner Unique": true, "Hash Cond": "(books_book_author.author_id = books_a
uthor.id)", "Filter": "(to_tsvector((((COALESCE(books_book.title, ''::character varying))::text || ' '::text) || (COALESCE(books_author.name, ''::character varying))::text)) @@ plainto_tsquery('harry potter'::text))", "Rows Removed 
by Filter": 11101, "Shared Hit Blocks": 1751, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Tem
p Read Blocks": 0, "Temp Written Blocks": 0,

"Plans": [{"Node Type": "Hash Join", "Parent Relationship": "Outer", "Parallel Aware": false, "Join Type": "Right", "Startup Cost": 1797.36, "Total Cost": 2008.85, "Plan Rows": 11127, "Pl
an Width": 314, "Actual Startup Time": 17.085, "Actual Total Time": 28.099, "Actual Rows": 11127, "Actual Loops": 1, "Inner Unique": true, "Hash Cond": "(books_book_author.book_id = books_book.id)", "Shared Hit Blocks": 1621, "Share
d Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, 

"Plans"
: [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Relation Name": "books_book_author", "Alias": "books_book_author", "Startup Cost": 0.0, "Total Cost": 182.27, "Plan Rows": 11127, "Plan Width": 1
6, "Actual Startup Time": 0.008, "Actual Total Time": 1.567, "Actual Rows": 11127, "Actual Loops": 1, "Shared Hit Blocks": 71, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "
Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}, {"Node Type": "Hash", "Parent Relationship": "Inner", "Parallel Aware": false, "Startup Cost": 1658.27, "
Total Cost": 1658.27, "Plan Rows": 11127, "Plan Width": 306, "Actual Startup Time": 16.964, "Actual Total Time": 16.965, "Actual Rows": 11127, "Actual Loops": 1, "Hash Buckets": 16384, "Original Hash Buckets": 16384, "Hash Batches":
 1, "Original Hash Batches": 1, "Peak Memory Usage": 3894, "Shared Hit Blocks": 1547, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blo
cks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0,

 "Plans": [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Relation Name": "books_book", "Alias": "books_book", 
"Startup Cost": 0.0, "Total Cost": 1658.27, "Plan Rows": 11127, "Plan Width": 306, "Actual Startup Time": 0.986, "Actual Total Time": 8.64, "Actual Rows": 11127, "Actual Loops": 1, "Shared Hit Blocks": 1547, "Shared Read Blocks": 0,
 "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}]}]}, {"Node Type": "Hash"
, "Parent Relationship": "Inner", "Parallel Aware": false, "Startup Cost": 210.27, "Total Cost": 210.27, "Plan Rows": 11127, "Plan Width": 33, "Actual Startup Time": 4.623, "Actual Total Time": 4.624, "Actual Rows": 11127, "Actual L
oops": 1, "Hash Buckets": 16384, "Original Hash Buckets": 16384, "Hash Batches": 1, "Original Hash Batches": 1, "Peak Memory Usage": 881, "Shared Hit Blocks": 99, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written 
Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0,

 "Plans": [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "P
arallel Aware": false, "Relation Name": "books_author", "Alias": "books_author", "Startup Cost": 0.0, "Total Cost": 210.27, "Plan Rows": 11127, "Plan Width": 33, "Actual Startup Time": 0.005, "Actual Total Time": 1.981, "Actual Rows
": 11127, "Actual Loops": 1, "Shared Hit Blocks": 99, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks":
 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}]}]}, {"Node Type": "Index Scan", "Parent Relationship": "Inner", "Parallel Aware": false, "Scan Direction": "Forward", "Index Name": "books_category_pkey", "Relation Name": "books
_category", "Alias": "books_category", "Startup Cost": 0.15, "Total Cost": 0.17, "Plan Rows": 1, "Plan Width": 126, "Actual Startup Time": 0.004, "Actual Total Time": 0.004, "Actual Rows": 1, "Actual Loops": 26, "Index Cond": "(id =
 books_book.category_id)", "Rows Removed by Index Recheck": 0, "Shared Hit Blocks": 52, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied B
locks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}]}, "Planning": {"Shared Hit Blocks": 359, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0,
 "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}, "Planning Time": 1.249, "Triggers": [], "Execution Time": 205.936}]

[{"Plan": {"Node Type": "Nested Loop", "Parallel Aware": false, "Join Type": "Left", "Startup Cost": 2146.87, "Total Cost": 2411.83, "Plan Rows": 56, "Plan Width": 464, "Actual Startup Time": 11.983, "Actual Total Time": 19
0.653, "Actual Rows": 26, "Actual Loops": 1, "Inner Unique": true, "Shared Hit Blocks": 1769, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dir
tied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0,

"Plans": [{"Node Type": "Hash Join", "Parent Relationship": "Outer", "Parallel Aware": false, "Join Type": "Left", "Startup Cost": 2146.72,
 "Total Cost": 2387.93, "Plan Rows": 56, "Plan Width": 331, "Actual Startup Time": 11.946, "Actual Total Time": 190.116, "Actual Rows": 26, "Actual Loops": 1, "Inner Unique": true, "Hash Cond": "(books_book_author.author_id = books_
author.id)", "Filter": "(to_tsvector((((COALESCE(books_book.title, ''::character varying))::text || ' '::text) || (COALESCE(books_author.name, ''::character varying))::text)) @@ plainto_tsquery('harry potter'::text))", "Rows Removed
 by Filter": 11101, "Shared Hit Blocks": 1717, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Te
mp Read Blocks": 0, "Temp Written Blocks": 0,

"Plans": [{"Node Type": "Hash Join", "Parent Relationship": "Outer", "Parallel Aware": false, "Join Type": "Right", "Startup Cost": 1797.36, "Total Cost": 2008.85, "Plan Rows": 11127, "P
lan Width": 314, "Actual Startup Time": 8.637, "Actual Total Time": 18.293, "Actual Rows": 11127, "Actual Loops": 1, "Inner Unique": true, "Hash Cond": "(books_book_author.book_id = books_book.id)", "Shared Hit Blocks": 1618, "Share
d Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, 

"Plans": [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Relation Name": "books_book_author", "Alias": "books_book_author", "Startup Cost": 0.0, "Total Cost": 182.27, "Plan Rows": 11127, "Plan Width": 1
6, "Actual Startup Time": 0.026, "Actual Total Time": 1.417, "Actual Rows": 11127, "Actual Loops": 1, "Shared Hit Blocks": 71, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "
Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}, {"Node Type": "Hash", "Parent Relationship": "Inner", "Parallel Aware": false, "Startup Cost": 1658.27, "
Total Cost": 1658.27, "Plan Rows": 11127, "Plan Width": 306, "Actual Startup Time": 8.595, "Actual Total Time": 8.596, "Actual Rows": 11127, "Actual Loops": 1, "Hash Buckets": 16384, "Original Hash Buckets": 16384, "Hash Batches": 1
, "Original Hash Batches": 1, "Peak Memory Usage": 3894, "Shared Hit Blocks": 1547, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Block
s": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0,

"Plans": [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Relation Name": "books_book", "Alias": "books_book", "S
tartup Cost": 0.0, "Total Cost": 1658.27, "Plan Rows": 11127, "Plan Width": 306, "Actual Startup Time": 0.419, "Actual Total Time": 4.455, "Actual Rows": 11127, "Actual Loops": 1, "Shared Hit Blocks": 1547, "Shared Read Blocks": 0, 
"Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}]}]}, {"Node Type": "Hash",
 "Parent Relationship": "Inner", "Parallel Aware": false, "Startup Cost": 210.27, "Total Cost": 210.27, "Plan Rows": 11127, "Plan Width": 33, "Actual Startup Time": 3.237, "Actual Total Time": 3.237, "Actual Rows": 11127, "Actual Lo
ops": 1, "Hash Buckets": 16384, "Original Hash Buckets": 16384, "Hash Batches": 1, "Original Hash Batches": 1, "Peak Memory Usage": 881, "Shared Hit Blocks": 99, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written B
locks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0,

"Plans": [{"Node Type": "Seq Scan", "Parent Relationship": "Outer", "Pa
rallel Aware": false, "Relation Name": "books_author", "Alias": "books_author", "Startup Cost": 0.0, "Total Cost": 210.27, "Plan Rows": 11127, "Plan Width": 33, "Actual Startup Time": 0.005, "Actual Total Time": 1.361, "Actual Rows"
: 11127, "Actual Loops": 1, "Shared Hit Blocks": 99, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 
0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}]}]}, {"Node Type": "Index Scan", "Parent Relationship": "Inner", "Parallel Aware": false, "Scan Direction": "Forward", "Index Name": "books_category_pkey", "Relation Name": "books_
category", "Alias": "books_category", "Startup Cost": 0.15, "Total Cost": 0.17, "Plan Rows": 1, "Plan Width": 126, "Actual Startup Time": 0.004, "Actual Total Time": 0.004, "Actual Rows": 1, "Actual Loops": 26, "Index Cond": "(id = 
books_book.category_id)", "Rows Removed by Index Recheck": 0, "Shared Hit Blocks": 52, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Bl
ocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}]}, "Planning": {"Shared Hit Blocks": 24, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "
Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0}, "Planning Time": 0.393, "Triggers": [], "Execution Time": 190.715}]

generated sql query

SELECT "books_book"."id",
       "books_book"."category_id",
       "books_book"."title",
       "books_book"."description",
       "books_book"."price",
       "books_book"."publisher",
       "books_book"."language",
       "books_book"."pages",
       "books_book"."isbn",
       "books_book"."cover_image",
       "books_book"."publish",
       "books_book"."created_at",
       "books_book"."updated_at",
       to_tsvector(COALESCE("books_book"."title", '') || ' ' || COALESCE("books_author"."name", '')) AS "search",
       "books_category"."id",
       "books_category"."name"
FROM "books_book"
         LEFT OUTER JOIN "books_book_author" ON ("books_book"."id" = "books_book_author"."book_id")
         LEFT OUTER JOIN "books_author" ON ("books_book_author"."author_id" = "books_author"."id")
         LEFT OUTER JOIN "books_category" ON ("books_book"."category_id" = "books_category"."id")
WHERE to_tsvector(COALESCE("books_book"."title", '') || ' ' || COALESCE("books_author"."name", '')) @@
      (plainto_tsquery('harry potter'))
LIMIT 10

CREATE INDEX book_title_idx ON public.books_book USING gin (title)

CodePudding user response：

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.