PL/pgSQL遍历超大SELECT结果集的高效方法及性能疑问

核心疑问：游标会预加载全量数据吗？ #

PL/pgSQL里的FOR ... IN SELECT循环用的是隐式游标，默认是按需取数，不会一次性把1亿行结果都加载到内存里：

• 启动循环时，数据库只会先做好查询规划，不会立刻计算并返回所有结果。
• 每次循环迭代，游标会从数据库获取一小批数据（默认批次大小由数据库参数控制，通常是几百行），直到你调用EXIT终止循环，剩下的数据就不会再被获取处理。
• 注意：你的查询带ORDER BY date_created DESC，数据库必须先找到所有符合date_created < START_TIME的数据才能排序出正确的倒序列表。这时候数据库会先完成排序操作（可能要用到临时表或磁盘排序），但不会把所有排序后的结果一次性传给存储过程，还是按需返回给游标。不过1亿行数据排序的开销极大，会占大量CPU和IO资源，这是要重点注意的。

无限制查询+EXIT终止的方式可行吗？ #

这种写法能跑，但不是最优解：

• 能用的场景：当你需要的行数（比如1万行）远小于总结果数，且排序的开销在可接受范围内时，写法简单直观，代码可读性高。
• 问题所在：
  1. 排序成本高：哪怕只取前1万行，1亿行数据的全量排序依然会消耗大量数据库资源，拖慢执行速度。
  2. 性能风险：如果后续业务需要的行数变多，或者某次数据量突然暴涨，可能导致存储过程执行超时甚至内存溢出。

更优的处理方案 #

结合你的场景（按date_created倒序取数），推荐两种优化方式：

1. 显式游标分批取数 #

手动控制游标每次获取的行数，更灵活可控：

DECLARE
  cur CURSOR FOR
    SELECT * FROM your_table
    WHERE date_created < START_TIME
    ORDER BY date_created DESC;
  rec your_table%ROWTYPE;
  row_count INT := 0;
  target_rows INT := 10000; -- 需要的总行数
BEGIN
  OPEN cur;
  LOOP
    FETCH NEXT 1000 FROM cur INTO rec; -- 每次取1000行
    EXIT WHEN NOT FOUND OR row_count >= target_rows;
    
    -- 在这里处理当前批次数据，计算平均值
    row_count := row_count + 1;
  END LOOP;
  CLOSE cur;
END;

这种方式同样是按需取数，和隐式游标逻辑一致，但能自主控制批次大小，便于调试和性能调优。不过还是解决不了全量排序的问题。

2. 利用索引+合理LIMIT避免全量排序 #

如果date_created字段有倒序索引（或者普通索引，PostgreSQL也能反向扫描），可以直接加LIMIT优化：

FOR rec IN
  SELECT * FROM your_table
  WHERE date_created < START_TIME
  ORDER BY date_created DESC
  LIMIT 10000 -- 设置一个足够大的上限值
LOOP
  -- 处理每行数据，计算平均值
END LOOP;

你之前担心加LIMIT会因为数据量不足漏算，但实际上LIMIT只是限制最多返回的行数——如果符合条件的数据不足1万行，数据库会返回所有数据，不会截断。这种方式的核心优势是：

• 数据库可以直接从索引的末尾开始扫描，不需要对全量数据排序，性能提升非常明显。
• 避免了遍历不必要的数据，减少资源消耗。

总结 #

• 隐式游标不会预加载全量结果，但ORDER BY会触发全量排序（除非有合适的索引）。
• 无限制查询+EXIT的方式可以用，但性能风险高，数据量大时不推荐。
• 最优方案是结合索引+合理的LIMIT值；如果必须遍历到满足条件为止，显式游标分批取数是更可控的选择。

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