Skip site navigation (1) Skip section navigation (2)

Force another plan.

From: Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)se>
To: pgsql-performance(at)postgresql(dot)org
Subject: Force another plan.
Date: 2006-02-18 16:04:34
Message-ID: 43F74592.7010701@treyst.se (view raw or flat)
Thread:
Lists: 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(dot)olsson(at)treyst(dot)se


Responses

pgsql-performance by date

Next:From: RonDate: 2006-02-18 17:01:10
Subject: Re: qsort again (was Re: [PERFORM] Strange Create
Previous:From: Adam AlkinsDate: 2006-02-18 07:39:19
Subject: Re: Index Choice Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group