SQL plan in functions
Hello, list.
I have a function witch returns record set. What troubles me is that
depending on parameters I pass to this function the execution time
varies greatly. On the other hand if I execute the query (not the
function) with pgAdmin - it gives results quickly.
In previous post You helped me realize, that the problem was because the
function has only one plan of SQL inside no matter the parameters
values. Is there a way to order postgres to check the plan each time the
function is called?
--
Julius Tuskenis
On Thu, Dec 18, 2008 at 9:18 AM, Julius Tuskenis
<julius.tuskenis@gmail.com> wrote:
Hello, list.
I have a function witch returns record set. What troubles me is that
depending on parameters I pass to this function the execution time varies
greatly. On the other hand if I execute the query (not the function) with
pgAdmin - it gives results quickly.
In previous post You helped me realize, that the problem was because the
function has only one plan of SQL inside no matter the parameters values. Is
there a way to order postgres to check the plan each time the function is
called?
use EXECUTE 'query' .
--
GJ
In response to Julius Tuskenis :
Hello, list.
I have a function witch returns record set. What troubles me is that
depending on parameters I pass to this function the execution time
varies greatly. On the other hand if I execute the query (not the
function) with pgAdmin - it gives results quickly.
In previous post You helped me realize, that the problem was because the
function has only one plan of SQL inside no matter the parameters
values. Is there a way to order postgres to check the plan each time the
function is called?
Sure, use EXECUTE 'your sql' inside the function to force the planner to
generate a new plan depending on the current parameters.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Thank you Andreas and Grzegorz. It worked!
While experimenting I found that if I wright FOR rec IN EXECUTE 'my sql'
LOOP ...... its OK, but If I wright RETURN QUERY EXECUTE 'my sql' I get
syntax error. Is it a bug?
Julius Tuskenis
A. Kretschmer rašė:
Show quoted text
In response to Julius Tuskenis :
Hello, list.
I have a function witch returns record set. What troubles me is that
depending on parameters I pass to this function the execution time
varies greatly. On the other hand if I execute the query (not the
function) with pgAdmin - it gives results quickly.
In previous post You helped me realize, that the problem was because the
function has only one plan of SQL inside no matter the parameters
values. Is there a way to order postgres to check the plan each time the
function is called?Sure, use EXECUTE 'your sql' inside the function to force the planner to
generate a new plan depending on the current parameters.Regards, Andreas
On 18/12/2008 12:12, Julius Tuskenis wrote:
While experimenting I found that if I wright FOR rec IN EXECUTE 'my sql'
LOOP ...... its OK, but If I wright RETURN QUERY EXECUTE 'my sql' I get
syntax error. Is it a bug?
No, it's a syntax error. :-)
You need to do something like this to return the rows from the query:
FOR rec in EXECUTE 'your sql here'
LOOP
RETURN NEXT rec;
END LOOP;
RETURN; -- exits from the function.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
Yes, Raymond - I know how to return record set in function using FOR,
but since version 8.3 there is a option - using RETURN QUERY SELECT
something FROM somewhere;. So if it works for SELECT why should it not
work for EXECUTE ?
Julius Tuskenis
Raymond O'Donnell rašė:
Show quoted text
On 18/12/2008 12:12, Julius Tuskenis wrote:
While experimenting I found that if I wright FOR rec IN EXECUTE 'my sql'
LOOP ...... its OK, but If I wright RETURN QUERY EXECUTE 'my sql' I get
syntax error. Is it a bug?No, it's a syntax error. :-)
You need to do something like this to return the rows from the query:
FOR rec in EXECUTE 'your sql here'
LOOP
RETURN NEXT rec;
END LOOP;RETURN; -- exits from the function.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
On 18/12/2008 12:40, Julius Tuskenis wrote:
Yes, Raymond - I know how to return record set in function using FOR,
but since version 8.3 there is a option - using RETURN QUERY SELECT
something FROM somewhere;. So if it works for SELECT why should it not
work for EXECUTE ?
Oh - I didn't know about that....so I'm afraid I don't know why it's
causing a problem for you.... :-)
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------