PostgreSQL查询执行时间随表数量激增问题及优化诉求

环境 #

Ubuntu 24.04 系统，PostgreSQL 16.3

问题描述 #

当schema内表数量达到95张及以上时，用开源库中的SQL查询指定schema/表的外键约束，PostgreSQL优化器会自动切换执行计划，查询耗时从7ms暴涨到1254ms。试过重新索引、调整缓存大小，都没法恢复高效执行计划。需求是：不修改开源库的SQL语句，强制优化器用高效的执行方式。

原查询语句 #

SELECT tc.constraint_name, kcu.column_name, ccu.table_name, ccu.column_name, 0
FROM information_schema.table_constraints tc
         JOIN information_schema.key_column_usage AS kcu
              ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
         JOIN (SELECT ROW_NUMBER()
                      OVER ( PARTITION BY table_schema, table_name, constraint_name ORDER BY row_num ) AS ordinal_position,
                      table_schema,
                      table_name,
                      column_name,
                      constraint_name
               FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_num,
                            table_schema,
                            table_name,
                            column_name,
                            constraint_name
                     FROM information_schema.constraint_column_usage) t) AS ccu
              ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema AND
                 ccu.ordinal_position = kcu.ordinal_position
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
  AND tc.table_name = 'xxx';

两种执行计划对比 #

高效执行计划 #

->  Index Only Scan using pg_namespace_oid_index on pg_namespace nc_3  (cost=0.13..0.18 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (oid = c_3.connamespace)
Heap Fetches: 1
Buffers: shared hit=3
Planning:
  Buffers: shared hit=779 read=64
Planning Time: 9.951 ms
Execution Time: 7.677 ms

低效执行计划 #

->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.29..0.46 rows=1 width=70) (actual time=0.019..0.020 rows=1 loops=1)
Index Cond: ((attrelid = r_2.oid) AND (attnum = ((information_schema._pg_expandarray(c_2.conkey))).x))
"        Filter: ((NOT attisdropped) AND (pg_has_role(r_2.relowner, 'USAGE'::text) OR has_column_privilege(r_2.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))"
Buffers: shared hit=3
Planning:
  Buffers: shared hit=118
Planning Time: 10.022 ms
Execution Time: 1254.787 ms

不修改原SQL的解决方案 #

1. 用计划管理固定高效计划（PG12+支持） #

PostgreSQL 12及以上版本支持计划管理，能把高效执行计划锁定：

• 先获取目标查询的计划句柄：

  SELECT pg_get_plan_handle('SELECT tc.constraint_name, kcu.column_name, ccu.table_name, ccu.column_name, 0
  FROM information_schema.table_constraints tc
           JOIN information_schema.key_column_usage AS kcu
                ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
           JOIN (SELECT ROW_NUMBER()
                        OVER ( PARTITION BY table_schema, table_name, constraint_name ORDER BY row_num ) AS ordinal_position,
                        table_schema,
                        table_name,
                        column_name,
                        constraint_name
                 FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_num,
                              table_schema,
                              table_name,
                              column_name,
                              constraint_name
                       FROM information_schema.constraint_column_usage) t) AS ccu
                ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema AND
                   ccu.ordinal_position = kcu.ordinal_position
  WHERE tc.constraint_type = ''FOREIGN KEY''
    AND tc.table_schema = ''public''
    AND tc.table_name = ''xxx'';');
  

• 把获取到的<plan_handle>替换到下面语句，强制启用该计划：

  ALTER PLAN <plan_handle> SET ENABLED = true, FORCE = true;
  

  注意：如果后续表结构或统计信息变更，可能需要更新计划。

2. 临时调整优化器参数 #

针对该查询临时修改优化器参数，逼它选高效计划：

• 会话级设置：如果开源库允许在执行查询前先跑参数设置，就先执行：

  -- 禁用哈希连接和合并连接，强制用嵌套循环（高效计划的核心执行方式）
  SET enable_hashjoin = off;
  SET enable_mergejoin = off;
  -- 或者调整索引扫描的成本权重，让索引扫描更划算
  SET random_page_cost = 1.1;
  

  查询完成后可以重置参数：RESET enable_hashjoin; RESET enable_mergejoin; RESET random_page_cost;
• 全局设置：如果这个查询是系统高频用的，可以改postgresql.conf里的参数，但不推荐——会影响其他查询的执行计划。

3. 用自定义函数封装查询 #

写个自定义函数，在函数内部先设置优化器参数，再执行原查询，然后让开源库调用这个函数：

CREATE OR REPLACE FUNCTION get_foreign_keys(p_schema text, p_table text)
RETURNS TABLE(constraint_name text, column_name text, ref_table_name text, ref_column_name text, flag integer) AS $$
BEGIN
  -- 先设置参数，禁用低效的连接方式
  SET enable_hashjoin = off;
  -- 执行原查询
  RETURN QUERY
  SELECT tc.constraint_name, kcu.column_name, ccu.table_name, ccu.column_name, 0::integer
  FROM information_schema.table_constraints tc
           JOIN information_schema.key_column_usage AS kcu
                ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
           JOIN (SELECT ROW_NUMBER()
                        OVER ( PARTITION BY table_schema, table_name, constraint_name ORDER BY row_num ) AS ordinal_position,
                        table_schema,
                        table_name,
                        column_name,
                        constraint_name
                 FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_num,
                              table_schema,
                              table_name,
                              column_name,
                              constraint_name
                       FROM information_schema.constraint_column_usage) t) AS ccu
                ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema AND
                   ccu.ordinal_position = kcu.ordinal_position
  WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema = p_schema
    AND tc.table_name = p_table;
  -- 重置参数，不影响后续查询
  RESET enable_hashjoin;
END;
$$ LANGUAGE plpgsql STABLE;

之后让开源库调用SELECT * FROM get_foreign_keys('public', 'xxx');就行，原查询逻辑完全没改，只是加了一层参数控制。

4. 精准更新系统表统计信息 #

虽然你试过重新索引，但可以针对information_schema依赖的系统表单独更新统计信息，让优化器拿到更准的基数估计：

ANALYZE pg_namespace;
ANALYZE pg_attribute;
ANALYZE pg_constraint;

有时候优化器选低效计划，就是因为统计信息过时，导致对数据量的判断出错。

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