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 8F1B353803 for ; Fri, 15 Apr 2005 18:12:59 -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 63493-09 for ; Fri, 15 Apr 2005 21:12:49 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A1E5C537D4 for ; Fri, 15 Apr 2005 18:12:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3FLClMQ006777; Fri, 15 Apr 2005 17:12:48 -0400 (EDT) To: weigelt@metux.de Cc: pgsql-performance@postgresql.org Subject: Re: immutable functions vs. join for lookups ? In-reply-to: <20050415205511.GB20345@nibiru.borg.metux.de> References: <20050415205511.GB20345@nibiru.borg.metux.de> Comments: In-reply-to Enrico Weigelt message dated "Fri, 15 Apr 2005 22:55:11 +0200" Date: Fri, 15 Apr 2005 17:12:47 -0400 Message-ID: <6776.1113599567@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/366 X-Sequence-Number: 11763 Enrico Weigelt writes: > c) CREATE FUNCTION id2username(oid) RETURNS text > LANGUAGE 'SQL' IMMUTABLE AS ' > SELECT username AS RESULT FROM users WHERE uid = $1'; This is simply dangerous. The function is *NOT* immutable (it is stable though). When ... not if ... your application breaks because you got the wrong answers, you'll get no sympathy from anyone. The correct question to ask was "if I make a stable function like this, is it likely to be faster than the join?". The answer is "probably not; at best it will be equal to the join". The best the planner is likely to be able to do with the function-based query is equivalent to a nestloop with inner indexscan (assuming there is an index on users.uid). If that's the best plan then the join case should find it too ... but if you are selecting a lot of items rows then it won't be the best plan. regards, tom lane