X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2B20E9DC838 for ; Fri, 13 Jan 2006 20:27:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28073-09 for ; Fri, 13 Jan 2006 20:27:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8F0859DC80C for ; Fri, 13 Jan 2006 20:27:27 -0400 (AST) 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 k0E0RSv0016008; Fri, 13 Jan 2006 19:27:28 -0500 (EST) To: "Jim C. Nasby" cc: Mark Liberman , pgsql-performance@postgresql.org Subject: Re: Stable function being evaluated more than once in a single query In-reply-to: <20060114000640.GN9017@pervasive.com> References: <200601111641.20627.mliberman@mixedsignals.com> <935.1137040403@sss.pgh.pa.us> <20060114000640.GN9017@pervasive.com> Comments: In-reply-to "Jim C. Nasby" message dated "Fri, 13 Jan 2006 18:06:40 -0600" Date: Fri, 13 Jan 2006 19:27:28 -0500 Message-ID: <16007.1137198448@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.088 required=5 tests=[AWL=0.088] X-Spam-Score: 0.088 X-Spam-Level: X-Archive-Number: 200601/174 X-Sequence-Number: 16652 "Jim C. Nasby" writes: > Is the issue that the optimizer won't combine two function calls (ie: > SELECT foo(..) ... WHERE foo(..)), or is it that sometimes it won't make > the optimization (maybe depending on the query plan, for example)? What the STABLE category actually does is give the planner permission to use the function within an indexscan qualification, eg, WHERE indexed_column = f(42) Since an indexscan involves evaluating the comparison expression just once and using its value to search the index, this would be incorrect if the expression's value might change from row to row. (For VOLATILE functions, we assume that the correct behavior is the naive SQL semantics of actually computing the WHERE clause at each candidate row.) There is no function cache and no checking for duplicate expressions. I think we do check for duplicate aggregate expressions, but not anything else. regards, tom lane