为何在Cockroach中添加OFFSET（甚至OFFSET 0）会大幅减慢查询速度？

问题场景 #

有如下SQL查询：

SELECT
  -- 省略列名
FROM foo
INNER JOIN bar
ON
  foo.id = bar.foo_id
ORDER BY
  foo.timestamp DESC
WHERE
  bar.baz_id = @SomeUuidHere
LIMIT 20

• 不带OFFSET时，查询耗时约1.8秒；
• 添加OFFSET 0后，耗时骤增至17秒；
• LIMIT 20 OFFSET 100耗时90-120秒，而仅用LIMIT 120时耗时仅50-60秒。

两张表的基础定义（不含索引）：

CREATE TABLE foo (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "timestamp" TIMESTAMPTZ NOT NULL,
  "type" INTEGER NOT NULL,
  external_reference UUID, -- 引用外部数据，无外键约束
  notes TEXT NOT NULL
);
CREATE TABLE bar (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  amount NUMERIC(19,4) NOT NULL,
  foo_id UUID NOT NULL REFERENCES foo(id),
  baz_id UUID NOT NULL REFERENCES baz(id), -- "baz"表未参与当前查询
  balance NUMERIC(19,4) NOT NULL
);

两张表均已创建适配该查询的覆盖索引，其中foo表的索引按timestamp DESC排序，与查询的排序逻辑匹配。

执行计划表现：

• LIMIT 20与LIMIT 20 OFFSET 0的EXPLAIN结果完全一致；
• LIMIT 120与LIMIT 20 OFFSET 100的EXPLAIN差异仅在于后者多一层offset为100的limit节点：

└── • limit
+    │ offset: 100
+    │
+    └── • limit
         │ count: 120

核心原因 #

1. Soft Limit优化被禁用
   不带OFFSET的LIMIT会触发CockroachDB的Soft Limit优化（从执行计划的FULL SCAN (SOFT LIMIT)可验证）。该优化允许数据库在扫描数据时，一旦收集到足够满足LIMIT的结果，就提前终止扫描，无需遍历全部符合条件的数据。

   但只要添加OFFSET（哪怕是OFFSET 0），数据库会判定需要先完成"跳过指定行数"的逻辑，直接禁用Soft Limit。此时数据库必须扫描并处理所有符合条件的数据，再应用OFFSET和LIMIT，这直接导致原本可以提前终止的扫描变成全量扫描，耗时剧增。

2. OFFSET的额外遍历开销
   对于LIMIT 20 OFFSET 100，数据库需要先找到前120条符合条件的结果，再丢弃前100条返回最后20条；而LIMIT 120只需找到前120条即可返回，无额外丢弃步骤。更关键的是，OFFSET禁用Soft Limit后，关联查询的JOIN操作会处理更多中间数据，开销随数据量增大呈非线性上升。

3. 执行计划的表象局限性
   EXPLAIN仅展示逻辑执行计划，无法体现Soft Limit这种运行时优化行为。带OFFSET和不带OFFSET的查询逻辑结构相似，但实际执行的终止条件完全不同——前者必须完成全量扫描，后者可提前终止。

优化建议 #

• 替换OFFSET为键集分页：如果是分页场景，改用键集分页（Keyset Pagination），利用上一页最后一条数据的timestamp和id作为下一页的查询条件，示例：

  SELECT ...
  FROM foo
  INNER JOIN bar ON foo.id = bar.foo_id
  WHERE bar.baz_id = @SomeUuidHere
    AND (foo.timestamp < @LastTimestampFromPrevPage 
         OR (foo.timestamp = @LastTimestampFromPrevPage AND foo.id < @LastIdFromPrevPage))
  ORDER BY foo.timestamp DESC, foo.id DESC
  LIMIT 20
  

  这种方式可以保留Soft Limit优化，同时避免OFFSET带来的全量扫描开销。

• 更新统计信息：若数据分布近期变化较大，手动执行ANALYZE foo; ANALYZE bar;更新统计信息，帮助优化器生成更精准的执行计划。

• 验证覆盖索引有效性：确认bar表的覆盖索引包含baz_id、foo_id及查询所需的其他列；foo表的覆盖索引包含timestamp DESC、id及查询所需的其他列，确保数据库无需回表即可获取全部数据。

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