在 Django 模型中使用 Trigram (gin_trgm_ops) 创建 Gin 索引

django.contrib.postgres 的新 TrigramSimilarity 功能非常适合解决我遇到的问题。我用它作为搜索栏来查找难以拼写的拉丁名字。问题是名字超过 200 万个，搜索时间比我想要的要长。

我想在三元组上创建一个索引，如postgres 文档 https://www.postgresql.org/docs/9.6/static/pgtrgm.html.

但我不确定如何以 Django API 使用它的方式做到这一点。对于 postgres 文本搜索有关于如何创建索引的描述，但没有关于三元相似度 https://docs.djangoproject.com/en/dev/ref/contrib/postgres/search/#trigram-similarity.

这就是我现在所拥有的：

class NCBI_names(models.Model):
    tax_id          =   models.ForeignKey(NCBI_nodes, on_delete=models.CASCADE, default = 0)
    name_txt        =   models.CharField(max_length=255, default = '')
    name_class      =   models.CharField(max_length=32, db_index=True, default = '')

    class Meta:
        indexes = [GinIndex(fields=['name_txt'])]

在视图的get_queryset method:

class TaxonSearchListView(ListView):    
    #form_class=TaxonSearchForm
    template_name='collectie/taxon_list.html'
    paginate_by=20
    model=NCBI_names
    context_object_name = 'taxon_list'

    def dispatch(self, request, *args, **kwargs):
        query = request.GET.get('q')
        if query:
            try:
                tax_id = self.model.objects.get(name_txt__iexact=query).tax_id.tax_id
                return redirect('collectie:taxon_detail', tax_id)
            except (self.model.DoesNotExist, self.model.MultipleObjectsReturned) as e:
                return super(TaxonSearchListView, self).dispatch(request, *args, **kwargs)
        else:
            return super(TaxonSearchListView, self).dispatch(request, *args, **kwargs)
    
    def get_queryset(self):
        result = super(TaxonSearchListView, self).get_queryset()
        #
        query = self.request.GET.get('q')
        if query:            
            result = result.exclude(name_txt__icontains = 'sp.')
            result = result.annotate(similarity=TrigramSimilarity('name_txt', query)).filter(similarity__gt=0.3).order_by('-similarity')
        return result

我找到了一个12/2020 文章 https://blacksheephacks.pl/optimizing-django-database-queries-part-2/它使用最新版本的 Django ORM，如下所示：

class Author(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

    class Meta:
        indexes = [
            GinIndex(
                name='review_author_ln_gin_idx', 
                fields=['last_name'], 
                opclasses=['gin_trgm_ops'],
            )
        ]

如果像原始海报一样，您想要创建一个与 icontains 一起使用的索引，则必须对列的 UPPER() 进行索引，这需要从OpClass https://docs.djangoproject.com/en/dev/ref/contrib/postgres/indexes/#opclass-expressions:

from django.db.models.functions import Upper
from django.contrib.postgres.indexes import GinIndex, OpClass

class Author(models.Model):
        indexes = [
            GinIndex(
                OpClass(Upper('last_name'), name='gin_trgm_ops'),
                name='review_author_ln_gin_idx',
            )
        ]

灵感来自于旧文章 http://rogueleaderr.com/post/50576527241/postgres-fuzzy-search-using-trigrams-django在这个问题上，我想到了一个当前的一个 https://vxlabs.com/2018/01/31/creating-a-django-migration-for-a-gist-gin-index-with-a-special-index-operator/这给出了以下解决方案GistIndex:

更新： 从 Django-1.11 开始，事情似乎更简单了，因为这个答案 https://stackoverflow.com/a/43794092/2996101 and Django 文档 https://docs.djangoproject.com/en/dev/ref/contrib/postgres/indexes/#ginindex sugest:

from django.contrib.postgres.indexes import GinIndex

class MyModel(models.Model):
    the_field = models.CharField(max_length=512, db_index=True)

    class Meta:
        indexes = [GinIndex(fields=['the_field'])]

From Django-2.2 https://docs.djangoproject.com/en/dev/ref/models/indexes/#module-django.db.models.indexes，一个属性opclasses将在class Index(fields=(), name=None, db_tablespace=None, opclasses=()) https://docs.djangoproject.com/en/dev/ref/models/indexes/#django.db.models.Index以此目的。

from django.contrib.postgres.indexes import GistIndex

class GistIndexTrgrmOps(GistIndex):
    def create_sql(self, model, schema_editor):
        # - this Statement is instantiated by the _create_index_sql()
        #   method of django.db.backends.base.schema.BaseDatabaseSchemaEditor.
        #   using sql_create_index template from
        #   django.db.backends.postgresql.schema.DatabaseSchemaEditor
        # - the template has original value:
        #   "CREATE INDEX %(name)s ON %(table)s%(using)s (%(columns)s)%(extra)s"
        statement = super().create_sql(model, schema_editor)
        # - however, we want to use a GIST index to accelerate trigram
        #   matching, so we want to add the gist_trgm_ops index operator
        #   class
        # - so we replace the template with:
        #   "CREATE INDEX %(name)s ON %(table)s%(using)s (%(columns)s gist_trgrm_ops)%(extra)s"
        statement.template =\
            "CREATE INDEX %(name)s ON %(table)s%(using)s (%(columns)s gist_trgm_ops)%(extra)s"

        return statement

然后您可以在模型类中使用它，如下所示：

class YourModel(models.Model):
    some_field = models.TextField(...)

    class Meta:
        indexes = [
            GistIndexTrgrmOps(fields=['some_field'])
        ]