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: 38E2265E-B38E-4EBD-BB87-23B46EF4FD1C@gluefinance.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

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  
function.

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  
used.

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  
though.

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


Responses

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-2014 The PostgreSQL Global Development Group