Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id F14DE9F995C for ; Mon, 13 Aug 2007 18:01:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 20580-03 for ; Mon, 13 Aug 2007 18:01:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 69D1B9F950B for ; Mon, 13 Aug 2007 18:01:43 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7DL1eKX021919; Mon, 13 Aug 2007 17:01:40 -0400 (EDT) To: Philipp Specht cc: pgsql-performance@postgresql.org Subject: Re: Stable function optimisation In-reply-to: <2E281675-B282-41F6-B7A2-86853250850A@phlybye.de> References: <2E281675-B282-41F6-B7A2-86853250850A@phlybye.de> Comments: In-reply-to Philipp Specht message dated "Mon, 13 Aug 2007 22:37:59 +0200" Date: Mon, 13 Aug 2007 17:01:40 -0400 Message-ID: <21918.1187038900@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/140 X-Sequence-Number: 26270 Philipp Specht writes: > The biggest question here is: Why is the runtime of the query with > the stable function not near the runtime of the immutable function? Stable functions don't get folded to constants. > It's definitely one query and the manual states that a stable > function does not change in one statement and therefore can be > optimised. That's not the type of optimization that gets done with it. What "STABLE" is for is marking functions that are safe to use in index conditions. If you'd been using an indexable condition you'd have seen three different behaviors here. (I see that you do have an index on t.a, but apparently there are too many matching rows for the planner to think the index is worth using.) regards, tom lane