PostgreSQL循环查询：如何避免大量临时表空间占用？

为什么加ORDER BY id会占用大量临时表空间？ #

1. PostgreSQL堆表的无序性：big_table是堆表，数据本身无序存储，主键（B树索引）虽有序，但默认情况下SELECT ... ORDER BY id可能选择全表顺序扫描+显式排序的执行计划，此时需要临时表存储排序后的完整结果集，导致大量临时空间占用。
2. PL/pgSQL循环的物化特性：即便查询走主键索引扫描，PL/pgSQL的FOR ... IN循环默认会把整个查询结果物化到临时存储，再逐行返回——因为排序、JOIN这类操作必须拿到完整结果才能处理，数据库无法流式返回部分结果。
3. 无ORDER BY时的流式返回：去掉ORDER BY id后，查询可通过顺序扫描直接流式返回数据，无需先聚合或排序所有结果，因此不会占用大量临时空间。

避免大量临时表空间占用的方案 #

1. 强制走主键索引，避免全表排序 #

利用big_table主键id非空的特性，引导查询计划走主键索引，跳过全表扫描后的显式排序：

FOR rec in
    SELECT s.id_main AS id
    FROM big_table b
    LEFT JOIN small_table s ON s.id = b.word_id
    WHERE b.id IS NOT NULL  -- 引导查询走主键索引
    ORDER BY b.id
LOOP
    -- do something
END LOOP;

也可直接用索引提示强制使用主键索引：

FOR rec in
    SELECT s.id_main AS id
    FROM big_table b USE INDEX (big_table__pk)
    LEFT JOIN small_table s ON s.id = b.word_id
    ORDER BY b.id
LOOP
    -- do something
END LOOP;

2. 按主键范围分批处理（推荐） #

把大表拆分成多个小批次，每次只处理一个主键范围内的数据，避免一次性生成全量结果集：

DECLARE
    min_id bigint;
    max_id bigint;
    current_id bigint;
    batch_size bigint := 10000;  -- 根据服务器性能调整批次大小
BEGIN
    SELECT MIN(id), MAX(id) INTO min_id, max_id FROM big_table;
    current_id := min_id;
    
    WHILE current_id <= max_id LOOP
        FOR rec IN
            SELECT s.id_main AS id
            FROM big_table b
            LEFT JOIN small_table s ON s.id = b.word_id
            WHERE b.id BETWEEN current_id AND current_id + batch_size - 1
            ORDER BY b.id
        LOOP
            -- do something
        END LOOP;
        current_id := current_id + batch_size;
    END LOOP;
END;

这种方式每次仅处理少量数据，JOIN和排序的结果集很小，几乎不会占用大量临时空间，同时WHERE b.id BETWEEN ...会高效走主键索引。

3. 调整work_mem参数，让排序在内存完成 #

如果必须一次性处理全表，可临时增大work_mem，让排序操作在内存中完成，避免写入磁盘临时表：

-- 仅对当前会话生效，根据服务器内存调整大小，比如64MB/128MB
SET work_mem = '64MB';

FOR rec in
    SELECT s.id_main AS id
    FROM big_table b
    LEFT JOIN small_table s ON s.id = b.word_id
    ORDER BY b.id
LOOP
    -- do something
END LOOP;

注意：work_mem是每个数据库操作的内存配额，设置过大可能导致内存耗尽，需根据服务器总内存合理调整。

4. 使用显式游标控制结果获取 #

显式游标可更灵活地控制结果集的获取逻辑，虽不能完全避免排序的物化，但可减少不必要的内存占用：

DECLARE
    cur CURSOR FOR
        SELECT s.id_main AS id
        FROM big_table b
        LEFT JOIN small_table s ON s.id = b.word_id
        ORDER BY b.id;
    rec RECORD;
BEGIN
    OPEN cur;
    LOOP
        FETCH NEXT FROM cur INTO rec;
        EXIT WHEN NOT FOUND;
        -- do something
    END LOOP;
    CLOSE cur;
END;

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