From: Aaron Bono <postgresql@aranya.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sequence vs. Index Scan
Date: Mon, 7 May 2007 11:40:19 -0500
Message-ID: <bf05e51c0705070940y5c5bc0ue5d52cc50a1a4b80@mail.gmail.com> (raw)
In-Reply-To: <5447.1178548679@sss.pgh.pa.us>
References: <bf05e51c0705051405p29ee139fn30c9979f3772a1d4@mail.gmail.com>
<29507.1178410970@sss.pgh.pa.us>
<bf05e51c0705052125h33c1579ch652c978647d530d9@mail.gmail.com>
<c2d9e70e0705052137i44d56415ud7ec0a8fa9250e23@mail.gmail.com>
<bf05e51c0705061145s43815026q49394acba8407c1f@mail.gmail.com>
<20070507105829.GB30076@phlogiston.dyndns.org>
<5447.1178548679@sss.pgh.pa.us>
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.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, tgl@sss.pgh.pa.us, ajs@crankycanuck.ca
Subject: Re: Sequence vs. Index Scan
In-Reply-To: <bf05e51c0705070940y5c5bc0ue5d52cc50a1a4b80@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox