PostgreSQL部分唯一索引批量查询时无法稳定触发的优化求助

从执行计划可以明确，慢查询的核心问题是PostgreSQL优化器的统计信息估算错误：

• 新增portfolio的数据量未达到自动analyze触发阈值时，统计信息中该portfolio_id对应的预估行数极低（执行计划中预估rows=1）
• 优化器错误判断：先扫描索引中该portfolio的所有行，再过滤consumer_id的成本更低，因此仅将portfolio_id作为索引条件，consumer_id = ANY(...)降级为后置过滤条件
• 随着portfolio下数据增长到10万级别仍未触发统计更新时，就会出现扫描全量portfolio索引行再过滤的极慢情况，直到数据量触发统计更新后，优化器才会选择将两个列都作为索引条件的高效执行计划。

• 方案1：修改查询写法强制触发全索引匹配
  把ANY(ARRAY)写法替换为unnest数组后等值连接，优化器会优先选择两列联合索引的精确匹配，不会出现降级过滤的情况，代码示例：

SELECT e.*
FROM unnest(ARRAY['C1','C2',...,'C1000']) AS t(cid)
INNER JOIN enrollments e
  ON e.portfolio_id = 1
  AND e.consumer_id = t.cid
  AND e.deleted_at IS NULL;

该方案无需修改表结构和配置，改完即可生效，测试验证成本极低。

• 方案2：优化统计信息收集规则解决估算错误

  1. 提升portfolio_id列的统计粒度，让PostgreSQL收集更多不同portfolio的分布数据：

  ALTER TABLE enrollments ALTER COLUMN portfolio_id SET STATISTICS 1000;
  

  2. 调低该表的自动analyze触发阈值，避免新增portfolio的数据量迟迟不触发统计更新：

  -- 关闭百分比阈值，固定行数变化1万就触发统计更新
  ALTER TABLE enrollments SET (autovacuum_analyze_scale_factor = 0, autovacuum_analyze_threshold = 10000);
  

  该方案从根源解决优化器估算错误的问题，适合所有查询场景。

• 方案3：全量同步场景直接使用批量同步逻辑，避免逐批查询
  你当前的场景是全量文件同步，完全可以跳过逐批查询判断的逻辑，用临时表批量导入+一次性对比更新，效率提升可达数百倍：

-- 1. 创建临时表存储文件全量数据
CREATE TEMP TABLE tmp_enrollments (
  consumer_id varchar(255) NOT NULL,
  identity_id varchar(255) NOT NULL
) ON COMMIT DROP;

-- 2. 用COPY命令把整个文件导入临时表，性能远高于逐行插入
COPY tmp_enrollments FROM '/path/to/your/file.csv' WITH (FORMAT csv);

-- 3. 批量删除不在文件中的旧数据
UPDATE enrollments
SET deleted_at = CURRENT_TIMESTAMP
WHERE portfolio_id = 1
  AND deleted_at IS NULL
  AND consumer_id NOT IN (SELECT consumer_id FROM tmp_enrollments);

-- 4. 批量新增/更新文件中的数据
INSERT INTO enrollments (portfolio_id, consumer_id, identity_id, deleted_at)
SELECT 1, t.consumer_id, t.identity_id, NULL
FROM tmp_enrollments t
LEFT JOIN enrollments e
  ON e.portfolio_id = 1
  AND e.consumer_id = t.consumer_id
  AND e.deleted_at IS NULL
WHERE e.id IS NULL
ON CONFLICT (portfolio_id, consumer_id) WHERE (deleted_at IS NULL)
DO UPDATE SET identity_id = EXCLUDED.identity_id;

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