PostgreSQL特定INSERT查询随机慢问题排查与优化咨询

问题背景 #

我们遇到一个特定INSERT查询的性能异常：该查询有时执行耗时超620000ms，但随机出现——再次执行同一查询仅需约79ms。

查询语句 #

INSERT INTO product_store(id_store, cod_product, account_id, stock,  product,active) 
SELECT s.id_store, t.cod_product, t.account_id, 0,  i.product, i.active
FROM tmp_import_product t, store s, product i 
WHERE t.id_process_import_product = 99988 
AND s.account_id = t.account_id 
AND s.active = 'S' 
AND i.account_id = t.account_id 
AND i.cod_product = t.cod_product 
AND i.manage_stock = 'S' 
AND i.variant_stock = 'N' 
AND t.existent = 'S' 
AND t.error = 'N' 
AND NOT EXISTS(SELECT 1 FROM product_store i WHERE i.cod_product = t.cod_product AND i.account_id = t.account_id AND i.id_store = s.id_store)

执行计划 #

Insert on product_store  (cost=1.57..296.01 rows=1 width=733) (actual rows=0 loops=1)
  Buffers: shared hit=532388600 read=21556 dirtied=186
  ->  Nested Loop  (cost=1.57..296.01 rows=1 width=733) (actual rows=0 loops=1)
        Buffers: shared hit=532388600 read=21556 dirtied=186
        ->  Nested Loop  (cost=1.14..291.09 rows=1 width=76) (actual rows=22200 loops=1)
                Join Filter: (t.account_id = s.account_id)
              Buffers: shared hit=74893
              ->  Nested Loop  (cost=0.85..290.40 rows=2 width=76) (actual rows=7400 loops=1)
                    Buffers: shared hit=37893
                    ->  Index Scan using ix_tmp_import_product_2 on tmp_import_product t  (cost=0.42..120.32 rows=64 width=21) (actual rows=7400 loops=1)
                            Index Cond: (t.id_process_import_product = '96680'::bigint)
                            Filter: ((t.existent = 'S'::bpchar) AND (t.error = 'N'::bpchar))
                            Rows Removed by Filter: 102
                          Buffers: shared hit=3515
                    ->  Index Scan using pk_product on product i  (cost=0.43..2.66 rows=1 width=64) (actual rows=1 loops=7400)
                            Index Cond: (((i.cod_product)::text = t.cod_product) AND (i.account_id = t.account_id))
                            Filter: ((i.manage_stock = 'S'::bpchar) AND (i.variant_stock = 'N'::bpchar))
                            Rows Removed by Filter: 0
                          Buffers: shared hit=34291
              ->  Index Scan using store_account_id_active_index on store s  (cost=0.28..0.32 rows=2 width=16) (actual rows=3 loops=7400)
                      Index Cond: ((s.account_id = i.account_id) AND (s.active = 'S'::bpchar))
                    Buffers: shared hit=37000
        ->  Index Scan using idx_product_store_account_id_id_store on product_store i_1  (cost=0.43..4.90 rows=1 width=24) (actual rows=1 loops=22200)
                Index Cond: ((i_1.id_store = s.id_store) AND (i_1.account_id = t.account_id))
                Filter: ((i_1.cod_product)::text = t.cod_product)
                Rows Removed by Filter: 141420
              Buffers: shared hit=532306240 read=21556 dirtied=186
Trigger t_insert_product_stock: time=0.000 calls=1

一、检查表统计信息是否更新 #

1. 查看统计信息状态 #

执行以下SQL，对比估算行数与实际行数的差异：

-- 查看tmp_import_product的统计基础信息
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'tmp_import_product';

-- 查看字段级统计分布
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'tmp_import_product';

-- 获取tmp_import_product的实际匹配行数
SELECT COUNT(*) 
FROM tmp_import_product 
WHERE id_process_import_product = 99988 
  AND existent = 'S' 
  AND error = 'N';

