Django全文搜索优化 - Postgres

Question

我将尝试为地址自动完成功能创建全文搜索，利用Django (v2.1) 和Postgres (9.5)，但目前性能不适合自动完成，并且我不理解性能结果背后的逻辑。该表格相当庞大，有1400万行。

我的模型:

from django.db import models
from postgres_copy import CopyManager
from django.contrib.postgres.indexes import GinIndex

class Addresses(models.Model):
date_update = models.DateTimeField(auto_now=True, null=True)
longitude = models.DecimalField(max_digits=9, decimal_places=6 , null=True)
latitude = models.DecimalField(max_digits=9, decimal_places=6 , null=True)
number = models.CharField(max_length=16, null=True, default='')
street = models.CharField(max_length=60, null=True, default='')
unit = models.CharField(max_length=50, null=True, default='')
city = models.CharField(max_length=50, null=True, default='')
district = models.CharField(max_length=10, null=True, default='')
region = models.CharField(max_length=5, null=True, default='')
postcode = models.CharField(max_length=5, null=True, default='')
addr_id = models.CharField(max_length=20, unique=True)
addr_hash = models.CharField(max_length=20, unique=True)
objects = CopyManager()

class Meta:
    indexes = [
        GinIndex(fields=['number', 'street', 'unit', 'city', 'region', 'postcode'], name='search_idx')
    ]

我创建了一个小测试，以搜索词汇量为基础来检查性能：

    search_vector = SearchVector('number', 'street', 'unit', 'city', 'region', 'postcode')

    searchtext1 = "north"
    searchtext2 = "north bondi"
    searchtext3 = "north bondi blair"
    searchtext4 = "north bondi blair street 2026"

    print('Test1: 1 word')
    start_time = time.time()
    result = AddressesAustralia.objects.annotate(search=search_vector).filter(search=searchtext1)[:10]
    #print(len(result))
    time_exec = str(timedelta(seconds=time.time() - start_time))
    print(time_exec)
    print(' ')

    #print(AddressesAustralia.objects.annotate(search=search_vector).explain(verbose=True))

    print('Test2: 2 words')
    start_time = time.time()
    result = AddressesAustralia.objects.annotate(search=search_vector).filter(search=searchtext2)[:10]
    #print(len(result))
    time_exec = str(timedelta(seconds=time.time() - start_time))
    print(time_exec)
    print(' ')

    print('Test3: 3 words')
    start_time = time.time()
    result = AddressesAustralia.objects.annotate(search=search_vector).filter(search=searchtext3)[:10]
    #print(len(result))
    time_exec = str(timedelta(seconds=time.time() - start_time))
    print(time_exec)
    print(' ')

    print('Test4: 5 words')
    start_time = time.time()
    result = AddressesAustralia.objects.annotate(search=search_vector).filter(search=searchtext4)[:10]
    #print(len(result))
    time_exec = str(timedelta(seconds=time.time() - start_time))
    print(time_exec)
    print(' ')

我得到了下面的结果，看起来相当正确：

Test1: 1 word
0:00:00.001841

Test2: 2 words
0:00:00.001422

Test3: 3 words
0:00:00.001574

Test4: 5 words
0:00:00.001360

然而，如果我取消注释print(len(results))这行代码，我会得到以下结果：

Test1: 1 word
10
0:00:00.046392

Test2: 2 words
10
0:00:06.544732

Test3: 3 words
10
0:01:12.367157

Test4: 5 words
10
0:01:17.786596

这显然不适合自动完成功能。

有人能解释一下，在对查询集结果执行操作时为什么执行时间会变长吗？似乎数据库检索总是很快的，但是遍历结果需要时间，这对我来说没有意义，因为我将结果限制为10个，返回的查询集始终是相同大小。

此外，尽管我已经创建了一个GIN索引，但这个索引似乎并没有被使用。看起来它已经被正确创建：

=# \d public_data_au_addresses
                                   Table 
