PostgreSQL继承表查询优化：如何指定特定子表提速？

我之前处理过类似的PostgreSQL表继承场景，你的问题核心其实是主表查询会强制遍历所有1400个子表，哪怕你加了过滤条件也没法跳过无关子表。要进一步提速，必须直接定位到目标供应商对应的子表来查询，而不是通过主表。

核心原因复盘 #

你之前的CTE优化虽然缩短了耗时，但本质还是在查询主表pub.net。PostgreSQL的表继承机制决定了：查询主表时，数据库会自动扫描所有继承的子表，哪怕你加了u.id = any(...)的过滤条件，它还是会逐个检查每个子表的记录是否符合条件，没法直接跳过无关子表——这就是慢的根源。

要彻底解决，必须绕过主表，直接查询指定的子表。下面是两种可行的方案：

方案一：应用层构造静态UNION ALL查询（最直观高效） #

如果你的应用可以先获取目标供应商及其合作商的ID列表，直接构造包含这些子表的UNION ALL查询，这是效率最高的方式。

步骤1：获取目标供应商列表

先查sup.members拿到N40的合作商ID：

SELECT ARRAY['N40'] || regexp_split_to_array(partnerslist, ',') AS supplier_ids
FROM sup.members WHERE id = 'N40';

假设返回结果是{'N40','G01','G06'}。

步骤2：构造子表查询

直接针对每个供应商的子表写查询，用UNION ALL拼接：

-- 查询N40的子表
SELECT 
    (SELECT m2.company FROM sup.members m2 WHERE m2.id = u.id) AS company,
    u.id, u.item,
    DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock,
    u.grade AS condition, u.stockno AS stocknumber, u.ic,
    CASE WHEN u.rprice > 0 THEN u.rprice ELSE NULL END AS price, u.qty
FROM pub.n40_stock u 
WHERE u.ic = '01036'

UNION ALL

-- 查询G01的子表
SELECT 
    (SELECT m2.company FROM sup.members m2 WHERE m2.id = u.id) AS company,
    u.id, u.item,
    DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock,
    u.grade AS condition, u.stockno AS stocknumber, u.ic,
    CASE WHEN u.rprice > 0 THEN u.rprice ELSE NULL END AS price, u.qty
FROM pub.g01_stock u 
WHERE u.ic = '01036'

UNION ALL

-- 查询G06的子表
SELECT 
    (SELECT m2.company FROM sup.members m2 WHERE m2.id = u.id) AS company,
    u.id, u.item,
    DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock,
    u.grade AS condition, u.stockno AS stocknumber, u.ic,
    CASE WHEN u.rprice > 0 THEN u.rprice ELSE NULL END AS price, u.qty
FROM pub.g06_stock u 
WHERE u.ic = '01036';

这种方式让数据库直接访问指定的几个子表，完全跳过其他1397个子表，速度会比CTE方案快一个量级，甚至可能降到几十毫秒级别。

方案二：用PL/pgSQL函数实现动态查询（适合数据库层封装） #

如果不想在应用层处理逻辑，可以写一个数据库函数，动态生成子表查询并执行：

CREATE OR REPLACE FUNCTION get_target_supplier_stock(p_vendor_id text, p_ic text)
RETURNS TABLE (
    company text,
    id text,
    item text,
    daysinstock double precision,
    condition text,
    stocknumber text,
    ic text,
    price numeric,
    qty numeric
) AS $$
DECLARE
    v_supplier_ids text[];
    v_subtable_queries text;
BEGIN
    -- 获取目标供应商+合作商ID列表，自动过滤空值
    SELECT ARRAY[p_vendor_id] || array_remove(regexp_split_to_array(partnerslist, ','), '')
    INTO v_supplier_ids
    FROM sup.members
    WHERE id = p_vendor_id;

    -- 为每个供应商生成对应的子表查询语句
    SELECT string_agg(
        format(
            'SELECT (SELECT m2.company FROM sup.members m2 WHERE m2.id = u.id) AS company,
                    u.id, u.item,
                    DATE_PART(''day'', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock,
                    u.grade AS condition, u.stockno AS stocknumber, u.ic,
                    CASE WHEN u.rprice > 0 THEN u.rprice ELSE NULL END AS price, u.qty
             FROM pub.%I u
             WHERE u.ic = %L',
            lower(s_id) || '_stock', -- 假设子表命名规则是「小写供应商ID+_stock」
            p_ic
        ),
        ' UNION ALL '
    )
    INTO v_subtable_queries
    FROM unnest(v_supplier_ids) s_id;

    -- 执行动态SQL并返回结果
    RETURN QUERY EXECUTE v_subtable_queries;
END;
$$ LANGUAGE plpgsql STABLE;

调用方式非常简单：

SELECT * FROM get_target_supplier_stock('N40', '01036');

这个函数会自动处理合作商列表，生成只包含目标子表的查询，彻底避免主表的全量扫描。

额外优化建议 #

1. 给子表建立复合索引：为每个子表的ic和id字段建立复合索引，比如：

   CREATE INDEX idx_substock_ic_id ON pub.n40_stock (ic, id);
   

   查询时可以直接命中索引，进一步缩短耗时。
2. 验证子表命名规则：确保你的子表命名规则和函数里的lower(s_id) || '_stock'一致，如果规则不同（比如大写或其他后缀），需要调整函数里的子表名生成逻辑。
3. 清理无效合作商ID：如果partnerslist里存在空字符串或无效ID，函数里的array_remove已经帮你过滤掉了，避免尝试查询不存在的子表。

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