PostgreSQL 9.6/10查询性能异常缓慢问题排查求助

让我来帮你拆解这个问题——从你提供的执行计划和系统信息来看，查询慢和117GB临时文件的核心原因是内存不足导致查询操作被迫使用磁盘临时文件，结合你的场景，我整理了几个针对性的排查和解决步骤：

一、执行计划关键问题分析 #

先看你的EXPLAIN ANALYZE输出：

'Aggregate (cost=297026.78..297026.79 rows=1 width=8) (actual time=5959.018..5959.018 rows=1 loops=1)'
' Buffers: shared hit=1697364 read=193415'
' -> Hash Join (cost=3.78..291505.59 rows=2208477 width=0) (actual time=0.106..5735.227 rows=2298433 loops=1)'
' Hash Cond: (sl.dealership_id = d.id)'
' Buffers: shared hit=1697364 read=193415'
' -> Merge Left Join (cost=0.86..261136.11 rows=2208477 width=8) (actual time=0.030..5216.924 rows=2298433 loops=1)'
' Merge Cond: (sl.customer_id = ce.customer_id)'
' Buffers: shared hit=1697362 read=193415'
' -> Index Scan using fki_fk_customer_id on sales_lead sl (cost=0.43..169112.10 rows=2113129 width=16) (actual time=0.010..3929.479 rows=2113057 loops=1)'
' Buffers: shared hit=1695917 read=187211'
' -> Index Only Scan using idx_customer_email_customer_id on customer_email ce (cost=0.43..58979.27 rows=2270856 width=4) (actual time=0.016..390.700 rows=2792048 loops=1)'
' Heap Fetches: 0'
' Buffers: shared hit=1445 read=6204'
' -> Hash (cost=2.64..2.64 rows=23 width=4) (actual time=0.056..0.056 rows=23 loops=1)'
' Buckets: 1024 Batches: 1 Memory Usage: 9kB'
' Buffers: shared hit=2'
' -> Hash Join (cost=1.09..2.64 rows=23 width=4) (actual time=0.046..0.051 rows=23 loops=1)'
' Hash Cond: (d.dealership_group_id = dg.id)'
' Buffers: shared hit=2'
' -> Seq Scan on dealership d (cost=0.00..1.23 rows=23 width=8) (actual time=0.011..0.012 rows=23 loops=1)'
' Buffers: shared hit=1'
' -> Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.026..0.026 rows=4 loops=1)'
' Buckets: 1024 Batches: 1 Memory Usage: 9kB'
' Buffers: shared hit=1'
' -> Seq Scan on dealership_group dg (cost=0.00..1.04 rows=4 width=4) (actual time=0.005..0.006 rows=4 loops=1)'
' Buffers: shared hit=1'
'Planning time: 1.417 ms'
'Execution time: 5959.196 ms'

几个明显的信号：

1. Merge Left Join耗时占比极高：sales_lead的Index Scan耗时3929ms，且shared read=187211说明大量数据需要从磁盘读取，内存缓存命中率不足。
2. Hash Join潜在内存溢出：虽然连接小表dealership/dealership_group没问题，但连接200万+行的sales_lead时，Hash表可能因内存不足溢出到磁盘，这正是临时文件暴涨的原因。
3. 临时文件未清理：正常情况下PostgreSQL会在查询结束后删除临时文件，持续存在的117GB临时文件大概率是未结束的事务持有，或是磁盘权限问题。

二、针对性解决步骤 #

1. 调整work_mem参数（最紧急） #

work_mem控制单个操作（如Hash Join、Sort）可使用的内存，默认值通常仅4MB，远不足以处理200万行的Hash Join。

• 临时测试：在当前会话中执行SET work_mem = '64MB';，再重新运行查询，观察临时文件是否减少、查询时间是否下降。
• 长期调整：在postgresql.conf中修改全局值，根据服务器内存设置（比如8GB内存可设为work_mem = '64MB'，16GB内存可设为128MB），避免设置过大导致内存耗尽。修改后需重启数据库或执行SELECT pg_reload_conf();生效。

2. 优化effective_cache_size #

这个参数告诉优化器系统可用于缓存数据的内存总量，若设置过低，优化器会选择低效的执行计划。

• 建议设置为服务器可用内存的50%-75%，例如8GB内存设为effective_cache_size = '4GB'，修改后执行VACUUM ANALYZE;让优化器更新统计信息。

3. 检查索引效率与碎片化 #

虽然你已创建索引，但索引碎片化或覆盖性不足会影响性能：

• 重建sales_lead的关联索引：REINDEX INDEX fki_fk_customer_id;，消除索引碎片化。
• 确认索引是否为覆盖索引：如果fki_fk_customer_id仅包含customer_id和dealership_id，那查询时无需回表，效率会更高（你的count查询不需要其他字段，当前索引应该足够，但可以检查）。

4. 清理未结束事务与临时文件 #

临时文件持续存在的解决方法：

• 检查未结束的事务：SELECT pid, query, state FROM pg_stat_activity WHERE state = 'idle in transaction';，找到长期 idle 的事务，用SELECT pg_terminate_backend(pid);结束。
• 检查临时文件目录权限：确保PostgreSQL进程对pg_temp目录（默认在数据目录下）有读写权限，若磁盘空间不足也会导致无法删除临时文件。

5. 考虑升级PostgreSQL版本 #

你使用的9.6和10版本已停止主流支持，后续版本（如12+）在查询优化、内存管理、临时文件处理上有大量改进，升级后能从根本上提升这类查询的性能。

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