pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPgAdmin startup query VERY slow
5+ messages / 3 participants
[nested] [flat]
* PgAdmin startup query VERY slow
@ 2004-01-06 20:49 Michael Shapiro <mshapiro@ncsa.edu>
0 siblings, 2 replies; 5+ messages in thread
From: Michael Shapiro @ 2004-01-06 20:49 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAdmin startup query VERY slow
@ 2004-01-06 21:01 Josh Berkus <josh@agliodbs.com>
parent: Michael Shapiro <mshapiro@ncsa.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2004-01-06 21:01 UTC (permalink / raw)
To: Michael Shapiro <mshapiro@ncsa.edu>; pgsql-performance
Michael,
> 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.
Have you run VACUUM ANALYZE *as the superuser* on the faulty server recently?
From the look of the explain, PG is grossly underestimating the number of
items in the pg_trigger and pg_depend tables, and thus choosing an
inappropriate nested loop execution.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAdmin startup query VERY slow
@ 2004-01-06 21:17 Josh Berkus <josh@agliodbs.com>
parent: Michael Shapiro <mshapiro@ncsa.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2004-01-06 21:17 UTC (permalink / raw)
To: Michael Shapiro <mshapiro@ncsa.edu>; pgsql-performance
Mark,
> That seemed to fix it. What does VACUUM ANALYZE do that VACUUM FULL does
> not? What causes a database to need vacuuming?
See the Online Docs:
http://www.postgresql.org/docs/current/static/maintenance.html
Incidentally, just ANALYZE would probably have fixed your problem. Please do
suggest to the PGAdmin team that they add a FAQ item about this.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAdmin startup query VERY slow
@ 2004-01-06 21:31 Michael Shapiro <mshapiro@ncsa.edu>
parent: Michael Shapiro <mshapiro@ncsa.edu>
1 sibling, 1 reply; 5+ messages in thread
From: Michael Shapiro @ 2004-01-06 21:31 UTC (permalink / raw)
To: pgsql-performance
That seemed to fix it. What does VACUUM ANALYZE do that VACUUM FULL does
not? What causes a database to need vacuuming?
At 01:01 PM 1/6/2004 -0800, Josh Berkus wrote:
>Michael,
>
> > 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.
>
>Have you run VACUUM ANALYZE *as the superuser* on the faulty server
>recently?
> >From the look of the explain, PG is grossly underestimating the number of
>items in the pg_trigger and pg_depend tables, and thus choosing an
>inappropriate nested loop execution.
>
>--
>-Josh Berkus
> Aglio Database Solutions
> San Francisco
---
Michael
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAdmin startup query VERY slow
@ 2004-01-06 23:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-01-06 23:12 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Michael Shapiro <mshapiro@ncsa.edu>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> Incidentally, just ANALYZE would probably have fixed your problem.
... or just VACUUM; that would have updated the row count which is all
that was really needed here. The main point is that you do have to do
that as superuser, since the same commands issued as a non-superuser
won't touch the system tables (or any table you do not own).
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-01-06 23:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-06 20:49 PgAdmin startup query VERY slow Michael Shapiro <mshapiro@ncsa.edu>
2004-01-06 21:01 ` Josh Berkus <josh@agliodbs.com>
2004-01-06 21:31 ` Michael Shapiro <mshapiro@ncsa.edu>
2004-01-06 21:17 ` Josh Berkus <josh@agliodbs.com>
2004-01-06 23:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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