0

We are using PostgreSQL 9.5.2

We have 11 tables with around average of 10K records in each table

One of the table contains text column for which maximum content size is 12K characters.

When we exclude text column from select statement, it comes in around 5 seconds, and when we include text column, it take around 55 seconds. if we select any other column from same table, it works fine, but as soon as we take text column, performance goes on toss. All tables are inner joined.

Can you please suggest on how to solve this?

Explain output shows 378ms but in real, it take around 1 minute to get these data. so when we exclude text column from "ic" table, get result in 4-5 seconds.

"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"
Improve this question
3
  • 1
    Can you include the explain (analyze, buffers) output from your queries? Commented Jun 19, 2019 at 13:42
  • text column for which maximum content size is 12K lines. and, what is the length of these lines ? Commented Jun 19, 2019 at 20:32
  • Thanks @Jeremy for your reply, i have included explain output in original question. Thanks wildplasser, for your reply, sorry i mean 12k length of text data. Commented Jun 20, 2019 at 12:38

1 Answer 1

Reset to default
3

If the explain analyze output is taking 378ms, that is how long the query is taking and there's probably not a lot of room for improvement there. If it's taking 1 minute to transfer and load the data, you need to work on that end.

If you're trying to view very wide rows in psql or pgadmin, it can take some time to calculate the row widths or render the html, but that has nothing to do with query performance.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hello Jeremy, Thanks for your reply. So query execution happens in PGAdmin and execution completes in 1 minutes and if we remove text column then it takes 5 seconds to complete.
@ManishJoisar: pgAdmin (especially pgAdmin 3) is known to be extremely slow with large results
Right, so query execution takes 378ms, but it takes the rest of the minute to transfer and render those huge columns to the screen. If you were to instead output the results directly to disk using psql, you'd see that it's much faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.