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
