Force another plan. - Mailing list pgsql-performance

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
  fredrik.olsson@treyst.se