Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Pgsql Performance > Re: SQL Functio...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 5 of 17 Topic 3994 of 4352
Post > Topic >>

Re: SQL Function Slowness, 8.3.0

by tgl@[EMAIL PROTECTED] (Tom Lane) Apr 16, 2008 at 04:24 PM

"Gavin M. Roy" <gmr@[EMAIL PROTECTED]
> writes:
> After detailed examination of pg_stat_user_indexes usage, it's clear
that
> the functions don't use the same indexes.  I've casted everything to
match
> the indexes in the SQL function, to no success.  Any suggestions on next
> steps?  Maybe for 8.4 we could find a way to explain analyze function
> internals ;-)

Yeah, this could be easier, but it's certainly possible to examine the
plan generated for a function's parameterized statement.  For instance,
say you're wondering about the plan for

	create function foo(int, text) ... as
	$$ select * from bar where f1 = $1 and f2 = $2 $$
	language sql

What you do is

prepare p(int, text) as select * from bar where f1 = $1 and f2 = $2 ;

explain analyze execute p(42, 'hello world');

It works exactly the same for statements in plpgsql functions,
remembering that both parameters and local variables of the function
have to become $n placeholders.  Remember to make the parameters
of the prepared statement have the same declared types as the
function's parameters and variables.

			regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
 




 17 Posts in Topic:
SQL Function Slowness, 8.3.0
gmr@[EMAIL PROTECTED] (&  2008-04-16 11:06:35 
Re: SQL Function Slowness, 8.3.0
tgl@[EMAIL PROTECTED] (T  2008-04-16 11:09:47 
Re: SQL Function Slowness, 8.3.0
mark@[EMAIL PROTECTED] (  2008-04-16 11:14:26 
Re: SQL Function Slowness, 8.3.0
gmr@[EMAIL PROTECTED] (&  2008-04-16 14:44:40 
Re: SQL Function Slowness, 8.3.0
tgl@[EMAIL PROTECTED] (T  2008-04-16 16:24:32 
Re: SQL Function Slowness, 8.3.0
jd@[EMAIL PROTECTED] (&q  2008-04-16 11:58:08 
Re: SQL Function Slowness, 8.3.0
simon@[EMAIL PROTECTED]   2008-04-17 17:12:31 
Re: SQL Function Slowness, 8.3.0
tgl@[EMAIL PROTECTED] (T  2008-04-17 12:12:50 
Re: SQL Function Slowness, 8.3.0
simon@[EMAIL PROTECTED]   2008-04-17 17:25:57 
Re: SQL Function Slowness, 8.3.0
tgl@[EMAIL PROTECTED] (T  2008-04-17 12:41:12 
Re: SQL Function Slowness, 8.3.0
craig@[EMAIL PROTECTED]   2008-04-18 01:00:43 
Re: SQL Function Slowness, 8.3.0
alvherre@[EMAIL PROTECTED  2008-04-17 13:15:40 
Re: SQL Function Slowness, 8.3.0
tgl@[EMAIL PROTECTED] (T  2008-04-17 19:38:54 
Re: SQL Function Slowness, 8.3.0
craig@[EMAIL PROTECTED]   2008-04-18 11:41:17 
Re: SQL Function Slowness, 8.3.0
tgl@[EMAIL PROTECTED] (T  2008-04-18 00:01:52 
Re: SQL Function Slowness, 8.3.0
craig@[EMAIL PROTECTED]   2008-04-18 12:21:46 
Re: SQL Function Slowness, 8.3.0
simon@[EMAIL PROTECTED]   2008-04-17 17:50:55 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Sun Oct 12 9:39:53 CDT 2008.