I have reported this on the pgadmin-support mailing list, but Andreas Pflug has asked me to post it here.

With a particular database, PgAdmin3 takes a very long time to connect to a database. this is not a general problem with PgAdmin, but only with one database out of many. Other databases do not have the problem. And only with one particular server. The exact same database on a different server does not have the problem.

The server in question is running PostgreSQL 7.3.2 on sparc-sun-solaris2.8, compiled by GCC 2.95.2

The other server which has the same database is running Postgres 7.3.4 on i386-redhat-linux-gnu, complied by GCC i386-redhat-linux-gcc 3.2.2.

I have attached the query that Andreas says is the one that is run when PgAdmin first connects to a database as well as the output from running the query with explain turned on.

Both Andreas and I would be every interested if this group might have any ideas why the query is so slow.

NOTE: I have vacuumed the database, but that did not affect the timing at all.
NOTE: The startup on the sparc server is 44 seconds, The startup on the linux server is 5 seconds.


Andreas writes:
I can't see too much from this query plan, it just seems you have 321 triggers an 4750 dependencies which isn't too extraordinary much. But 48 seconds execution time *is* much.


Please repost this to pgsql-performance, including the query, backend version, and modified server settings. I'm not deep enough in planner items to analyze this sufficiently.
Please let me CCd on this topic so I can see what I should change in pgAdmin3 (if any).




---
Michael


