Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D795E9FB865 for ; Mon, 7 May 2007 11:38:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 76370-04 for ; Mon, 7 May 2007 11:38:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D1CA69FB849 for ; Mon, 7 May 2007 11:38:10 -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 l47Ebxq3005448; Mon, 7 May 2007 10:37:59 -0400 (EDT) To: Andrew Sullivan cc: pgsql-sql@postgresql.org Subject: Re: Sequence vs. Index Scan In-reply-to: <20070507105829.GB30076@phlogiston.dyndns.org> References: <29507.1178410970@sss.pgh.pa.us> <20070507105829.GB30076@phlogiston.dyndns.org> Comments: In-reply-to Andrew Sullivan message dated "Mon, 07 May 2007 06:58:29 -0400" Date: Mon, 07 May 2007 10:37:59 -0400 Message-ID: <5447.1178548679@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/44 X-Sequence-Number: 28325 Andrew Sullivan writes: > On Sun, May 06, 2007 at 01:45:54PM -0500, Aaron Bono wrote: >> So my conclusion is that the function is being treated as volatile even >> though it is stable because the number of records is small. > I don't think that's the issue. If this is dependent on the > number of records, then for some reason the way the data is > structured means that the planner thinks a seqscan's a better bet. > This is probably due to distribution of the values. You could try > increasing the stats sample, and see if that helps. It's got nothing to do with distribution, just with numbers of pages to fetch. You'll nearly always get a seqscan plan if there are only a couple of pages in the table, simply because it would take more I/O to read the index too. The reason this is a problem in this example is that the function is so expensive to execute. The planner should be avoiding the seqscan on the basis of CPU cost not I/O cost, but it doesn't know that the function is expensive enough to drive the decision that way. In CVS HEAD (8.3-to-be) we've added a "cost" property to functions, which provides a clean way to fix this issue, but there's no good way to deal with it in existing releases :-( regards, tom lane