pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
constant vs function param differs in performance
3+ messages / 2 participants
[nested] [flat]

* constant vs function param differs in performance
@ 2003-11-14 15:08 SZŰCS Gábor <surrano@mailbox.hu>
  2003-11-14 20:59 ` Re: constant vs function param differs in performance Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: SZŰCS Gábor @ 2003-11-14 15:08 UTC (permalink / raw)
  To: pgsql-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 -------------------------------




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: constant vs function param differs in performance
  2003-11-14 15:08 constant vs function param differs in performance SZŰCS Gábor <surrano@mailbox.hu>
@ 2003-11-14 20:59 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-11-19 16:04   ` Re: constant vs function param differs in performance SZŰCS Gábor <surrano@mailbox.hu>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2003-11-14 20:59 UTC (permalink / raw)
  To: SZŰCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-performance

"=?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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: constant vs function param differs in performance
  2003-11-14 15:08 constant vs function param differs in performance SZŰCS Gábor <surrano@mailbox.hu>
  2003-11-14 20:59 ` Re: constant vs function param differs in performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-11-19 16:04   ` SZŰCS Gábor <surrano@mailbox.hu>
  0 siblings, 0 replies; 3+ messages in thread

From: SZŰCS Gábor @ 2003-11-19 16:04 UTC (permalink / raw)
  To: pgsql-performance

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
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-11-19 16:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-14 15:08 constant vs function param differs in performance SZŰCS Gábor <surrano@mailbox.hu>
2003-11-14 20:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-19 16:04   ` SZŰCS Gábor <surrano@mailbox.hu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox