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
