pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Michael Shapiro <mshapiro@ncsa.edu>
To: pgsql-performance@postgresql.org
Subject: PgAdmin startup query VERY slow
Date: Tue, 06 Jan 2004 14:49:16 -0600
Message-ID: <5.2.0.9.2.20040106144745.00bcfe30@pop.ncsa.uiuc.edu> (raw )
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
Attachments:
query.txt (text/plain, 247B) download
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
expalin.txt (text/plain, 30.1K) download
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
view thread (5+ messages) latest in thread
Message-ID: <5.2.0.9.2.20040106144745.00bcfe30@pop.ncsa.uiuc.edu>
Permalink: ../5.2.0.9.2.20040106144745.00bcfe30@pop.ncsa.uiuc.edu/
Also on: postgresql.org/message-id/5.2.0.9.2.20040106144745.00bcfe30@pop.ncsa.uiuc.edu
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mshapiro@ncsa.edu
Subject: Re: PgAdmin startup query VERY slow
In-Reply-To: <5.2.0.9.2.20040106144745.00bcfe30@pop.ncsa.uiuc.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox