sql - Django 慢查询性能提升

Question

我将 Django 与 Postgres 结合使用，并有下表:

class Person(models.Model):
    person_id=models.IntegerField(primary_key=True)
    name = models.CharField(max_length=500, blank=True)
    description = models.ManyToManyField('descriptions.Description', through='DescriptionPersonUser')

class DescriptionPersonUser(models.Model):
    person = models.ForeignKey(Person)
    description = models.ForeignKey('descriptions.Description')
    user = models.ForeignKey(User)

    class Meta:
        managed = True
        unique_together = ('person', 'description', 'user')

class Description(models.Model):
    description_id=models.AutoField (primary_key=True)
    description_word=models.CharField(max_length=50, blank=True, unique=True)

class AuthUser(models.Model):
    id = models.IntegerField(primary_key=True)  # AutoField?
   ...
    username = models.CharField(unique=True, max_length=30)

Person 表有超过 1.5 mio 行，其他表各不超过 100 行。据我了解，执行“正常”执行的查询仍然是合理的。我想通过从 DescriptionPersonUser 表中注释计数来对 Person 表进行排序。

person_list = Person.objects.annotate(count=Count('descriptionpersonuser')).order_by('-count')[:10]

此查询加载耗时约 50000 毫秒。比我尝试在原始 sql 中执行它并改进了很多到 cca 1900 ms。

person_list= Person.objects.raw('SELECT "person"."person_id", COUNT("persons_descriptionpersonuser"."id") AS "count" FROM "person" LEFT OUTER JOIN "persons_descriptionpersonuser" ON ( "person"."person_id" = "persons_descriptionspersonuser"."person_id" ) GROUP BY "person"."person_id" ORDER BY "count" DESC, "person"."person_id" ASC LIMIT 10'),

我还在 persons_descriptionpersonuser 上创建了索引:

CREATE INDEX index_descriptionpersonuser ON persons_descriptionpersonuser (person_id, description_id, id);

所以我的问题是:

1. 还有提高查询速度的余地吗？或者 1900 毫秒用于 1+ mio 行查询是否合适？
2. 由于我没有发现创建的索引在查询速度上有任何差异，我如何检查索引是否正常工作或是否影响查询？

已编辑(根据 Tomasz Jakub Rup 添加 EXPLAIN ANALYZE 结果的建议):

没有 index_descriptionpersonuser:

Limit  (cost=138185.30..138185.33 rows=10 width=8) (actual time=2470.974..2470.976 rows=10 loops=1)
   ->  Sort  (cost=138185.30..142177.82 rows=1597006 width=8) (actual time=2470.973..2470.975 rows=10 loops=1)
         Sort Key: (count(persons_descriptionpersonuser.id)), person.person_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  GroupAggregate  (cost=0.56..103674.58 rows=1597006 width=8) (actual time=0.402..1945.107 rows=1597006 loops=1)
               Group Key: person.person_id
               ->  Merge Left Join  (cost=0.56..79719.49 rows=1597006 width=8) (actual time=0.378..1014.179 rows=1597016 loops=1)
                     Merge Cond: (person.person_id = persons_descriptionpersonuse.person_id)
                     ->  Index Only Scan using person_pkey on person  (cost=0.43..75718.86 rows=1597006 width=4) (actual time=0.359..610.272 rows=1597006 loops=1)
                           Heap Fetches: 235898
                     ->  Index Scan using persons_descriptionpersonuse_person_id on persons_descriptionpersonuser  (cost=0.14..12.42 rows=19 width=8) (actual time=0.014..0.025 rows=20 loops=1)
 Planning time: 17.879 ms
 Execution time: 2472.821 ms
(13 rows) 

使用 index_descriptionpersonuser:

Limit  (cost=138185.55..138185.58 rows=10 width=8) (actual time=2341.349..2341.352 rows=10 loops=1)
   ->  Sort  (cost=138185.55..142178.07 rows=1597006 width=8) (actual time=2341.325..2341.325 rows=10 loops=1)
         Sort Key: (count(persons_descriptionpersonuser.id)), person.person_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  GroupAggregate  (cost=0.56..103674.83 rows=1597006 width=8) (actual time=0.106..1819.330 rows=1597006 loops=1)
               Group Key: person.person_id
               ->  Merge Left Join  (cost=0.56..79719.74 rows=1597006 width=8) (actual time=0.092..877.874 rows=1597016 loops=1)
                     Merge Cond: (person.person_id = persons_descriptionpersonuser.person_id)
                     ->  Index Only Scan using person_pkey on person  (cost=0.43..75718.86 rows=1597006 width=4) (actual time=0.023..473.046 rows=1597006 loops=1)
                           Heap Fetches: 235898
                     ->  Index Only Scan using index_descriptionpersonuser on persons_descriptionpersonuser  (cost=0.14..12.44 rows=20 width=8) (actual time=0.059..0.085 rows=20 loops=1)
                           Heap Fetches: 20
 Planning time: 0.715 ms
 Execution time: 2343.815 ms
(14 rows)

作为Tomasz Jakub Rup建议优化的 sql 查询现在需要 cca 40 毫秒。以下是结果:

Limit  (cost=1.50..1.52 rows=8 width=8) (actual time=0.061..0.064 rows=10 loops=1)
   ->  Sort  (cost=1.50..1.52 rows=8 width=8) (actual time=0.060..0.061 rows=10 loops=1)
         Sort Key: (count(id)), person_id
         Sort Method: quicksort  Memory: 25kB
         ->  HashAggregate  (cost=1.30..1.38 rows=8 width=8) (actual time=0.039..0.044 rows=10 loops=1)
               Group Key: person_id
               ->  Seq Scan on persons_descriptionpersonuser  (cost=0.00..1.20 rows=20 width=8) (actual time=0.011..0.018 rows=20 loops=1)
 Planning time: 0.175 ms
 Execution time: 0.129 ms
(9 rows)

谢谢

Answer 1

第二个问题的答案:

查看结果

EXPLAIN ANALYZE SELECT "person"."person_...

查询。如果您在结果中找到 index_descriptionpersonuser 是，则您的查询使用索引。如果不是，请尝试创建其他索引。也许仅在 person_id 上？

第一个问题:是的，这个查询可以更快。显示 EXPLAIN ANALYZE... 的结果，然后我们尝试加快查询速度。

注意

原始查询可能更快，因为它们从 PostgreSQL 缓存中获取数据。