PostgreSQL 9.5.2含text列查询性能骤降问题求助

先看你的具体场景：使用PostgreSQL 9.5.2，11张表做内连接，单表平均约10K条记录，排除ic表的TEXT列时查询耗时4-5秒，包含该列直接飙升至55秒。更关键的矛盾点是：EXPLAIN ANALYZE显示服务器端执行时间仅378ms，但实际查询要跑1分钟——咱们来拆解背后的原因和解决办法：

核心原因分析 #

1. 数据传输与客户端处理开销（最可能的主因） #

EXPLAIN ANALYZE只统计数据库服务器端的执行时间，但实际查询的端到端耗时还包含两部分：

• 数据库将包含大TEXT字段的结果集传输到客户端的网络耗时
• 客户端接收、解析、渲染这些大文本内容的处理耗时

从你的执行计划来看，最终返回了24118 rows，如果每行TEXT字段按平均5KB计算，总数据量就超过120MB，传输+客户端处理这部分数据的时间，完全能把总耗时拉到1分钟，而服务器端的执行时间（378ms）是正常的——因为服务器只是完成了数据读取，剩下的压力都在网络和客户端。

2. 磁盘IO缓存的差异 #

有时候EXPLAIN ANALYZE执行时，目标数据刚好在内存缓存（shared_buffers）里，所以执行快；但实际查询时缓存失效，数据库需要从磁盘读取大TEXT字段，这会额外增加耗时。不过你的执行计划里actual time已经是378ms，这部分影响可能不如传输开销大，但也不能完全排除。

3. TEXT字段的TOAST存储特性 #

PostgreSQL中，超过一定大小的TEXT字段会被存成TOAST（The Oversized-Attribute Storage Technique），读取TOAST数据需要额外的IO操作，但这部分耗时应该已经包含在EXPLAIN ANALYZE的actual time里，所以不是核心问题。

解决方案（按优先级排序） #

• 优先避免查询不必要的大字段：如果业务逻辑不需要这个TEXT列，就不要在SELECT语句中包含它——这是最直接有效的优化，你已经验证过排除它后耗时回归正常。
• 分批获取结果：如果必须要这个TEXT列，用FETCH NEXT n ROWS ONLY或者分页查询，减少单次传输的数据量，避免一次性处理几万条大文本。
• 优化数据传输：
  • 开启PostgreSQL的传输压缩：在postgresql.conf里设置ssl_compression = on（如果使用SSL连接），或者客户端连接时指定compression=1（比如psql执行SET compression TO 1;）。
  • 优化网络环境：如果客户端和服务器不在同一机房，网络传输大文件的耗时会更明显，尽量将客户端部署到靠近服务器的位置。
• 调整缓存配置：把shared_buffers参数调整为系统内存的25%-50%，确保ic表的TEXT数据能被缓存到内存中，减少磁盘IO次数。
• 更换客户端工具：有些GUI工具（比如pgAdmin）处理大文本时性能较差，换成psql命令行工具试试，看耗时是否下降——如果是工具的问题，换个更高效的客户端即可。

你的执行计划参考 #