"public.public_data_au_addresses"
Column    |           Type           | Collation | Nullable |                            
Default                            
-------------+--------------------------+-----------+----------+------ 
---------------------------------------------------------
id          | integer                  |           | not null | 
nextval('public_data_au_addresses_id_seq'::regclass)
date_update | timestamp with time zone |           |          | 
longitude   | numeric(9,6)             |           |          | 
latitude    | numeric(9,6)             |           |          | 
number      | character varying(16)    |           |          | 
street      | character varying(60)    |           |          | 
unit        | character varying(50)    |           |          | 
city        | character varying(50)    |           |          | 
district    | character varying(10)    |           |          | 
region      | character varying(5)     |           |          | 
postcode    | character varying(5)     |           |          | 
addr_id     | character varying(20)    |           | not null | 
addr_hash   | character varying(20)    |           | not null | 
Indexes:
"public_data_au_addresses_pkey" PRIMARY KEY, btree (id)
"public_data_au_addresses_addr_hash_key" UNIQUE CONSTRAINT, btree (addr_hash)
"public_data_au_addresses_addr_id_key" UNIQUE CONSTRAINT, btree (addr_id)
"public_data_au_addresses_addr_hash_e8c67a89_like" btree (addr_hash varchar_pattern_ops)
"public_data_au_addresses_addr_id_9ee00c76_like" btree (addr_id varchar_pattern_ops)
"search_idx" gin (number, street, unit, city, region, postcode)

当我在我的查询上运行explain()方法时，我得到以下结果：

Test1: 1 word
Limit  (cost=0.00..1110.60 rows=10 width=140)
->  Seq Scan on public_data_au_addresses  (cost=0.00..8081472.41 rows=72767 width=140)
    Filter: (to_tsvector((((((((((((COALESCE(number, ''::character varying))::text || ' '::text) || (COALESCE(street, ''::character varying))::text) || ' '::text) || (COALESCE(unit, ''::character varying))::text) || ' '::text) || (COALESCE(city, ''::character varying))::text) || ' '::text) || (COALESCE(region, ''::character varying))::text) || ' '::text) || (COALESCE(postcode, ''::character varying))::text)) @@ plainto_tsquery('north'::text))

所以它仍然显示顺序扫描而不是使用索引扫描。有人知道如何修复或调试吗？

即使有这么多字段要搜索，GIN索引仍然有效吗？

最后，有人知道我如何改进代码以进一步提高性能吗？

谢谢！问候

更新

我已经尝试按照Paolo的建议创建搜索向量，但似乎搜索仍然是顺序的，没有利用GIN索引。

class AddressesQuerySet(CopyQuerySet):

    def update_search_vector(self):
        return self.update(search_vector=SearchVector('number', 'street', 'unit', 'city', 'region', 'postcode', config='english'))


class AddressesAustralia(models.Model):
    date_update = models.DateTimeField(auto_now=True, null=True)
    longitude = models.DecimalField(max_digits=9, decimal_places=6 , null=True)
    latitude = models.DecimalField(max_digits=9, decimal_places=6 , null=True)
    number = models.CharField(max_length=16, null=True, default='')
    street = models.CharField(max_length=60, null=True, default='')
    unit = models.CharField(max_length=50, null=True, default='')
    city = models.CharField(max_length=50, null=True, default='')
    district = models.CharField(max_length=10, null=True, default='')
    region = models.CharField(max_length=5, null=True, default='')
    postcode = models.CharField(max_length=5, null=True, default='')
    addr_id = models.CharField(max_length=20, unique=True)
    addr_hash = models.CharField(max_length=20, unique=True)
    search_vector = SearchVectorField(null=True, editable=False)

    objects = AddressesQuerySet.as_manager()

    class Meta:
        indexes = [
            GinIndex(fields=['search_vector'], name='search_vector_idx')
        ]

我使用更新命令更新了search_vector字段：

AddressesAustralia.objects.update_search_vector()

然后我运行了一个查询，使用相同的搜索向量进行测试：

class Command(BaseCommand):

    def handle(self, *args, **options):

        search_vector = SearchVector('number', 'street', 'unit', 'city', 'region', 'postcode', config='english')

        searchtext1 = "north"

        print('Test1: 1 word')
        start_time = time.time()
        result = AddressesAustralia.objects.filter(search_vector=searchtext1)[:10].explain(verbose=True)
        print(len(result))
        print(result)
        time_exec = str(timedelta(seconds=time.time() - start_time))
        print(time_exec)

