To: "Olivier Hubaut" <olivier@scmbb.ulb.ac.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: Is it normal that functions are so much faster than inline
	queries
In-reply-to: <opr5qac8iq94ope3@olivier.amaze.ulb.ac.be> 
References: <opr5qac8iq94ope3@olivier.amaze.ulb.ac.be>
Comments: In-reply-to "Olivier Hubaut" <olivier@scmbb.ulb.ac.be>
	message dated "Wed, 31 Mar 2004 15:28:10 +0300"
Date: Wed, 31 Mar 2004 10:33:20 -0500
Message-ID: <5019.1080747200@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Olivier Hubaut" <olivier@scmbb.ulb.ac.be> writes:
> When I want to execute this set of queries in a function:
> ...
> It takes only 2 seconds.

> But when I tried to do it directly in the psql term (replacing the $1  
> value with the same used in the function call), I'm obliged to kill the  
> second query after 10 minutes because it's still runnning!

You're presumably getting different plans in the two cases.  Usually
we hear complaints about the function case being slower, because the
planner has less information when it has to work with a parameter
instead of a constant.  In this case it seems the stupider plan is being
chosen with a constant :-(.  You have not shown enough information to
tell why, but I'm wondering about datatype mismatch preventing an index
from being used.  What is the declared datatype of the $1 parameter, and
does it match what will be assumed for the unadorned constant?

			regards, tom lane