在Django模型中用Trigram (gin_trgm_ops)创建Gin索引
在Django模型中用Trigram (gin_trgm_ops)创建Gin索引
提问于 2017-06-29 08:46:19
对于我遇到的一个问题，django.contrib.postgres的新django.contrib.postgres特性是很好的。我用它来搜索栏，找出很难拼写拉丁语的名字。问题是有超过200万的名字，而且搜索时间比我想要的要长。
我想在postgres文件中创建一个三叉图的索引。
但我不知道如何做到Django API会利用它。对于postgres文本搜索，有关于如何创建索引的说明，但对三图相似没有描述。
这就是我现在拥有的：
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方法中：
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回答 5
Stack Overflow用户
回答已采纳
发布于 2018-08-16 15:31:19
我找到了一个12/2020条款，它使用了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'],
)
]如果像最初的海报一样，您希望创建一个与图标相关的索引，则必须对列的上方()进行索引，这需要OpClass的特殊处理
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',
)
]受旧文章关于这个主题的启发，我选择了一个目前的，它为GistIndex提供了以下解决方案
更新:来自Django-1.11的事情似乎更简单，因为这个答案和姜戈博士是最简单的：
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'])]在Django-2.2中，将有一个属性opclasses在class Index(fields=(), name=None, db_tablespace=None, opclasses=())中用于此目的。
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'])
]Stack Overflow用户
发布于 2017-07-07 05:08:04
我也遇到了类似的问题，试图使用pg_tgrm扩展来支持高效的contains和icontains Django字段查找。
也许有一种更优雅的方法，但是像这样定义一个新的索引类型对我有用：
from django.contrib.postgres.indexes import GinIndex
class TrigramIndex(GinIndex):
def get_sql_create_template_values(self, model, schema_editor, using):
fields = [model._meta.get_field(field_name) for field_name, order in self.fields_orders]
tablespace_sql = schema_editor._get_index_tablespace_sql(model, fields)
quote_name = schema_editor.quote_name
columns = [
('%s %s' % (quote_name(field.column), order)).strip() + ' gin_trgm_ops'
for field, (field_name, order) in zip(fields, self.fields_orders)
]
return {
'table': quote_name(model._meta.db_table),
'name': quote_name(self.name),
'columns': ', '.join(columns),
'using': using,
'extra': tablespace_sql,
}方法get_sql_create_template_values是从Index.get_sql_create_template_values()复制的，只需做一个修改:添加+ ' gin_trgm_ops'。
对于您的用例，您将使用这个TrigramIndex而不是一个GinIndex在TrigramIndex上定义索引。然后运行makemigrations，这将生成生成所需的CREATE INDEX SQL的迁移。
更新：
我看到您也在使用icontains进行查询
result.exclude(name_txt__icontains = 'sp.')Postgresql后端将将其转换为如下所示：
UPPER("NCBI_names"."name_txt"::text) LIKE UPPER('sp.')然后，由于UPPER()的存在，不会使用trigram索引。
我也遇到了同样的问题，最后对数据库后端进行子类处理：
from django.db.backends.postgresql import base, operations
class DatabaseFeatures(base.DatabaseFeatures):
pass
class DatabaseOperations(operations.DatabaseOperations):
def lookup_cast(self, lookup_type, internal_type=None):
lookup = '%s'
# Cast text lookups to text to allow things like filter(x__contains=4)
if lookup_type in ('iexact', 'contains', 'icontains', 'startswith',
'istartswith', 'endswith', 'iendswith', 'regex', 'iregex'):
if internal_type in ('IPAddressField', 'GenericIPAddressField'):
lookup = "HOST(%s)"
else:
lookup = "%s::text"
return lookup
class DatabaseWrapper(base.DatabaseWrapper):
"""
Override the defaults where needed to allow use of trigram index
"""
ops_class = DatabaseOperations
def __init__(self, *args, **kwargs):
self.operators.update({
'icontains': 'ILIKE %s',
'istartswith': 'ILIKE %s',
'iendswith': 'ILIKE %s',
})
self.pattern_ops.update({
'icontains': "ILIKE '%%' || {} || '%%'",
'istartswith': "ILIKE {} || '%%'",
'iendswith': "ILIKE '%%' || {}",
})
super(DatabaseWrapper, self).__init__(*args, **kwargs)Stack Overflow用户
发布于 2019-07-15 11:26:04
要使Django 2.2使用索引进行icontains和类似的搜索：
子类GinIndex使大小写不敏感索引(大写所有字段值)：
from django.contrib.postgres.indexes import GinIndex
class UpperGinIndex(GinIndex):
def create_sql(self, model, schema_editor, using=''):
statement = super().create_sql(model, schema_editor, using=using)
quote_name = statement.parts['columns'].quote_name
def upper_quoted(column):
return f'UPPER({quote_name(column)})'
statement.parts['columns'].quote_name = upper_quoted
return statement将索引添加到这样的模型中，包括使用name时所需的kwarg opclasses
class MyModel(Model):
name = TextField(...)
class Meta:
indexes = [
UpperGinIndex(fields=['name'], name='mymodel_name_gintrgm', opclasses=['gin_trgm_ops'])
]生成迁移并编辑生成的文件：
# Generated by Django 2.2.3 on 2019-07-15 10:46
from django.contrib.postgres.operations import TrigramExtension # <<< add this
from django.db import migrations
import myapp.models
class Migration(migrations.Migration):
operations = [
TrigramExtension(), # <<< add this
migrations.AddIndex(
model_name='mymodel',
index=myapp.models.UpperGinIndex(fields=['name'], name='mymodel_name_gintrgm', opclasses=['gin_trgm_ops']),
),
]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44820345
复制相关文章
相似问题
腾讯云开发者
