X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 31FA5D1D85C for ; Wed, 19 Nov 2003 16:07:09 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04794-04 for ; Wed, 19 Nov 2003 12:06:38 -0400 (AST) Received: from mystic.root.hu (mystic.root.hu [62.68.166.4]) by svr1.postgresql.org (Postfix) with ESMTP id 7508BD1D8DE for ; Wed, 19 Nov 2003 12:05:55 -0400 (AST) Received: from [213.163.10.103] (helo=fejleszt4) by mystic.root.hu with asmtp (Exim 3.36 #1 (Debian)) id 1AMUpr-0002ah-00 (authenticated sender: 44b23a02ffc625b094b01917b5736f32) for ; Wed, 19 Nov 2003 17:05:43 +0100 Message-ID: <01e301c3aeb6$bff63350$0403a8c0@fejleszt4> From: "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" To: References: <01a601c3aac1$281e72c0$0403a8c0@fejleszt4> <5187.1068843554@sss.pgh.pa.us> Subject: Re: constant vs function param differs in performance Date: Wed, 19 Nov 2003 17:04:01 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Authenticated-Sender: 44b23a02ffc625b094b01917b5736f32 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/158 X-Sequence-Number: 4718 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" Sent: Friday, November 14, 2003 9:59 PM > "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" 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 >