基于Prisma的PostgreSQL分页查询优化：索引与配置问题

我正在优化一个基于Prisma ORM的项目，其中Video表与Channel表关联的分页查询在136k条数据的Video表上执行耗时4-10秒，即使添加了部分索引后性能仍未达标。我缺乏数据库索引优化经验，想知道是否遗漏了关键优化点，或是受限于服务器硬件。

提取的Prisma生成SQL查询 #

SELECT
    "public"."Video"."id",
    "public"."Video"."youtubeId",
    "public"."Video"."channelId",
    "public"."Video"."type",
    "public"."Video"."status",
    "public"."Video"."reviewed",
    "public"."Video"."category",
    "public"."Video"."youtubeTags",
    "public"."Video"."language",
    "public"."Video"."title",
    "public"."Video"."description",
    "public"."Video"."duration",
    "public"."Video"."durationSeconds",
    "public"."Video"."viewCount",
    "public"."Video"."likeCount",
    "public"."Video"."commentCount",
    "public"."Video"."scheduledStartTime",
    "public"."Video"."actualStartTime",
    "public"."Video"."actualEndTime",
    "public"."Video"."sortTime",
    "public"."Video"."createdAt",
    "public"."Video"."updatedAt",
    "public"."Video"."publishedAt"
FROM
    "public"."Video",
    (
        SELECT
            "public"."Video"."sortTime" AS "Video_sortTime_0"
        FROM
            "public"."Video"
        WHERE ("public"."Video"."id") = (29949)
    ) AS "order_cmp"
WHERE (
    ("public"."Video"."id") IN(
        SELECT
            "t0"."id" FROM "public"."Video" AS "t0"
            INNER JOIN "public"."Channel" AS "j0" ON ("j0"."id") = ("t0"."channelId")
        WHERE (
            (NOT "j0"."status" IN('HIDDEN', 'ARCHIVED'))
            AND "t0"."id" IS NOT NULL)
    )
    AND "public"."Video"."status" IN('UPCOMING', 'LIVE', 'PUBLISHED')
    AND "public"."Video"."sortTime" <= "order_cmp"."Video_sortTime_0")
ORDER BY
    "public"."Video"."sortTime" DESC OFFSET 0;

当前数据库索引配置 #

CREATE UNIQUE INDEX "Video_youtubeId_key" ON public."Video" USING btree ("youtubeId");
CREATE INDEX "Video_status_idx" ON public."Video" USING btree (status);
CREATE INDEX "Video_sortTime_idx" ON public."Video" USING btree ("sortTime" DESC);
CREATE UNIQUE INDEX "Video_pkey" ON public."Video" USING btree (id);
CREATE INDEX "Video_channelId_idx" ON public."Video" USING btree ("channelId");

CREATE UNIQUE INDEX "Channel_youtubeId_key" ON public."Channel" USING btree ("youtubeId");
CREATE UNIQUE INDEX "Channel_pkey" ON public."Channel" USING btree (id);

查询执行计划（EXPLAIN ANALYZE） #

