使用Text列时PostgreSQL查询性能较慢

Question

我们使用的是PostgreSQL 9.5.2

我们有11个表，每个表中平均有10K条记录

其中一个表包含最大内容大小为12K个字符的文本列。

当我们从select语句中排除文本列时，大约需要5秒，而当我们包括文本列时，大约需要55秒。如果我们从同一张表中选择任何其他列，它会工作得很好，但一旦我们选择文本列，性能就会继续提高。所有的表都是内连接的。

你能建议一下如何解决这个问题吗？

Explain输出显示378毫秒，但实际上，获取这些数据大约需要1分钟。因此，当我们从"ic“表中排除文本列时，会在4-5秒内得到结果。

"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"

Answer 1

如果explain analyze的输出花费了378ms，那么这就是查询所花费的时间，并且可能没有太大的改进空间。如果传输和加载数据需要1分钟，您需要在这一端进行工作。

如果您试图在psql或pgadmin中查看非常宽的行，可能需要一些时间来计算行宽或呈现html，但这与查询性能无关。