Nested Loop Left Join (cost=4.04..156.40 rows=10 width=616) (actual time=3.092..377.128 rows=24118 loops=1)
 -> Nested Loop Left Join (cost=3.90..59.92 rows=7 width=603) (actual time=2.834..110.842 rows=14325 loops=1)
 -> Nested Loop Left Join (cost=3.76..58.56 rows=7 width=604) (actual time=2.832..101.481 rows=12340 loops=1)
 -> Nested Loop (cost=3.62..57.19 rows=7 width=590) (actual time=2.830..90.614 rows=8436 loops=1)
 Join Filter: (i."Id" = ic."ImId")
 -> Nested Loop (cost=3.33..51.42 rows=7 width=210) (actual time=2.807..65.782 rows=8436 loops=1)
 -> Nested Loop (cost=3.19..50.21 rows=7 width=187) (actual time=2.424..54.596 rows=8436 loops=1)
 -> Nested Loop (cost=2.77..46.16 rows=7 width=175) (actual time=1.944..32.056 rows=8436 loops=1)
 -> Nested Loop (cost=2.35..23.66 rows=5 width=87) (actual time=1.750..1.877 rows=4 loops=1)
 -> Hash Join (cost=2.22..22.84 rows=5 width=55) (actual time=1.492..1.605 rows=4 loops=1)
 Hash Cond: (i."ImtypId" = it."Id")
 -> Nested Loop (cost=0.84..21.29 rows=34 width=51) (actual time=1.408..1.507 rows=30 loops=1)
 -> Nested Loop (cost=0.56..9.68 rows=34 width=35) (actual time=1.038..1.053 rows=30 loops=1)
 -> Index Only Scan using ev_query on "table_Ev" e (cost=0.28..4.29 rows=1 width=31) (actual time=0.523..0.523 rows=1 loops=1)
 Index Cond: ("Id" = 1301)
 Heap Fetches: 0
 -> Index Only Scan using asmitm_query on "table_AsmItm" ai (cost=0.28..5.07 rows=31 width=8) (actual time=0.499..0.508 rows=30 loops=1)
 Index Cond: (("AsmId" = e."AsmId") AND ("IsActive" = true))
 Filter: "IsActive"
 Heap Fetches: 0
 -> Index Only Scan using itm_query on "table_Itm" i (cost=0.28..0.33 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=30)
 Index Cond: ("Id" = ai."ImId")
 Heap Fetches: 0
 -> Hash (cost=1.33..1.33 rows=4 width=12) (actual time=0.026..0.026 rows=4 loops=1)
 Buckets: 1024 Batches: 1 Memory Usage: 9kB
 -> Seq Scan on "ItmTyp" it (cost=0.00..1.33 rows=4 width=12) (actual time=0.013..0.018 rows=4 loops=1)
 Filter: ("ParentId" = 12)
 Rows Removed by Filter: 22
 -> Index Only Scan using jur_query on "table_Jur" j (cost=0.14..0.15 rows=1 width=36) (actual time=0.065..0.066 rows=1 loops=4)
 Index Cond: ("Id" = i."JurId")
 Heap Fetches: 4
 -> Index Scan using pwsres_evid_ImId_canid_query on "table_PwsRes" p (cost=0.42..3.78 rows=72 width=92) (actual time=0.056..6.562 rows=2109 loops=4)
 Index Cond: (("EvId" = 1301) AND ("ImId" = i."Id"))
 -> Index Only Scan using user_query on "table_User" u (cost=0.42..0.57 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=8436)
 Index Cond: ("Id" = p."CanId")
 Heap Fetches: 0
 -> Index Only Scan using ins_query on "table_Ins" ins (cost=0.14..0.16 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=8436)
 Index Cond: ("Id" = u."InsId")
 Heap Fetches: 0
 -> Index Scan using "IX_ItmCont_ImId" on "table_ItmCont" ic (cost=0.29..0.81 rows=1 width=392) (actual time=0.002..0.002 rows=1 loops=8436)
 Index Cond: ("ImId" = p."ImId")
 Filter: ("ContTyp" = 'CP'::text)
 Rows Removed by Filter: 1
 -> Index Scan using "IX_FreDetail_FreId" on "table_FreDetail" f (cost=0.14..0.18 rows=2 width=22) (actual time=0.000..0.001 rows=1 loops=8436)
 Index Cond: ("FreId" = p."FreId")
 -> Index Scan using "IX_DurDetail_DurId" on "table_DurDetail" d (cost=0.14..0.17 rows=2 width=7) (actual time=0.000..0.000 rows=0 loops=12340)
 Index Cond: ("DurId" = p."DurId")
 -> Index Scan using "IX_DruConsRouteDetail_DruConsRouId" on "table_DruConsRouDetail" dr (cost=0.14..0.18 rows=2 width=21) (actual time=0.001..0.001 rows=1 loops=14325)
 Index Cond: ("DruConsRouteId" = p."RouteId")
 SubPlan 1
 -> Index Only Scan using asm_query on "table_Asm" (cost=0.14..8.16 rows=1 width=26) (actual time=0.001..0.001 rows=1 loops=24118)
 Index Cond: ("Id" = e."AsmId")
 Heap Fetches: 24118
 SubPlan 2
 -> Seq Scan on "ItmTyp" ity (cost=0.00..1.33 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=24118)
 Filter: ("Id" = it."ParentId")
 Rows Removed by Filter: 25
Planning time: 47.056 ms
Execution time: 378.229 ms

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