Django 2.2+PostgreSQL下过滤+排序查询的最优索引设计咨询

嘿，这个场景我处理过好多次了，咱们一步步捋清楚：

一、最优索引选择分析 #

你的查询逻辑是先按age范围过滤，再按name排序：

Person.objects.filter(age__gt=20, age__lt=30).order_by('name')

我们逐个拆解四个选项的适配性：

• 选项4（单独age索引）：能快速过滤出符合age条件的记录，但过滤后需要回表查询name字段，再对这些记录做排序，多了回表的IO开销，效率一般。
• 选项3（单独name索引）：只能加速排序步骤，但过滤age范围时需要全表扫描（或走其他低效索引），整体性能最差。
• 选项2（name在前、age在后的联合索引）：这个索引的排序逻辑是先按name再按age，完全匹配不上你「先过滤age」的查询逻辑，数据库需要遍历整个索引去匹配age条件，完全发挥不了索引的优势，直接pass。
• 选项1（age在前、name在后的联合索引）：这才是最优解！核心优势有两点：
  1. 利用age作为索引首字段，PostgreSQL可以快速定位到age>20且age<30的记录范围，大幅减少需要处理的数据量；
  2. 这是一个覆盖索引——索引里已经包含了name字段，不需要回表查询原表数据，直接在索引内就能完成后续的排序操作，节省了大量IO开销。

所以最优的Meta类定义应该是：

class Meta:
    indexes = [
        models.Index(fields=['age','name']),
    ]

二、能否避免排序操作？ #

很遗憾，按照你当前的查询逻辑，无法完全避免排序。原因在于：
age是范围查询（不是等值匹配），即使使用(age, name)的联合索引，索引内的记录顺序是「先按age升序，同age内按name升序」——这意味着不同age区间的name并不是全局有序的。数据库必须把所有符合age条件的记录收集起来，再做一次全局排序才能得到按name有序的结果。

不过也不用灰心，使用(age, name)的覆盖索引已经把排序的成本降到最低了——因为排序的数据源是体积更小的索引数据，而不是整个表的行数据，性能提升还是很明显的。

内容的提问来源于stack exchange，提问作者GCru