我得到了以下结果，仍然显示顺序搜索：

Test1: 1 word
532
Limit  (cost=0.00..120.89 rows=10 width=235)
  Output: id, date_update, longitude, latitude, number, street, unit, city, district, region, postcode, addr_id, addr_hash, search_vector
  ->  Seq Scan on public.public_data_au_addressesaustralia  (cost=0.00..5061078.91 rows=418651 width=235)
        Output: id, date_update, longitude, latitude, number, street, unit, city, district, region, postcode, addr_id, addr_hash, search_vector
        Filter: (public_data_au_addressesaustralia.search_vector @@ plainto_tsquery('north'::text))
0:00:00.075262

我还尝试过：

• 在搜索向量中使用和不使用config="english"（在更新和查询中都尝试过）

• 删除GIN索引，然后重新创建它并重新运行update_search_Vector

但结果仍然相同。你有任何关于我做错了什么或如何进一步排除故障的想法吗？

- shitzuu

---

AddressesAustralia 模型的定义是什么？您展示了 Addresses 模型的定义。 - Red Cricket

Querysets是惰性的，你的第一个测试并没有测量任何数据库操作。无论如何，请查看全文搜索文档的性能部分。 - knbk

@RedCricket Addresses和AddressesAustralia是同一个模型，只是在发布时打错了字，抱歉。 - shitzuu

@knbk 感谢您指出Querysets是惰性加载的，这解释了为什么它在我的第一次测试中实际上没有执行任何数据库操作。关于性能，我之前确实查看了您提供的链接，但它说“如果您查询的所有字段都包含在一个特定的模型中，您可以创建与您希望使用的搜索向量匹配的函数索引”，这就是我用GIN索引做的事情。我的理解是，SearchVectorField在跨多个模型进行搜索时最有用，而这对我来说并不是那样。 - shitzuu

@shitzuu，正如我在答案中所解释的那样，SearchVectorField 对于提高搜索性能非常有用，不仅在跨多个模型搜索时使用。你试过我的代码了吗？它对你有效吗？ - Paolo Melchiorre

Answer 1

正如@knbk已经建议的那样，为了提高性能，您需要阅读Django文档中的全文搜索性能部分。

"如果这种方法变得太慢，您可以在模型中添加一个SearchVectorField。"

在您的代码中，您可以在模型中添加一个搜索向量字段，带有相关的GIN索引和一个新的查询集方法来更新该字段：

from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVector, SearchVectorField
from django.db import models
from postgres_copy import CopyQuerySet


class AddressesQuerySet(CopyQuerySet):

    def update_search_vector(self):
        return self.update(search_vector=SearchVector(
            'number', 'street', 'unit', 'city', 'region', 'postcode'
        ))


class Addresses(models.Model):
    date_update = models.DateTimeField(auto_now=True, null=True)
    longitude = models.DecimalField(max_digits=9, decimal_places=6, null=True)
    latitude = models.DecimalField(max_digits=9, decimal_places=6, null=True)
    number = models.CharField(max_length=16, null=True, default='')
    street = models.CharField(max_length=60, null=True, default='')
    unit = models.CharField(max_length=50, null=True, default='')
    city = models.CharField(max_length=50, null=True, default='')
    district = models.CharField(max_length=10, null=True, default='')
    region = models.CharField(max_length=5, null=True, default='')
    postcode = models.CharField(max_length=5, null=True, default='')
    addr_id = models.CharField(max_length=20, unique=True)
    addr_hash = models.CharField(max_length=20, unique=True)
    search_vector = SearchVectorField(null=True, editable=False)

    objects = AddressesQuerySet.as_manager()

    class Meta:
        indexes = [
            GinIndex(fields=['search_vector'], name='search_vector_idx')
        ]

你可以使用新的queryset方法更新你的新搜索向量字段：

