如何让PostgreSQL查询规划器倒序扫描分区、找到值后停止扫描

方案说明 #

PostgreSQL 13 原生没有内置调整分区扫描顺序的查询Hint，你可以通过以下两种可行方案实现需求：

方案1：修改查询语句，加排序+LIMIT（最简便，推荐）

你的场景中ShippingID唯一或仅需返回匹配的第一条记录的前提下，在原有查询中增加CreatedDate倒序排序+LIMIT限制，优化器会自动调整分区扫描顺序为从最新分区到最早分区，且匹配到足够行数后就会停止扫描剩余分区。
修改后的查询示例：

SELECT * FROM dbo.shipmentmaster_partitioned sp 
WHERE "ShippingID" = 'S9169001008'
ORDER BY "CreatedDate" DESC
LIMIT 1;

如果同一个ShippingID可能存在多条匹配记录，将LIMIT值调整为你需要返回的最大行数即可。

原理：

PostgreSQL的范围分区默认按分区范围从小到大（时间从旧到新）排列，当查询指定按分区键倒序排序且有LIMIT限制时，优化器会自动反转Append节点的执行顺序，从最新的分区开始扫描，拿到足够的行数后直接终止执行，无需扫描剩余旧分区。

方案2：自定义PL/pgSQL函数遍历分区（可控性最高）

如果需要完全手动控制分区扫描逻辑，可以写一个存储函数，按时间倒序逐个查询每个分区，匹配到结果就直接返回，不会扫描任何多余分区。
函数示例：

CREATE OR REPLACE FUNCTION get_shipping_by_id(p_shipping_id varchar)
RETURNS SETOF dbo.shipmentmaster_partitioned AS $$
DECLARE
    part_name text;
    -- 动态获取倒序排列的所有分区，无需手动维护分区列表
    cur_part CURSOR FOR
        SELECT nmsp_child.nspname || '.' || child.relname AS part_fullname
        FROM pg_inherits
        JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
        JOIN pg_class child ON pg_inherits.inhrelid = child.oid
        JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
        JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
        JOIN pg_partitioned_table pt ON pt.partrelid = parent.oid
        WHERE nmsp_parent.nspname = 'dbo' AND parent.relname = 'shipmentmaster_partitioned'
        -- 按分区时间范围倒序排列
        ORDER BY pg_get_expr(child.relpartbound, child.oid) DESC;
BEGIN
    OPEN cur_part;
    LOOP
        FETCH cur_part INTO part_name;
        EXIT WHEN NOT FOUND;
        -- 逐个查询分区，匹配到结果直接返回
        RETURN QUERY EXECUTE format('SELECT * FROM %s WHERE "ShippingID" = $1 LIMIT 1', part_name)
        USING p_shipping_id;
        IF FOUND THEN
            CLOSE cur_part;
            RETURN;
        END IF;
    END LOOP;
    CLOSE cur_part;
END;
$$ LANGUAGE plpgsql STABLE;

使用方式：

SELECT * FROM get_shipping_by_id('S9169001008');

额外说明 #

你当前每个分区都已经创建了ShippingID的B树索引，上述两种方案都可以直接命中索引，查询效率很高。第三方扩展pg_hint_plan也没有直接控制分区扫描顺序的规则，不建议使用Hint实现该需求。

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