django - 执行不区分大小写的查询时不使用 GIN 三元组索引

Question

• 当我使用 trigram_similar 或 contains 查询表时，会按预期使用索引。

• 当我使用 icontains 查询同一个表时，Django 使用 UPPER 执行顺序扫描。

区别是 3 毫秒和 240 毫秒。

• 我应该创建一个小写索引并匹配 contains 吗？ (这怎么可能？)

• 我是否应该创建一个所有内容都将小写的字段并为该字段编制索引？

• 还有别的吗？

模型:

class Name(models.Model):
    name_en = models.CharField(max_length=127)
    ...

    class Meta:
        indexes = [
            GinIndex(
                name="name_en_gin_trigram",
                fields=["name_en"],
                opclasses=["gin_trgm_ops"],
            )
        ]

• 使用索引的查询:

  >>> Name.objects.filter(
      Q(name_en__contains='eeth')
      | Q(name_en__trigram_similar='eeth')
  )
  
  SELECT *
  FROM "shop_name"
  WHERE ("shop_name"."name_en"::text LIKE '%eeth%' OR "shop_name"."name_en" % 'eeth')
  LIMIT 21;
  

  生成的查询计划:

  Limit  (cost=64.06..90.08 rows=7 width=121) (actual time=0.447..2.456 rows=14 loops=1)
     ->  Bitmap Heap Scan on shop_name  (cost=64.06..90.08 rows=7 width=121) (actual time=0.443..2.411 rows=14 loops=1)
           Recheck Cond: (((name_en)::text ~~ '%eeth%'::text) OR ((name_en)::text % 'eeth'::text))
           Rows Removed by Index Recheck: 236
           Heap Blocks: exact=206
           ->  BitmapOr  (cost=64.06..64.06 rows=7 width=0) (actual time=0.371..0.378 rows=0 loops=1)
               ->  Bitmap Index Scan on name_en_gin_trigram  (cost=0.00..20.03 rows=4 width=0) (actual time=0.048..0.049 rows=15 loops=1)
                     Index Cond: ((name_en)::text ~~ '%eeth%'::text)
               ->  Bitmap Index Scan on name_en_gin_trigram  (cost=0.00..44.03 rows=4 width=0) (actual time=0.318..0.320 rows=250 loops=1)
                     Index Cond: ((name_en)::text % 'eeth'::text)
  Planning Time: 0.793 ms
  Execution Time: 2.531 ms
  (12 rows)
  

• 如果我使用 icontains，则不会使用索引:

  >>> Name.objects.filter(
    Q(name_en__icontains='eeth')
    | Q(name_en__trigram_similar='eeth')
  )
  
  SELECT * 
  FROM "shop_name"
  WHERE (UPPER("shop_name"."name_en"::text) LIKE UPPER('%eeth%') OR "shop_name"."name_en" % 'eeth')
  LIMIT 21;
  

  生成的查询计划:

  Limit  (cost=0.00..95.61 rows=21 width=121) (actual time=10.513..244.244 rows=14 loops=1)
     ->  Seq Scan on shop_name  (cost=0.00..1356.79 rows=298 width=121) (actual time=10.509..244.195 rows=14 loops=1)
           Filter: ((upper((name_en)::text) ~~ '%EETH%'::text) OR ((name_en)::text % 'eeth'::text))
           Rows Removed by Filter: 36774
  Planning Time: 0.740 ms
  Execution Time: 244.299 ms
  (6 rows)
  

Answer 1

Django 使用 UPPER() 运行 icontains，我们可以通过使索引也 UPPER() 来解决这个问题:

使用 GIN (UPPER(col_name) gin_trgm_ops) 在 table_name 上创建索引 upper_col_name_gin_idx

Django 然后会使用这个索引运行 WHERE UPPER("table_name"."col_name"::text) LIKE UPPER('%term%')。