I have some quite huge queries, inside functions, so debugging is kind
of hard. But I have located the query that for some reason gets 4 times
as slow after an analyze.
Before analyze the plan for the query is this:
Nested Loop (cost=16.80..34.33 rows=1 width=28)
Join Filter: (ischildof(2, "outer".calendar) OR (hashed subplan))
-> Nested Loop (cost=0.00..11.66 rows=1 width=32)
-> Index Scan using t_events_eventype on t_events e
(cost=0.00..5.82 rows=1 width=28)
Index Cond: (eventtype = 1)
Filter: (rrfreq IS NOT NULL)
-> Index Scan using t_entities_pkey on t_entities te
(cost=0.00..5.83 rows=1 width=4)
Index Cond: (te."ID" = "outer".entity)
Filter: (partof = 'events'::name)
-> Index Scan using t_entities_pkey on t_entities (cost=0.00..5.85
rows=1 width=4)
Index Cond: (t_entities."ID" = "outer".entity)
Filter: ((haveaccess(createdby, responsible, "class", false) OR
CASE WHEN (partof = 'contacts'::name) THEN ischildof(ancestorof(me()),
"ID") ELSE false END) AND (subplan))
SubPlan
-> Function Scan on alleventoccurances (cost=0.00..12.50
rows=1000 width=8)
SubPlan
-> Seq Scan on t_attendees (cost=0.00..16.38 rows=170 width=4)
Filter: ischildof(2, contact)
In reality this takes approximately 1.0s in the general case. After an
analyze the plan becomes:
Nested Loop (cost=2.09..4.82 rows=1 width=28)
Join Filter: ("inner"."ID" = "outer"."ID")
-> Hash Join (cost=2.09..3.59 rows=1 width=32)
Hash Cond: ("outer"."ID" = "inner".entity)
Join Filter: (ischildof(2, "inner".calendar) OR (hashed subplan))
-> Seq Scan on t_entities (cost=0.00..1.46 rows=6 width=4)
Filter: ((haveaccess(createdby, responsible, "class",
false) OR CASE WHEN (partof = 'contacts'::name) THEN
ischildof(ancestorof(me()), "ID") ELSE false END) AND (subplan))
SubPlan
-> Function Scan on alleventoccurances
(cost=0.00..12.50 rows=1000 width=8)
-> Hash (cost=1.06..1.06 rows=2 width=28)
-> Seq Scan on t_events e (cost=0.00..1.06 rows=2 width=28)
Filter: ((rrfreq IS NOT NULL) AND (eventtype = 1))
SubPlan
-> Seq Scan on t_attendees (cost=0.00..1.02 rows=1 width=4)
Filter: ischildof(2, contact)
-> Seq Scan on t_entities te (cost=0.00..1.16 rows=5 width=4)
Filter: (partof = 'events'::name)
This takes on approximately 4.5s. So obviously it has degraded.
I count myself as a newbie here, so any hints on what goes on, why a
plan might be chosen, and how I can make is better is appreciated.
Naturally the I can provide scripts to set up all or parts of the
database if anyone like.
regards
--
//Fredrik Olsson
Treyst AB
+46-(0)19-362182
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
