Michael Shapiro wrote:
At 10:12 PM 12/30/2003 +0100, Andreas Pflug wrote:Try EXPLAIN on that query.
Michael,
Michael Shapiro wrote:
The only clue that I might be able to offer you is that we have dropped and recreated the schemas in the database many many times without dropping and recreating the database. Might that cause this query to run slowly?
Yes, that's why I mentioned VACUUMing.
Regards, Andreas
However, vacuuming did not speed this up. So the question remains.. why so slow on the one database while al the others are fast.
Regards, Andreas
---
Michael
QUERY PLAN
{ AGG
:startup_cost 183.16
:total_cost 183.16
: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.14
:total_cost 183.16
:rows 1
:width 22
:qptargetlist <>
:qpqual <>
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 1
:subplan
{ AGG
:startup_cost 183.14
:total_cost 183.16
: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.14
:total_cost 183.15
: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.14
:total_cost 183.15
: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.13
: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.12
: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.02
:rows 2802
: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.16..183.16 rows=1 width=22) (actual time=48397.87..48397.88
rows=1 loops=1)
-> Subquery Scan foo (cost=183.14..183.16 rows=1 width=22) (actual
time=48397.85..48397.85 rows=0 loops=1)
-> Aggregate (cost=183.14..183.16 rows=1 width=22) (actual
time=48397.84..48397.84 rows=0 loops=1)
Filter: (count(1) = 3)
-> Group (cost=183.14..183.15 rows=2 width=22) (actual
time=48397.45..48397.67 rows=15 loops=1)
-> Sort (cost=183.14..183.15 rows=2 width=22) (actual
time=48397.43..48397.45 rows=15 loops=1)
Sort Key: tr.tgargs
-> Hash Join (cost=0.00..183.13 rows=2 width=22) (actual
time=160.37..48396.34 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.12 rows=2 width=17)
(actual time=152.98..48376.94 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..15.85 rows=321 loops=1)
-> Seq Scan on pg_depend dep (cost=0.00..49.02
rows=2802 width=9) (actual time=0.08..101.47 rows=4570 loops=321)
-> Hash (cost=0.00..0.00 rows=1 width=5) (actual
time=6.94..6.94 rows=0 loops=1)
-> Seq Scan on pg_constraint co
(cost=0.00..0.00 rows=1 width=5) (actual time=1.92..5.42 rows=183 loops=1)
Total runtime: 48401.96 msec
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
