Thread: constant vs function param differs in performance

Dear Gurus,

I have two SQL function that produce different times and I can't understand
why. Here is the basic difference between them:

CREATE FUNCTION test_const_1234 () RETURNS int4 AS '
  SELECT ... 1234 ... 1234 .... 1234 ...
' LANGUAGE 'SQL';

CREATE FUNCTION test_param (int4) RETURNS int4 AS '
  SELECT ... $1 .... $1 .... $1 ...
' LANGUAGE 'SQL';

Some sample times for different data:

test_const_1234()     450 msec
test_param(1234)     2700-4000 msec (probably disk cache)
test_const_5678()   13500 msec
test_param(5678)    14500 msec

Is there a sane explanation? a solution?
I can send more info if you wish.

TIA,
G.
------------------------------- cut here -------------------------------

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> I have two SQL function that produce different times and I can't understand
> why.

The planner often produces different plans when there are constants in
WHERE clauses than when there are variables, because it can get more
accurate ideas of how many rows will be retrieved.

            regards, tom lane

Dear Tom,

Thanks for your early response.

An addition: the nastier difference increased by adding an index (it was an
essential index for this query):

  func with param improved from 2700ms to 2300ms
  func with constant improved from 400ms to 31ms
  inline query improved from 390ms to 2ms

So am I reading correct and it is completely normal and can't be helped?
(couldn't have tried 7.4 yet)

In case it reveals something:

------------------------------- cut here -------------------------------
SELECT field FROM
(SELECT field, sum(something)=0 AS boolvalue
 FROM
 (SELECT * FROM subselect1 NATURAL LEFT JOIN subselect2
  UNION
  SELECT * FROM subselect3 NATURAL LEFT JOIN subselect4
 ) AS u
 GROUP BY field) AS t
WHERE not boolvalue
ORDER BY simple_sql_func_returns_bool(field) DESC
LIMIT 1;
------------------------------- cut here -------------------------------

G.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Friday, November 14, 2003 9:59 PM


> "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> > I have two SQL function that produce different times and I can't
understand
> > why.
>
> The planner often produces different plans when there are constants in
> WHERE clauses than when there are variables, because it can get more
> accurate ideas of how many rows will be retrieved.
>
> regards, tom lane
>