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

plpgsql plan cache

From: Joel Jacobson <joel(at)gluefinance(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: plpgsql plan cache
Date: 2010-02-22 19:26:49
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

I am trying to make a select query in my plpgsql function to use an  
index allowing an index scan instead of a seq scan.

When running the query in the sql prompt,  it works fine, but  
apparently the index is not used for the same query in the plpgsql  

The problem is not the data types of the parameters to the function or  
the query, they are identical.

When I tried using EXECUTE in the plpgsql function, the index is being  

I thought the query planner must have made a bad decision when I  
created the function the first time.

I therefore tried to drop the function, disconnect from the sql  
client, reconnect (to get a new session), create the function again.
The function still runs slow though.

I cannot understand why the index is not being used when in the  
plpgsql function?
I even tried to make a test function containing nothing more than the  
single query. Still the index is not being used.
When running the same query in the sql prompt, the index is in use  

Is there a way to someone clear the entire query cache or even better  
for a particular plpgsql function?

I'm greatful for any ideas.

Best regards,

Joel Jacobson


pgsql-performance by date

Next:From: Pierre CDate: 2010-02-22 19:42:44
Subject: Re: plpgsql plan cache
Previous:From: Ron MayerDate: 2010-02-22 18:00:34
Subject: Re: SSD + RAID

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