DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Hxeduh/uwG6wCIlx9DUbXI3doNJr+Crlvjqs4bFe4jh9fK66ox47tXPgrjZgkE7qMQhCYUOIG9nZsq5YqmSo0fqEm5OVt3rvAfr5lzLOuTU4iE2xFQSUqyPiOjkk9KQO/l86jCM0SEIkiBQMcehLwIMCAsQyZj35vYSAEcmRbYY=
Message-ID: <c2d9e70e0705052137i44d56415ud7ec0a8fa9250e23@mail.gmail.com>
Date: Sat, 5 May 2007 23:37:24 -0500
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Aaron Bono" <postgresql@aranya.com>
Subject: Re: Sequence vs. Index Scan
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
	"PostgreSQL SQL List" <pgsql-sql@postgresql.org>
In-Reply-To: <bf05e51c0705052125h33c1579ch652c978647d530d9@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <bf05e51c0705051405p29ee139fn30c9979f3772a1d4@mail.gmail.com>
	<29507.1178410970@sss.pgh.pa.us>
	<bf05e51c0705052125h33c1579ch652c978647d530d9@mail.gmail.com>

On 5/5/07, Aaron Bono <postgresql@aranya.com> wrote:
> On 5/5/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Aaron Bono" <postgresql@aranya.com> writes:
> > > 9.                           ->  Seq Scan on branch  (cost=0.00..4.72
> rows=1
> > > width=1281) (actual time= 130129.988..157492.057 rows=1 loops=1)
> > > 10.                                Filter: ((start_day
> <= now()) AND
> > > ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
> > > get_branch_for_zip('22151'::character varying)))
> >
> > There is something *awfully* wacko about that entry --- the fact that
> > the cost estimate is less than 5 units means that the planner thinks
> > there's 4 or fewer pages; either that's way wrong or the
> > get_branch_for_zip function is taking enormous amounts of time per row.
> > Have you tried timing that function on its own?
> >
> > One possible reason for the performance difference is if you have
> > get_branch_for_zip marked as stable in one database and volatile in the
> > other --- volatile would prevent it from being used in an indexqual as
> > you'd like.
> >
>
> I verified it by putting a RAISE NOTICE in the function.  The fast schema
> runs the function twice (odd, I would think it would run only once).  The
> slow schema runs it 30 times (the number of records returned + 1).  I know I
> put the functions into both schemas as stable and even dropped and recreated
> the function.  Then I verified with EMS Manager and it tells me the DDL for
> the function in the database is set to stable.  Is there something I can do
> to tell PostgreSQL that I really did mean stable?
>

maybe this is silly but you can verify what the database thinks of the
function selecting from pg_proc

select pronamespace, provolatile
from pg_proc where proname = 'get_branch_for_zip'

-- 
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
                                       Richard Cook