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 852455346A for ; Mon, 18 Apr 2005 12:51:24 -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 59633-10 for ; Mon, 18 Apr 2005 15:51:14 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 24223533CE for ; Mon, 18 Apr 2005 12:51:11 -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 j3IFonvu022220; Mon, 18 Apr 2005 11:50:52 -0400 (EDT) To: "Merlin Moncure" Cc: "Dawid Kuroczko" , pgsql-performance@postgresql.org Subject: Re: immutable functions vs. join for lookups ? In-reply-to: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local> Comments: In-reply-to "Merlin Moncure" message dated "Mon, 18 Apr 2005 08:50:46 -0400" Date: Mon, 18 Apr 2005 11:50:49 -0400 Message-ID: <22219.1113839449@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/405 X-Sequence-Number: 11802 "Merlin Moncure" writes: >> d) self-join with a function ;) >> EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username >> FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN >> aaa USING (n); > That's pretty clever. > It sure seems like the server was not caching the results of the > function...maybe the server thought it was to small a table to bother? No, it probably flattened the subquery on sight (looking at the actual EXPLAIN output would confirm or disprove that). You could prevent the flattening by adding OFFSET 0 in the subquery. However, the SELECT DISTINCT sub-sub-query is expensive enough, and the join itself is expensive enough, that you would need an *enormously* expensive id2username() function to make this a win. 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; which is much more amenable to join optimization. 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). regards, tom lane