>>> Addresses.objects.update_search_vector()
UPDATE "addresses_addresses"
SET "search_vector" = to_tsvector(
  COALESCE("addresses_addresses"."number", '') || ' ' ||
  COALESCE("addresses_addresses"."street", '') || ' ' ||
  COALESCE("addresses_addresses"."unit", '') || ' ' ||
  COALESCE("addresses_addresses"."city", '') || ' ' ||
  COALESCE("addresses_addresses"."region", '') || ' ' ||
  COALESCE("addresses_addresses"."postcode", '')
)

如果您执行一个查询并读取解释，您可以看到使用了GIN索引：

>>> print(Addresses.objects.filter(search_vector='north').values('id').explain(verbose=True))
EXPLAIN (VERBOSE true)
SELECT "addresses_addresses"."id"
FROM "addresses_addresses"
WHERE "addresses_addresses"."search_vector" @@ (plainto_tsquery('north')) = true [0.80ms]
Bitmap Heap Scan on public.addresses_addresses  (cost=12.25..16.52 rows=1 width=4)
  Output: id
  Recheck Cond: (addresses_addresses.search_vector @@ plainto_tsquery('north'::text))
  ->  Bitmap Index Scan on search_vector_idx  (cost=0.00..12.25 rows=1 width=0)
        Index Cond: (addresses_addresses.search_vector @@ plainto_tsquery('north'::text))

如果您想深入了解，可以阅读我撰写的有关该主题的文章：

"Django中使用PostgreSQL进行全文搜索"

更新

我尝试执行由Django ORM生成的SQL语句：
http://sqlfiddle.com/#!17/f9aa9/1

- Paolo Melchiorre

---

感谢您的帮助！对于回复时间我表示歉意，我的数据库非常大，更新需要几个小时，所以在更新这个线程之前我尝试了几次。不幸的是，我仍然使用顺序搜索，GIN索引似乎无法工作。我已经在上面更新了我的帖子，包括更新的代码和结果。如果您有进一步的解决方法，请告诉我！再次感谢。 - shitzuu

我在我的回答中提到，我还在SQL Fiddle中尝试了一次代码。你有一个样本CSV文件可以导入并使用大量行测试相同的SQL代码吗？ - Paolo Melchiorre

现在它实际上正在工作！看起来问题是我用搜索词“north”（就像你一样）进行测试，但是只要我输入另一个单词，它就可以工作了！如果我把“north”放回去，那么它就会恢复到顺序...不确定为什么，可能是在我没有正确实现search_vector时，它会重用某种缓存。无论如何，现在它可以工作了，非常感谢您的帮助！！ - shitzuu

不用谢。我很高兴它对你也起作用了。Django和Postgres的全文搜索非常强大。享受使用它并请分享你的经验。 - Paolo Melchiorre

使用默认搜索似乎效果很好，它只返回使用整个单词的结果（使用plainto_tsquery）。我尝试修改我的搜索，改用to_tsquery来允许不完整的搜索tsquery = " & ".join(searchtext.split()) + ":*"和AddressesAustralia.objects.extra(where=["search_vector @@ (to_tsquery(%s)) = true"], params=[tsquery])，但不幸的是，虽然它工作得很好，但在我的拥有1400万行数据的数据库上完成搜索需要3倍的时间（1.5秒 vs 0.5秒）！不确定是否还有任何地方可以改进！。 - shitzuu

我认为最好的做法是您可以添加另一个问题，包含您的所有细节和与新查询相关的测试代码，这样每个人都可以回答并帮助您。 - Paolo Melchiorre

Answer 2

你需要在搜索向量上创建一个功能性索引。目前，你已经在基础字段上创建了一个索引，但它仍然必须为每一行创建搜索向量，才能过滤结果。这就是为什么它正在执行顺序扫描。

Django目前不支持在Meta.indexes中使用功能性索引，因此你需要手动创建它，例如使用RunSQL操作。

RunSQL(
    """
    CREATE INDEX ON public_data_au_addresses USING GIN 
    (to_tsvector(...))
    """
)

to_tsvector()表达式必须与您查询中使用的表达式匹配。请务必阅读Postgres文档以获取所有详细信息。

- knbk