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 4024352A7C for ; Mon, 18 Apr 2005 15:33:37 -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 29529-04 for ; Mon, 18 Apr 2005 18:33:28 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id 2A1EC52955 for ; Mon, 18 Apr 2005 15:33:27 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1DNb3c-0007cE-00; Mon, 18 Apr 2005 14:33:16 -0400 To: Tom Lane Cc: "Merlin Moncure" , "Dawid Kuroczko" , pgsql-performance@postgresql.org Subject: Re: immutable functions vs. join for lookups ? References: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local> <22219.1113839449@sss.pgh.pa.us> In-Reply-To: <22219.1113839449@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 18 Apr 2005 14:33:15 -0400 Message-ID: <87u0m4j5p0.fsf@stark.xeocode.com> Lines: 40 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/426 X-Sequence-Number: 11823 Tom Lane writes: > It would be interesting sometime to try to teach the planner about > inlining SQL-language functions to become joins. That is, given > > create function id2name(int) returns text as > 'select name from mytab where id = $1' language sql stable; > > select uid, id2name(uid) from othertab where something; > > I think that in principle this could automatically be converted to > > select uid, name from othertab left join mytab on (uid = id) where something; The Inlining of the function is presumably a side-issue. I have tons of queries that use subqueries in the select list for which the same behaviour would be appropriate. Things like select uid, (select name from mytab where id = uid) as name from othertab ... > There are some pitfalls though, particularly that you'd have to be able to > prove that the function's query couldn't return more than one row (else the > join might produce more result rows than the original query). Or just have a special join type that has the desired behaviour in that case. Ie, pretend the query was really SELECT * FROM othertab LEFT SINGLE JOIN mytab ... Where "LEFT SINGLE JOIN" is an imaginary syntax that doesn't actually have to exist in the parser, but exists in the planner/executor and behaves differently in the case of duplicate matches. Actually I could see such a syntax being useful directly too. -- greg