X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7525153A37 for ; Fri, 22 Apr 2005 07:15:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75139-02 for ; Fri, 22 Apr 2005 10:15:23 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.201]) by svr1.postgresql.org (Postfix) with ESMTP id 288B75367E for ; Fri, 22 Apr 2005 07:15:22 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 34so1316687nzf for ; Fri, 22 Apr 2005 03:15:25 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=mwaMTjSgnqCbnE+oRR9X1Zw1QJGiMkiZ8a9+5J9/CvTSNSWUgWTA/FF6J853N4cDbbJoD7sa71HoBJGDYpLaOMFj0RKlhFcjmMaAadOtMrIDyWtrSoDIjrEK3IGFRsF82/lWPYKT8GFZBMMiue9wK9NhbamjWEiix2RXyxo1J3k= Received: by 10.36.12.2 with SMTP id 2mr278571nzl; Fri, 22 Apr 2005 03:15:24 -0700 (PDT) Received: by 10.36.22.15 with HTTP; Fri, 22 Apr 2005 03:15:24 -0700 (PDT) Message-ID: <758d5e7f050422031579a04963@mail.gmail.com> Date: Fri, 22 Apr 2005 12:15:24 +0200 From: Dawid Kuroczko Reply-To: Dawid Kuroczko To: weigelt@metux.de, pgsql-performance@postgresql.org Subject: Re: immutable functions vs. join for lookups ? In-Reply-To: <20050421192226.GB16548@nibiru.borg.metux.de> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20050415205511.GB20345@nibiru.borg.metux.de> <6776.1113599567@sss.pgh.pa.us> <20050417060603.GA23283@nibiru.borg.metux.de> <20050421192226.GB16548@nibiru.borg.metux.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.681 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS, RCVD_BY_IP, RCVD_IN_BL_SPAMCOP_NET X-Spam-Level: * X-Archive-Number: 200504/612 X-Sequence-Number: 12009 On 4/21/05, Enrico Weigelt wrote: > > > Even if your data never changes it *can* change so the function should > > be at most stable not immutable. >=20 > okay, the planner sees that the table could potentionally change. > but - as the dba - I'd like to tell him, this table *never* changes > in practise (or at most there will be an insert once a year) >=20 > isnt there any way to enforce the function to be really immutable ? Never say never. :) And to answer your question -- your IMMUTABLE function may reference other functions (even VOLATILE). So you may create a "caller" immutable function which just calls your non-immutable function. But from performance standpoint there is not much difference (probably your STABLE function will be faster than STABLE inside IMMUTABLE function). Ah, and please note that some time in future PostgreSQL may require that IMMUTABLE function calls only IMMUTABLE functions. Regards, Dawid