postgresql-磁盘空间不足问题排查

问题背景

加压测试过程中发现插入数据过程中报错：could not write to hash-join temporary file: 设备上没有空间。但是查看服务器还有很多空闲空间，是什么导致这样的错误呢？

查看执行脚本

insert into db_zjgj.result_rule_cwjbxx_db_sacw_t_cw_cwjbxx 
select db_zjgj.uuid(),c_bh,'2E810338E4F2CEE0462E9A021A0E0816','财物-财物处置信息中，非先行处置类的处置信息，处置日期不能小于裁判生效日期','7B7DCB103239F5CBAB4106016DE258D1' 
from db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx  where EXISTS (
SELECT
 1
FROM
 db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx
WHERE
 db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh = db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_bh
AND db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.d_czsj IS NOT NULL
)
AND EXISTS (
 SELECT
  1
 FROM
  db_zjgj.temp_ajjbxx_db_sacw_t_aj_ajjbxx AS ajjbxx
 WHERE
  db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh = ajjbxx.c_bh
 AND ajjbxx.d_pjsxrq IS NOT NULL
)
AND EXISTS (
 SELECT
  1
 FROM
  db_zjgj.temp_ajjbxx_db_sacw_t_aj_ajjbxx AS ajjbxx,
  db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx
 WHERE
  db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh = ajjbxx.c_bh
 AND db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_bh = db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh
 AND db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.d_czsj < ajjbxx.d_pjsxrq
)
--查看执行计划
Hash Semi Join (cost=270531577.85..324240042.87 rows=113055 width=33)
 Hash Cond: ((temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh)::text = (ajjbxx.c_bh)::text)
 -> Hash Semi Join (cost=270527939.60..324202660.37 rows=113055 width=99)
       Hash Cond: (((temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh)::text = (temp_cwczxx_db_sacw_t_cw_cwczxx_1.c_cwbh)::text)