Django 三元组 : create gin index and search suggested words in Django

Question

我有带有标题和描述字段的模型。

我想为标题和描述字段中的所有单词创建一个 GIN 索引

所以我使用 SQL 按以下方式执行:

第 1 步:使用简单的配置创建一个包含标题和描述中所有单词的表

CREATE TABLE words AS SELECT word FROM  ts_stat('SELECT to_tsvector(''simple'',COALESCE("articles_article"."title", '''')) || to_tsvector(''simple'',COALESCE("articles_article"."description", '''')) FROM "articles_article"');

STEP2:创建GIN索引

CREATE INDEX words_idx ON words USING GIN (word gin_trgm_ops);

第三步:搜索

SELECT word, similarity(word, 'sri') AS sml
  FROM words
  WHERE word % 'sri'
  ORDER BY sml DESC, word;

Result:

word  sml
sri 1
srila 0.5
srimad  0.428571

如何在 DJANGO 中执行此操作，而且我必须不断更新 GIN 索引

Answer 1

Django docs建议你安装相关btree_gin_extension并将以下内容附加到模型的 Meta 类中:

from django.contrib.postgres.indexes import GinIndex

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

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

可以找到相关答案here .

关于索引的更新，heroku建议:

Finally, indexes will become fragmented and unoptimized after some time, especially if the rows in the table are often updated or deleted. In those cases it may be required to perform a REINDEX leaving you with a balanced and optimized index. However be cautious about reindexing big indexes as write locks are obtained on the parent table. One strategy to achieve the same result on a live site is to build an index concurrently on the same table and columns but with a different name, and then dropping the original index and renaming the new one. This procedure, while much longer, won’t require any long running locks on the live tables.