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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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