The planner has no idea what $1 and $2 are when it plans the query, so that could easily explain why the performance is different. You can prepare statements in psql (at least in 8.1), which would be a good way to verify that theory (compare EXPLAIN for prepared vs. non).
On Thu, Oct 26, 2006 at 09:21:37AM -0700, Matthew Peters wrote: > Parameterized. > > IE (very simplified) > > CREATE OR REPLACE FUNCTION my_function(IN param1 BIGINT, IN param2 > INTEGER) > RETURNS my_type > SECURITY DEFINER > AS > $$ > /* my_type = (a,b,c) */ > Select a,b,c > FROM my_table > WHERE indexed_column = $1 > AND partition_constraint_column = $2; > $$ > LANGUAGE SQL; > > > > > Matthew A. Peters > Sr. Software Engineer, Haydrian Corp. > [EMAIL PROTECTED] > (mobile) 425-941-6566 > Haydrian Corp. > -----Original Message----- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 26, 2006 9:15 AM > To: Matthew Peters > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Stored procedure slower than sql? > Importance: High > > "Matthew Peters" <[EMAIL PROTECTED]> writes: > > How can a stored procedure containing a single query not implement the > > same execution plan (assumption based on the dramatic performance > > difference) that an identical ad-hoc query generates? > > Parameterized vs non parameterized query? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