---
Michael
SELECT COUNT(*) FROM
(SELECT tgargs from pg_trigger tr
LEFT JOIN pg_depend dep ON dep.objid=tr.oid AND deptype = 'i'
LEFT JOIN pg_constraint co ON refobjid = co.oid AND contype = 'f'
WHERE co.oid IS NULL
GROUP BY tgargs
HAVING count(1) = 3) AS foo
QUERY PLAN
   { AGG 
   :startup_cost 183.36 
   :total_cost 183.36 
   :rows 1 
   :width 22 
   :qptargetlist (
      { TARGETENTRY 
      :resdom 
         { RESDOM 
         :resno 1 
         :restype 20 
         :restypmod -1 
         :resname count 
         :reskey 0 
         :reskeyop 0 
         :ressortgroupref 0 
         :resjunk false 
         }
       
      :expr 
         { AGGREG 
         :aggfnoid 2147 
         :aggtype 20 
         :target 
            { CONST 
            :consttype 23 
            :constlen 4 
            :constbyval true 
            :constisnull false 
            :constvalue  4 [ 0 0 0 1 ] 
            }
          
         :aggstar true 
         :aggdistinct false 
         }
      }
   )
    
   :qpqual <> 
   :lefttree 
      { SUBQUERYSCAN 
      :startup_cost 183.34 
      :total_cost 183.36 
      :rows 1 
      :width 22 
      :qptargetlist <> 
      :qpqual <> 
      :lefttree <> 
      :righttree <> 
      :extprm ()
       
      :locprm ()
       
      :initplan <> 
      :nprm 0  
      :scanrelid 1 
      :subplan 
         { AGG 
         :startup_cost 183.34 
         :total_cost 183.36 
         :rows 1 
         :width 22 
         :qptargetlist (
            { TARGETENTRY 
            :resdom 
               { RESDOM 
               :resno 1 
               :restype 17 
               :restypmod -1 
               :resname tgargs 
               :reskey 0 
               :reskeyop 0 
               :ressortgroupref 1 
               :resjunk false 
               }
             
            :expr 
               { VAR 
               :varno 0 
               :varattno 1 
               :vartype 17 
               :vartypmod -1  
               :varlevelsup 0 
               :varnoold 1 
               :varoattno 13
               }
            }
         )
          
         :qpqual (
            { EXPR 
            :typeOid 16  
            :opType op 
            :oper 
               { OPER 
               :opno 416 
               :opid 474 
               :opresulttype 16 
               :opretset false 
               }
             
            :args (
               { AGGREG 
               :aggfnoid 2147 
               :aggtype 20 
               :target 
                  { CONST 
                  :consttype 23 
                  :constlen 4 
                  :constbyval true 
                  :constisnull false 
                  :constvalue  4 [ 0 0 0 1 ] 
                  }
                
               :aggstar false 
               :aggdistinct false 
               }
             
               { CONST 
               :consttype 23 
               :constlen 4 
               :constbyval true 
               :constisnull false 
               :constvalue  4 [ 0 0 0 3 ] 
               }
            )
            }
         )
          
         :lefttree 
            { GRP 
            :startup_cost 183.34 
            :total_cost 183.35 
            :rows 2 
            :width 22 
            :qptargetlist (
               { TARGETENTRY 
               :resdom 
                  { RESDOM 
                  :resno 1 
                  :restype 17 
                  :restypmod -1 
                  :resname <> 
                  :reskey 0 
                  :reskeyop 0 
                  :ressortgroupref 0 
                  :resjunk false 
                  }
                
               :expr 
                  { VAR 
                  :varno 0 
                  :varattno 1 
                  :vartype 17 
                  :vartypmod -1  
                  :varlevelsup 0 
                  :varnoold 1 
                  :varoattno 13
                  }
               }
            )
             
            :qpqual <> 
            :lefttree 
               { SORT 
               :startup_cost 183.34 
               :total_cost 183.35 
               :rows 2 
               :width 22 
               :qptargetlist (
                  { TARGETENTRY 
                  :resdom 
                     { RESDOM 
                     :resno 1 
                     :restype 17 
                     :restypmod -1 
                     :resname <> 
                     :reskey 1 
                     :reskeyop 1957 
                     :ressortgroupref 0 
                     :resjunk false 
                     }
                   
                  :expr 
                     { VAR 
                     :varno 1 
                     :varattno 13 
                     :vartype 17 
                     :vartypmod -1  
                     :varlevelsup 0 
                     :varnoold 1 
                     :varoattno 13
                     }
                  }
               )
                
               :qpqual <> 
               :lefttree 
                  { HASHJOIN 
                  :startup_cost 0.00 
                  :total_cost 183.33 
                  :rows 2 
                  :width 22 
                  :qptargetlist (
                     { TARGETENTRY 
                     :resdom 
                        { RESDOM 
                        :resno 1 
                        :restype 17 
                        :restypmod -1 
                        :resname <> 
                        :reskey 0 
                        :reskeyop 0 
                        :ressortgroupref 0 
                        :resjunk false 
                        }
                      
                     :expr 
                        { VAR 
                        :varno 65001 
                        :varattno 1 
                        :vartype 17 
                        :vartypmod -1  
                        :varlevelsup 0 
                        :varnoold 1 
                        :varoattno 13
                        }
                     }
                  )
                   
                  :qpqual (
                     { NULLTEST 
                     :arg 
                        { VAR 
                        :varno 65000 
                        :varattno 1 
                        :vartype 26 
                        :vartypmod -1  
                        :varlevelsup 0 
                        :varnoold 4 
                        :varoattno -2
                        }
                      
                     :nulltesttype 0 
                     }
                  )
                   
                  :lefttree 
                     { NESTLOOP 
                     :startup_cost 0.00 
                     :total_cost 183.32 
                     :rows 2 
                     :width 17 
                     :qptargetlist (
                        { TARGETENTRY 
                        :resdom 
                           { RESDOM 
                           :resno 1 
                           :restype 17 
                           :restypmod -1 
                           :resname <> 
                           :reskey 0 
                           :reskeyop 0 
                           :ressortgroupref 0 
                           :resjunk false 
                           }
                         
                        :expr 
                           { VAR 
                           :varno 65001 
                           :varattno 1 
                           :vartype 17 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 1 
                           :varoattno 13
                           }
                        }
                      
                        { TARGETENTRY 
                        :resdom 
                           { RESDOM 
                           :resno 2 
                           :restype 26 
                           :restypmod -1 
                           :resname <> 
                           :reskey 0 
                           :reskeyop 0 
                           :ressortgroupref 0 
                           :resjunk false 
                           }
                         
                        :expr 
                           { VAR 
                           :varno 65001 
                           :varattno 2 
                           :vartype 26 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 1 
                           :varoattno -2
                           }
                        }
                      
                        { TARGETENTRY 
                        :resdom 
                           { RESDOM 
                           :resno 3 
                           :restype 26 
                           :restypmod -1 
                           :resname <> 
                           :reskey 0 
                           :reskeyop 0 
                           :ressortgroupref 0 
                           :resjunk false 
                           }
                         
                        :expr 
                           { VAR 
                           :varno 65000 
                           :varattno 1 
                           :vartype 26 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 2 
                           :varoattno 2
                           }
                        }
                      
                        { TARGETENTRY 
                        :resdom 
                           { RESDOM 
                           :resno 4 
                           :restype 18 
                           :restypmod -1 
                           :resname <> 
                           :reskey 0 
                           :reskeyop 0 
                           :ressortgroupref 0 
                           :resjunk false 
                           }
                         
                        :expr 
                           { VAR 
                           :varno 65000 
                           :varattno 2 
                           :vartype 18 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 2 
                           :varoattno 7
                           }
                        }
                      
                        { TARGETENTRY 
                        :resdom 
                           { RESDOM 
                           :resno 5 
                           :restype 26 
                           :restypmod -1 
                           :resname <> 
                           :reskey 0 
                           :reskeyop 0 
                           :ressortgroupref 0 
                           :resjunk false 
                           }
                         
                        :expr 
                           { VAR 
                           :varno 65000 
                           :varattno 3 
                           :vartype 26 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 2 
                           :varoattno 5
                           }
                        }
                     )
                      
                     :qpqual <> 
                     :lefttree 
                        { SEQSCAN 
                        :startup_cost 0.00 
                        :total_cost 1.02 
                        :rows 2 
                        :width 8 
                        :qptargetlist (
                           { TARGETENTRY 
                           :resdom 
                              { RESDOM 
                              :resno 1 
                              :restype 17 
                              :restypmod -1 
                              :resname <> 
                              :reskey 0 
                              :reskeyop 0 
                              :ressortgroupref 0 
                              :resjunk false 
                              }
                            
                           :expr 
                              { VAR 
                              :varno 1 
                              :varattno 13 
                              :vartype 17 
                              :vartypmod -1  
                              :varlevelsup 0 
                              :varnoold 1 
                              :varoattno 13
                              }
                           }
                         
                           { TARGETENTRY 
                           :resdom 
                              { RESDOM 
                              :resno 2 
                              :restype 26 
                              :restypmod -1 
                              :resname <> 
                              :reskey 0 
                              :reskeyop 0 
                              :ressortgroupref 0 
                              :resjunk false 
                              }
                            
                           :expr 
                              { VAR 
                              :varno 1 
                              :varattno -2 
                              :vartype 26 
                              :vartypmod -1  
                              :varlevelsup 0 
                              :varnoold 1 
                              :varoattno -2
                              }
                           }
                        )
                         
                        :qpqual <> 
                        :lefttree <> 
                        :righttree <> 
                        :extprm ()
                         
                        :locprm ()
                         
                        :initplan <> 
                        :nprm 0  
                        :scanrelid 1 
                        }
                      
                     :righttree 
                        { SEQSCAN 
                        :startup_cost 0.00 
                        :total_cost 49.06 
                        :rows 2806 
                        :width 9 
                        :qptargetlist (
                           { TARGETENTRY 
                           :resdom 
                              { RESDOM 
                              :resno 1 
                              :restype 26 
                              :restypmod -1 
                              :resname <> 
                              :reskey 0 
                              :reskeyop 0 
                              :ressortgroupref 0 
                              :resjunk false 
                              }
                            
                           :expr 
                              { VAR 
                              :varno 2 
                              :varattno 2 
                              :vartype 26 
                              :vartypmod -1  
                              :varlevelsup 0 
                              :varnoold 2 
                              :varoattno 2
                              }
                           }
                         
                           { TARGETENTRY 
                           :resdom 
                              { RESDOM 
                              :resno 2 
                              :restype 18 
                              :restypmod -1 
                              :resname <> 
                              :reskey 0 
                              :reskeyop 0 
                              :ressortgroupref 0 
                              :resjunk false 
                              }
                            
                           :expr 
                              { VAR 
                              :varno 2 
                              :varattno 7 
                              :vartype 18 
                              :vartypmod -1  
                              :varlevelsup 0 
                              :varnoold 2 
                              :varoattno 7
                              }
                           }
                         
                           { TARGETENTRY 
                           :resdom 
                              { RESDOM 
                              :resno 3 
                              :restype 26 
                              :restypmod -1 
                              :resname <> 
                              :reskey 0 
                              :reskeyop 0 
                              :ressortgroupref 0 
                              :resjunk false 
                              }
                            
                           :expr 
                              { VAR 
                              :varno 2 
                              :varattno 5 
                              :vartype 26 
                              :vartypmod -1  
                              :varlevelsup 0 
                              :varnoold 2 
                              :varoattno 5
                              }
                           }
                        )
                         
                        :qpqual <> 
                        :lefttree <> 
                        :righttree <> 
                        :extprm ()
                         
                        :locprm ()
                         
                        :initplan <> 
                        :nprm 0  
                        :scanrelid 2 
                        }
                      
                     :extprm ()
                      
                     :locprm ()
                      
                     :initplan <> 
                     :nprm 0  
                     :jointype 1 
                     :joinqual (
                        { EXPR 
                        :typeOid 16  
                        :opType op 
                        :oper 
                           { OPER 
                           :opno 607 
                           :opid 184 
                           :opresulttype 16 
                           :opretset false 
                           }
                         
                        :args (
                           { VAR 
                           :varno 65000 
                           :varattno 1 
                           :vartype 26 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 2 
                           :varoattno 2
                           }
                         
                           { VAR 
                           :varno 65001 
                           :varattno 2 
                           :vartype 26 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 1 
                           :varoattno -2
                           }
                        )
                        }
                      
                        { EXPR 
                        :typeOid 16  
                        :opType op 
                        :oper 
                           { OPER 
                           :opno 92 
                           :opid 61 
                           :opresulttype 16 
                           :opretset false 
                           }
                         
                        :args (
                           { VAR 
                           :varno 65000 
                           :varattno 2 
                           :vartype 18 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 2 
                           :varoattno 7
                           }
                         
                           { CONST 
                           :consttype 18 
                           :constlen 1 
                           :constbyval true 
                           :constisnull false 
                           :constvalue  1 [ 0 0 0 105 ] 
                           }
                        )
                        }
                     )
                     }
                   
                  :righttree 
                     { HASH 
                     :startup_cost 0.00 
                     :total_cost 0.00 
                     :rows 1 
                     :width 5 
                     :qptargetlist (
                        { TARGETENTRY 
                        :resdom 
                           { RESDOM 
                           :resno 1 
                           :restype 26 
                           :restypmod -1 
                           :resname <> 
                           :reskey 0 
                           :reskeyop 0 
                           :ressortgroupref 0 
                           :resjunk false 
                           }
                         
                        :expr 
                           { VAR 
                           :varno 4 
                           :varattno -2 
                           :vartype 26 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 4 
                           :varoattno -2
                           }
                        }
                      
                        { TARGETENTRY 
                        :resdom 
                           { RESDOM 
                           :resno 2 
                           :restype 18 
                           :restypmod -1 
                           :resname <> 
                           :reskey 0 
                           :reskeyop 0 
                           :ressortgroupref 0 
                           :resjunk false 
                           }
                         
                        :expr 
                           { VAR 
                           :varno 4 
                           :varattno 3 
                           :vartype 18 
                           :vartypmod -1  
                           :varlevelsup 0 
                           :varnoold 4 
                           :varoattno 3
                           }
                        }
                     )
                      
                     :qpqual <> 
                     :lefttree 
                        { SEQSCAN 
                        :startup_cost 0.00 
                        :total_cost 0.00 
                        :rows 1 
                        :width 5 
                        :qptargetlist (
                           { TARGETENTRY 
                           :resdom 
                              { RESDOM 
                              :resno 1 
                              :restype 26 
                              :restypmod -1 
                              :resname <> 
                              :reskey 0 
                              :reskeyop 0 
                              :ressortgroupref 0 
                              :resjunk false 
                              }
                            
                           :expr 
                              { VAR 
                              :varno 4 
                              :varattno -2 
                              :vartype 26 
                              :vartypmod -1  
                              :varlevelsup 0 
                              :varnoold 4 
                              :varoattno -2
                              }
                           }
                         
                           { TARGETENTRY 
                           :resdom 
                              { RESDOM 
                              :resno 2 
                              :restype 18 
                              :restypmod -1 
                              :resname <> 
                              :reskey 0 
                              :reskeyop 0 
                              :ressortgroupref 0 
                              :resjunk false 
                              }
                            
                           :expr 
                              { VAR 
                              :varno 4 
                              :varattno 3 
                              :vartype 18 
                              :vartypmod -1  
                              :varlevelsup 0 
                              :varnoold 4 
                              :varoattno 3
                              }
                           }
                        )
                         
                        :qpqual <> 
                        :lefttree <> 
                        :righttree <> 
                        :extprm ()
                         
                        :locprm ()
                         
                        :initplan <> 
                        :nprm 0  
                        :scanrelid 4 
                        }
                      
                     :righttree <> 
                     :extprm ()
                      
                     :locprm ()
                      
                     :initplan <> 
                     :nprm 0  
                     :hashkey 
                        { VAR 
                        :varno 65000 
                        :varattno 1 
                        :vartype 26 
                        :vartypmod -1  
                        :varlevelsup 0 
                        :varnoold 4 
                        :varoattno -2
                        }
                     }
                   
                  :extprm ()
                   
                  :locprm ()
                   
                  :initplan <> 
                  :nprm 0  
                  :jointype 1 
                  :joinqual (
                     { EXPR 
                     :typeOid 16  
                     :opType op 
                     :oper 
                        { OPER 
                        :opno 92 
                        :opid 61 
                        :opresulttype 16 
                        :opretset false 
                        }
                      
                     :args (
                        { VAR 
                        :varno 65000 
                        :varattno 2 
                        :vartype 18 
                        :vartypmod -1  
                        :varlevelsup 0 
                        :varnoold 4 
                        :varoattno 3
                        }
                      
                        { CONST 
                        :consttype 18 
                        :constlen 1 
                        :constbyval true 
                        :constisnull false 
                        :constvalue  1 [ 0 0 0 102 ] 
                        }
                     )
                     }
                  )
                   
                  :hashclauses (
                     { EXPR 
                     :typeOid 16  
                     :opType op 
                     :oper 
                        { OPER 
                        :opno 607 
                        :opid 184 
                        :opresulttype 16 
                        :opretset false 
                        }
                      
                     :args (
                        { VAR 
                        :varno 65001 
                        :varattno 5 
                        :vartype 26 
                        :vartypmod -1  
                        :varlevelsup 0 
                        :varnoold 2 
                        :varoattno 5
                        }
                      
                        { VAR 
                        :varno 65000 
                        :varattno 1 
                        :vartype 26 
                        :vartypmod -1  
                        :varlevelsup 0 
                        :varnoold 4 
                        :varoattno -2
                        }
                     )
                     }
                  )
                   
                  :hashjoinop 0 
                  }
                
               :righttree <> 
               :extprm ()
                
               :locprm ()
                
               :initplan <> 
               :nprm 0  
               :keycount 1 
               }
             
            :righttree <> 
            :extprm ()
             
            :locprm ()
             
            :initplan <> 
            :nprm 0  
            :numCols 1 
            :tuplePerGroup true 
            }
          
         :righttree <> 
         :extprm ()
          
         :locprm ()
          
         :initplan <> 
         :nprm 0 
         }
      }
    
   :righttree <> 
   :extprm ()
    
   :locprm ()
    
   :initplan <> 
   :nprm 0 
   }

