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 39D7CD1B897 for ; Fri, 6 Feb 2004 08:43:02 +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 95995-02 for ; Fri, 6 Feb 2004 04:43:01 -0400 (AST) Received: from alvarezp.ods.org (unknown [200.77.201.28]) by svr1.postgresql.org (Postfix) with ESMTP id 5EC24D1D31D for ; Fri, 6 Feb 2004 04:42:58 -0400 (AST) Received: (from apache@localhost) by alvarezp.ods.org (8.11.6/8.11.6) id i168hDl12193; Fri, 6 Feb 2004 00:43:13 -0800 X-Authentication-Warning: alvarezp.ods.org: apache set sender to alvarezp@alvarezp.ods.org using -f Received: from 192.168.0.64 (SquirrelMail authenticated user alvarezp) by alvarezp.ods.org with HTTP; Fri, 6 Feb 2004 00:43:12 -0800 (PST) Message-ID: <4795.192.168.0.64.1076056992.squirrel@alvarezp.ods.org> In-Reply-To: <40234207.6040207@klaster.net> References: <4207.192.168.0.64.1076051944.squirrel@alvarezp.ods.org> <40234207.6040207@klaster.net> Date: Fri, 6 Feb 2004 00:43:12 -0800 (PST) Subject: Re: Seq scan on zero-parameters function From: "Octavio Alvarez" To: "Tomasz Myrta" Cc: pgsql-performance@postgresql.org Reply-To: alvarezp@alvarezp.ods.org User-Agent: SquirrelMail/1.4.0-1.7.x MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/85 X-Sequence-Number: 5609 Tomasz Myrta said: > Dnia 2004-02-06 08:19, U¿ytkownik Octavio Alvarez napisa³: >> In each couple, the first one has a WHERE field = function() >> condition, just like the second one, but in the form WHERE field = >> (SELECT function()). In my opinion, both should have the same execution >> plan, as the function has no parameters and, therefore, is constant. > > Nope. > > What would you say about function without params returning timeofday()? > Is it constant? No... :-P ;-) > If you are sure, that your function returns constant value - declare it > as IMMUTABLE. (look at CREATE FUNCTION documentation) Thanks for the hint. In fact, my current_period_id() is based on time, but it should be constant along the query execution. I mean, I don't want some records filtered with some values and other with other values... I'll have an uncongruent recordset. Say SELECT [field-list] FROM [complex-join] WHERE sec = datepart('second', now()); Now suppose the query takes always more than 1 second because of the complex-join or whatever reason: I will naver have a congruent recordset. IMMUTABLE wouldn't help here, only wrapping the function in a subquery. Is this expected behavior? Is this standards compliant (if it can be qualified as such)? Octavio. -- Octavio Alvarez. E-mail: alvarezp@alvarezp.ods.org. Agradezco que sus correos sean enviados siempre a esta dirección.