PostgreSQL中OFFSET 0/1查询无响应，OFFSET≥2正常问题排查

使用PostgreSQL 13.7时遇到异常：设置OFFSET 0或1时，查询长时间无响应；但OFFSET改为2及以上时，查询可正常执行。

问题查询语句 #

SELECT
        m.id,
        l.uuid,
        m.displacement,
        m.total_displacement
FROM
        locations l
        INNER JOIN markers m ON l.id = m.location_id
WHERE
        l.uuid = 42901 AND l.type_name = 'ascending' 
ORDER BY m.id ASC 
LIMIT 100 OFFSET 1

不同OFFSET值的执行计划 #

OFFSET 0的执行计划 #

Limit  (cost=0.85..127003.79 rows=100 width=28)
  ->  Nested Loop  (cost=0.85..215537958.57 rows=169711 width=28)
        Join Filter: (l.id = m.location_id)
        ->  Index Scan using markers_pkey on markers m  (cost=0.57..209234879.46 rows=420204720 width=24)
        ->  Materialize  (cost=0.28..8.30 rows=1 width=16)
              ->  Index Scan using locations_uuid_type_name_key on locations l  (cost=0.28..8.30 rows=1 width=16)
"                    Index Cond: ((uuid = '4294967296001'::bigint) AND ((type_name)::text = 'ascending'::text))"

OFFSET 2的执行计划 #

Limit  (cost=127821.43..127821.68 rows=100 width=28)
  ->  Sort  (cost=127821.43..128245.70 rows=169711 width=28)
        Sort Key: m.id
        ->  Nested Loop  (cost=0.57..121310.95 rows=169711 width=28)
              ->  Seq Scan on locations l  (cost=0.00..124.14 rows=1 width=16)
"                    Filter: ((uuid = '4294967296001'::bigint) AND ((type_name)::text = 'ascending'::text))"
              ->  Index Scan using markers_location_id_idx on markers m  (cost=0.57..118770.45 rows=241636 width=24)
                    Index Cond: (location_id = l.id)

问题原因分析 #

从执行计划对比可以明确问题根源：

• OFFSET 0/1时的错误执行路径：优化器选择先扫描markers表的主键索引（需遍历4.2亿条数据），再逐个与locations的匹配结果做连接过滤。由于符合条件的markers记录在m.id排序后的位置极靠后，需要扫描海量数据才能凑够100条结果，直接导致查询超时。
• OFFSET≥2时的高效执行路径：优化器先找到符合条件的locations记录（仅1条），再通过markers_location_id_idx索引直接关联对应的markers数据，最后排序取结果。这种路径的成本极低，能快速完成查询。

出现这种差异的核心是PostgreSQL优化器对数据分布的估算偏差：当OFFSET为0或1时，优化器错误认为可以通过markers主键索引快速筛选出前100条符合条件的记录；而当OFFSET≥2时，优化器估算到需要跳过更多数据，转而选择了更合理的连接顺序。

解决建议 #

• 强制指定连接顺序：使用查询提示/*+ Leading(l m) */（PostgreSQL 12+支持）强制优化器先处理locations表，示例：

  SELECT /*+ Leading(l m) */
          m.id,
          l.uuid,
          m.displacement,
          m.total_displacement
  FROM
          locations l
          INNER JOIN markers m ON l.id = m.location_id
  WHERE
          l.uuid = 42901 AND l.type_name = 'ascending' 
  ORDER BY m.id ASC 
  LIMIT 100 OFFSET 1
  

• 更新统计信息：执行ANALYZE markers;和ANALYZE locations;，让优化器获得更准确的数据分布，帮助其选择更优的执行计划。
• 验证索引有效性：确认markers_location_id_idx和locations_uuid_type_name_key索引正常，无损坏或失效情况。

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