我在PostgreSQL 15.3中有这个表（对应于Django模型）：

Table "public.myapp1_task"
         Column          |           Type           | Collation | Nullable |                     Default
-------------------------+--------------------------+-----------+----------+-------------------------------------------------
 id                      | bigint                   |           | not null | nextval('myapp1_task_id_seq'::regclass)
 created_at              | timestamp with time zone |           | not null |
 updated_at              | timestamp with time zone |           | not null |
 kind                    | character varying(12)    |           | not null |
 status                  | character varying(12)    |           | not null |
 environment             | character varying(7)     |           | not null |
 data                    | jsonb                    |           | not null |
 result                  | jsonb                    |           | not null |
 sent_at                 | timestamp with time zone |           |          |
 response_at             | timestamp with time zone |           |          |
 priority                | smallint                 |           | not null |
 sequence                | integer                  |           |          |
 result_attachment       | character varying(100)   |           | not null |
 taxes                   | jsonb                    |           | not null |
 myapp2_item_id          | bigint                   |           |          |
 source                  | character varying(8)     |           | not null |
 user_id                 | bigint                   |           |          |
 custom_actions          | jsonb                    |           | not null |
 
Indexes:
    "myapp1_task_pkey" PRIMARY KEY, btree (id)
    "myapp1_task_user_id_76a104e9" btree (user_id)
    "myapp1_task_myapp2_item_idd_441d91cb" btree (myapp2_item_id)
    "sequence_idx" btree (sequence DESC NULLS LAST)
    "sequence_mc_idx" btree (sequence, myapp2_item_id DESC NULLS LAST)

字符串

Goals：对于每个myapp2_item_id，查找序列最高的行。

我添加了与sequence列相关的最后两个索引。
使用Django ORM，我试图过滤一个查询集，代码如下：

queryset = Task.objects.all()
sequences = queryset.filter(item=OuterRef("item")).exclude(sequence__isnull=True).order_by("-sequence").distinct().values("sequence")
max_sequences = sequences.annotate(max_seq=Max("sequence")).values("max_seq")[:1]
filtered_queryset = queryset.filter(sequence=Subquery(max_sequences))
print(filtered_queryset.query)

型
它会将其转换成这个SQL语句。请注意具有group by和max聚合的子查询：

SELECT "myapp1_task"."id"
FROM "myapp1_task"
         LEFT OUTER JOIN "myapp2_item"
                         ON ("myapp1_task"."myapp2_item_id" = "myapp2_item"."id")
         LEFT OUTER JOIN "myapp2_user" ON ("myapp2_item"."user_id" = "myapp2_user"."id")
         LEFT OUTER JOIN "myapp2_category"
                         ON ("myapp2_item"."myapp2_category_id" = "myapp2_category"."id")
         LEFT OUTER JOIN "myapp2_user" T5 ON ("myapp1_task"."user_id" = T5."id")
WHERE "myapp1_task"."sequence" = (SELECT "subquery"."max_seq"
                                          FROM (
                                          SELECT MAX(U0."sequence") AS "max_seq", U0."sequence"
                                                FROM "myapp1_task" U0
                                                WHERE (U0."myapp2_item_id" =
                                                       ("myapp1_task"."myapp2_item_id"))
                                                GROUP BY U0."sequence"
                                                ORDER BY U0."sequence" DESC
                                                LIMIT 1) subquery)

型
可悲的是，它在相当大的表（>1M行）上非常慢。检查explain结果，我在子查询上得到了这个-> seq scan，所以没有使用任何新索引：

Seq Scan on myapp1_task  (cost=0.00..5525.25 rows=3 width=8)
  Filter: (sequence = (SubPlan 1))
  SubPlan 1
    ->  Subquery Scan on subquery  (cost=8.30..8.33 rows=1 width=4)
          ->  Limit  (cost=8.30..8.32 rows=1 width=8)
                ->  GroupAggregate  (cost=8.30..8.32 rows=1 width=8)
                      Group Key: u0.sequence
                      ->  Sort  (cost=8.30..8.31 rows=1 width=4)
                            Sort Key: u0.sequence DESC
                            ->  Index Scan using myapp1_task_myapp2_item_idd_441d91cb on myapp1_task u0  (cost=0.28..8.29 rows=1 width=4)
                                  Index Cond: (myapp2_item_id = myapp1_task.myapp2_item_id)

型
不知道我做错了什么。如何改善这种情况？