Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 918509FB2F5 for ; Mon, 7 May 2007 13:40:24 -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 72744-03 for ; Mon, 7 May 2007 13:40:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.243]) by postgresql.org (Postfix) with ESMTP id E93659FA37E for ; Mon, 7 May 2007 13:40:19 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so129778and for ; Mon, 07 May 2007 09:40:19 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=FyS31qqrfNpfm9KATfXyCR/O6exYjELt6xoRvxR4ipS/zx9bosFz9i8orlusLn2m7l4LbcUcRhP8ezzmC1I+c87un+vbWOmTiaiuszwRHM+a3m+jnxgRYpEXZfyMu+mMDz00XxDWF0RvjsfbuHuk+34dX48hF97Lch37VO3WM2w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=eF4jP+s9gqRCCsgrWObLlSLGCFGfkltM4NaSuMfjgXcMi4zgIr0sTV2oYj6aJpYaNpU6j+YvMYc64XyhnWifCYOFaUm5Io6FCD475C+ZjPep0c1mjBoh7SndcgBb95sIYi4n9WZOYUZC7/NMVN9ymAF+MfVIgc6n3btvNh6wpjc= Received: by 10.100.195.10 with SMTP id s10mr4922007anf.1178556019341; Mon, 07 May 2007 09:40:19 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Mon, 7 May 2007 09:40:19 -0700 (PDT) Message-ID: Date: Mon, 7 May 2007 11:40:19 -0500 From: "Aaron Bono" To: "Tom Lane" Subject: Re: Sequence vs. Index Scan Cc: "Andrew Sullivan" , pgsql-sql@postgresql.org In-Reply-To: <5447.1178548679@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_93447_22994003.1178556019103" References: <29507.1178410970@sss.pgh.pa.us> <20070507105829.GB30076@phlogiston.dyndns.org> <5447.1178548679@sss.pgh.pa.us> X-Google-Sender-Auth: ba3286a0b7f14ba6 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/46 X-Sequence-Number: 28327 ------=_Part_93447_22994003.1178556019103 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 5/7/07, Tom Lane wrote: > > 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 Since we don't delete records, we just deactivate them, I added 100 dummy records that are not active. This fixed the problem. As the number of records in that table grows, I will delete the dummy records. Thanks for all the help! Aaron -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_93447_22994003.1178556019103 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 5/7/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Sullivan <ajs@crankycanuck.ca> 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

 
Since we don't delete records, we just deactivate them, I added 100 dummy records that are not active.  This fixed the problem.  As the number of records in that table grows, I will delete the dummy records.

Thanks for all the help!
Aaron

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_93447_22994003.1178556019103--