Skip site navigation (1) Skip section navigation (2)

Re: Is it normal that functions are so much faster than inline queries

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Olivier Hubaut" <olivier(at)scmbb(dot)ulb(dot)ac(dot)be>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Is it normal that functions are so much faster than inline queries
Date: 2004-03-31 15:33:20
Message-ID: 5019.1080747200@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
"Olivier Hubaut" <olivier(at)scmbb(dot)ulb(dot)ac(dot)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

In response to

Responses

pgsql-sql by date

Next:From: KyleDate: 2004-03-31 18:06:28
Subject: Simple insert not returning
Previous:From: Olivier HubautDate: 2004-03-31 12:28:10
Subject: Is it normal that functions are so much faster than inline queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group