pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: SZŰCS Gábor <surrano@mailbox.hu>
To: pgsql-performance@postgresql.org
Subject: Re: constant vs function param differs in performance
Date: Wed, 19 Nov 2003 17:04:01 +0100
Message-ID: <01e301c3aeb6$bff63350$0403a8c0@fejleszt4> (raw)
References: <01a601c3aac1$281e72c0$0403a8c0@fejleszt4>
	<5187.1068843554@sss.pgh.pa.us>

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
>




view thread (3+ messages)

Message-ID: <01e301c3aeb6$bff63350$0403a8c0@fejleszt4>
Permalink:  ../01e301c3aeb6$bff63350$0403a8c0@fejleszt4/
Also on:    postgresql.org/message-id/01e301c3aeb6$bff63350$0403a8c0@fejleszt4

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: surrano@mailbox.hu
  Subject: Re: constant vs function param differs in performance
  In-Reply-To: <01e301c3aeb6$bff63350$0403a8c0@fejleszt4>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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