Aggregate  (cost=183.36..183.36 rows=1 width=22) (actual time=43589.73..43589.73 
rows=1 loops=1)
  ->  Subquery Scan foo  (cost=183.34..183.36 rows=1 width=22) (actual 
time=43589.71..43589.71 rows=0 loops=1)
        ->  Aggregate  (cost=183.34..183.36 rows=1 width=22) (actual 
time=43589.70..43589.70 rows=0 loops=1)
              Filter: (count(1) = 3)
              ->  Group  (cost=183.34..183.35 rows=2 width=22) (actual 
time=43589.32..43589.53 rows=15 loops=1)
                    ->  Sort  (cost=183.34..183.35 rows=2 width=22) (actual 
time=43589.31..43589.32 rows=15 loops=1)
                          Sort Key: tr.tgargs
                          ->  Hash Join  (cost=0.00..183.33 rows=2 width=22) (actual 
time=143.53..43588.22 rows=15 loops=1)
                                Hash Cond: ("outer".refobjid = "inner".oid)
                                Join Filter: ("inner".contype = 'f'::"char")
                                Filter: ("inner".oid IS NULL)
                                ->  Nested Loop  (cost=0.00..183.32 rows=2 width=17) 
(actual time=138.17..43573.24 rows=321 loops=1)
                                      Join Filter: (("inner".objid = "outer".oid) AND 
("inner".deptype = 'i'::"char"))
                                      ->  Seq Scan on pg_trigger tr  (cost=0.00..1.02 
rows=2 width=8) (actual time=0.11..10.21 rows=321 loops=1)
                                      ->  Seq Scan on pg_depend dep  (cost=0.00..49.06 
rows=2806 width=9) (actual time=0.03..87.78 rows=4570 loops=321)
                                ->  Hash  (cost=0.00..0.00 rows=1 width=5) (actual 
time=4.89..4.89 rows=0 loops=1)
                                      ->  Seq Scan on pg_constraint co  
(cost=0.00..0.00 rows=1 width=5) (actual time=0.13..3.41 rows=183 loops=1)
Total runtime: 43593.62 msec
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to