Sort  (cost=114760.67..114867.67 rows=42801 width=1071) (actual time=4115.144..4170.368 rows=13943 loops=1)
  Sort Key: "Video"."sortTime" DESC
  Sort Method: external merge  Disk: 12552kB
  Buffers: shared hit=19049 read=54334 dirtied=168, temp read=1569 written=1573
  I/O Timings: read=11229.719
  ->  Nested Loop  (cost=39030.38..91423.62 rows=42801 width=1071) (actual time=2720.873..4037.549 rows=13943 loops=1)
        Join Filter: ("Video"."sortTime" <= "Video_1"."sortTime")
        Rows Removed by Join Filter: 115529
        Buffers: shared hit=19049 read=54334 dirtied=168
        I/O Timings: read=11229.719
        ->  Index Scan using "Video_pkey" on "Video" "Video_1"  (cost=0.42..8.44 rows=1 width=8) (actual time=0.852..1.642 rows=1 loops=1)
              Index Cond: (id = 29949)
              Buffers: shared hit=2 read=2
              I/O Timings: read=0.809
        ->  Gather  (cost=39029.96..89810.14 rows=128404 width=1071) (actual time=2719.274..4003.170 rows=129472 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=19047 read=54332 dirtied=168
              I/O Timings: read=11228.910
              ->  Parallel Hash Semi Join  (cost=38029.96..75969.74 rows=53502 width=1071) (actual time=2695.849..3959.412 rows=43157 loops=3)
                    Hash Cond: ("Video".id = t0.id)
                    Buffers: shared hit=19047 read=54332 dirtied=168
                    I/O Timings: read=11228.910
                    ->  Parallel Seq Scan on "Video"  (cost=0.00..37202.99 rows=53938 width=1071) (actual time=0.929..1236.450 rows=43157 loops=3)
                          Filter: (status = ANY ('{UPCOMING,LIVE,PUBLISHED}'::"VideoStatus"[]))
                          Rows Removed by Filter: 3160
                          Buffers: shared hit=9289 read=27118
                          I/O Timings: read=3526.407
                    ->  Parallel Hash  (cost=37312.18..37312.18 rows=57422 width=4) (actual time=2692.172..2692.180 rows=46084 loops=3)
                          Buckets: 262144  Batches: 1  Memory Usage: 7520kB
                          Buffers: shared hit=9664 read=27214 dirtied=168
                          I/O Timings: read=7702.502
                          ->  Hash Join  (cost=173.45..37312.18 rows=57422 width=4) (actual time=3.485..2666.998 rows=46084 loops=3)
                                Hash Cond: (t0."channelId" = j0.id)
                                Buffers: shared hit=9664 read=27214 dirtied=168
                                I/O Timings: read=7702.502
                                ->  Parallel Seq Scan on "Video" t0  (cost=0.00..36985.90 rows=57890 width=8) (actual time=1.774..2646.207 rows=46318 loops=3)
                                      Filter: (id IS NOT NULL)
                                      Buffers: shared hit=9193 read=27214 dirtied=168
                                      I/O Timings: read=7702.502
                                ->  Hash  (cost=164.26..164.26 rows=735 width=4) (actual time=1.132..1.136 rows=735 loops=3)
                                      Buckets: 1024  Batches: 1  Memory Usage: 34kB
                                      Buffers: shared hit=471
                                      ->  Seq Scan on "Channel" j0  (cost=0.00..164.26 rows=735 width=4) (actual time=0.024..0.890 rows=735 loops=3)
                                            Filter: (status <> ALL ('{HIDDEN,ARCHIVED}'::"ChannelStatus"[]))
                                            Rows Removed by Filter: 6
                                            Buffers: shared hit=471
Planning Time: 8.134 ms
Execution Time: 4173.202 ms

执行计划显示排序操作使用了磁盘（需约12560kB内存），当前服务器是1G内存的Lightsail Postgres实例，work_mem设置为4M，不确定调整到16M或24M是否会超出内存负载。另外，即使移除Channel表关联，查询仍耗时7-8秒。

实际Prisma查询代码 #

const videos = await ctx.prisma.video.findMany({
  where: {
    channel: {
      NOT: {
        status: {
          in: [ChannelStatus.HIDDEN, ChannelStatus.ARCHIVED],
        },
      },
    },
    status: {
      in: [VideoStatus.UPCOMING, VideoStatus.LIVE, VideoStatus.PUBLISHED],
    },
  },
  include: {
    channel: {
      include: {
        links: true,
      },
    },
  },
  cursor: _args.cursor
    ? {
        id: _args.cursor,
      }
    : undefined,
  skip: _args.cursor ? 1 : 0,
  orderBy: {
    sortTime: 'desc',
  },
  take: Math.min(_args.limit, config.GRAPHQL_MAX_RECENT_VIDEOS),
});

优化方案建议 #

1. 调整work_mem参数 #

当前排序需要约12.5MB内存，而work_mem仅4M导致磁盘排序，这是耗时的主要原因之一。1G内存的服务器可以尝试临时调整work_mem到16M测试：

SET work_mem = '16MB';

如果测试后性能提升且内存占用稳定，可以修改postgresql.conf永久生效。work_mem是单个操作的内存分配，1G内存设置16M是安全的，实际并发场景下不会超出负载。

2. 添加复合索引，避免全表扫描 #

从执行计划看，Video表存在两次全表扫描，需针对查询条件创建复合索引：

• 针对Video的status和sortTime的复合索引：

CREATE INDEX "Video_status_sortTime_idx" ON public."Video" USING btree (status, "sortTime" DESC);

该索引可直接过滤符合status条件的记录，同时按sortTime排序，避免后续磁盘排序。

• 针对Video的channelId和id的索引：

CREATE INDEX "Video_channelId_id_idx" ON public."Video" USING btree ("channelId", id);

• 给Channel表的status添加索引（虽然数据量小，但可加速过滤）：

CREATE INDEX "Channel_status_idx" ON public."Channel" USING btree (status);

3. 优化Prisma查询逻辑，避免低效子查询 #

当前Prisma生成的SQL使用嵌套子查询和IN子句，效率低下。建议调整cursor分页逻辑，改用基于sortTime+id的复合cursor（避免sortTime重复导致的分页问题）：

const videos = await ctx.prisma.video.findMany({
  where: {
    channel: {
      status: {
        notIn: [ChannelStatus.HIDDEN, ChannelStatus.ARCHIVED],
      },
    },
    status: {
      in: [VideoStatus.UPCOMING, VideoStatus.LIVE, VideoStatus.PUBLISHED],
    },
    ...(_args.cursor ? {
      OR: [
        { sortTime: { lt: _args.cursor.sortTime } },
        { sortTime: _args.cursor.sortTime, id: { lt: _args.cursor.id } }
      ]
    } : {})
  },
  include: {
    channel: {
      include: {
        links: true,
      },
    },
  },
  orderBy: [
    { sortTime: 'desc' },
    { id: 'desc' }
  ],
  take: Math.min(_args.limit, config.GRAPHQL_MAX_RECENT_VIDEOS),
});

这种写法会让Prisma生成基于sortTime范围的高效查询，避免子查询获取order_cmp，减少数据扫描量。

4. 检查服务器硬件瓶颈 #

执行计划中I/O耗时超过11秒，说明磁盘IO是核心瓶颈。1G内存的Lightsail实例若使用普通磁盘，可考虑升级到SSD实例，或增加内存到2G，让更多数据缓存到内存中，减少磁盘读取次数。

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