Обсуждение: 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šė: > 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šė: > 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 ------------------------------------------------------------------