如果n_live_tup（估算活行数）与实际查询结果差异超过20%，说明统计信息已过时。

2. 手动更新统计信息 #

若确认统计信息不准确，执行强制更新：

-- 全表统计更新
ANALYZE tmp_import_product;

-- 针对关键字段精准更新（适合数据分布不均的场景）
ANALYZE tmp_import_product (id_process_import_product, existent, error, account_id, cod_product);

二、查询优化方案 #

从执行计划看，NOT EXISTS子句的索引扫描是核心性能瓶颈：每次循环需过滤141420行，且总缓冲区命中量极高，属于典型的低效过滤场景。

1. 优化NOT EXISTS的索引 #

当前idx_product_store_account_id_id_store仅包含id_store和account_id，cod_product需额外过滤。创建覆盖三个字段的复合索引：

-- 若account_id+id_store+cod_product是唯一键，创建唯一索引（性能更优）
CREATE UNIQUE INDEX idx_product_store_unique ON product_store (account_id, id_store, cod_product);

-- 无需唯一约束时，创建普通复合索引
CREATE INDEX idx_product_store_account_store_product ON product_store (account_id, id_store, cod_product);

该索引可让NOT EXISTS子句直接通过索引完成过滤，无需回表或额外行过滤。

2. 重构查询逻辑 #

将NOT EXISTS改为LEFT JOIN + IS NULL，部分场景下优化器会生成更高效的执行计划：

INSERT INTO product_store(id_store, cod_product, account_id, stock, product, active)
SELECT s.id_store, t.cod_product, t.account_id, 0, i.product, i.active
FROM tmp_import_product t
JOIN product i ON i.account_id = t.account_id 
              AND i.cod_product = t.cod_product 
              AND i.manage_stock = 'S' 
              AND i.variant_stock = 'N'
JOIN store s ON s.account_id = t.account_id 
            AND s.active = 'S'
LEFT JOIN product_store ps ON ps.account_id = t.account_id 
                          AND ps.id_store = s.id_store 
                          AND ps.cod_product = t.cod_product
WHERE t.id_process_import_product = 99988 
  AND t.existent = 'S' 
  AND t.error = 'N'
  AND ps.id_store IS NULL;

3. 临时表专项优化 #

若tmp_import_product是临时表，需确保索引覆盖查询条件，并手动触发统计更新：

-- 创建临时表时直接添加覆盖索引
CREATE TEMP TABLE tmp_import_product (
    -- 字段定义
) WITH (autovacuum_enabled = true);
CREATE INDEX ix_tmp_import_product_full ON tmp_import_product (id_process_import_product, existent, error, account_id, cod_product);

-- 写入数据后强制更新统计
ANALYZE tmp_import_product;

4. 强制连接顺序 #

执行计划中估算行数与实际差异极大（计划rows=1，实际22200），说明优化器选择了错误的连接顺序。可强制按写的顺序连接：

SET join_collapse_limit = 1;
-- 执行INSERT查询后恢复默认值
SET join_collapse_limit = default;

三、其他可能原因 #

1. 锁竞争 #

查询突增耗时可能是product_store被其他会话持有长时锁（如未提交的UPDATE/DELETE事务），可查看锁状态：

SELECT pid, locktype, mode, relation::regclass
FROM pg_locks
WHERE relation = 'product_store'::regclass;

2. 缓冲区命中率波动 #

若执行时出现大量磁盘读（read字段数值高），说明缓存失效或内存不足。检查shared_buffers配置是否匹配服务器内存规模，以及系统内存使用率。

3. 临时表数据波动 #

若tmp_import_product是会话级临时表，不同会话的数据量差异极大，会导致统计信息无法适配所有场景；或该表频繁写入/清空，autovacuum未能及时更新统计。

4. 触发器隐性开销 #

虽然执行计划中触发器耗时为0，但需检查t_insert_product_stock的内部逻辑，是否存在依赖外部资源（如远程调用、其他表锁）导致的偶发延迟。

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