Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B37579FB579 for ; Sun, 6 May 2007 01:25:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 69794-07 for ; Sun, 6 May 2007 01:25:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.249]) by postgresql.org (Postfix) with ESMTP id 93D7D9FB578 for ; Sun, 6 May 2007 01:25:20 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so40888and for ; Sat, 05 May 2007 21:25:20 -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=DQGE8cVQvwjHklaWIXLxF3m9VpmblBreL+JL9AmvS85vGrY6dbzDbwgES0XRAohugu8jEJTLsa2L5XuD8V7awN8gFf2j2kqNI9mAxDyfTBDJG35QALqTSwvQ89Smnehi8iJ+xosREQCTRlsZJPWBhpu1hVd5dR2LS+ZK/zLPOeo= 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=AmlKN1JRQPVJDwNwrKuQmeDLL+P9Ea/UhJX1IexAE1CPhpQBmW86PP26wey+No9CcrzdWk4R4bcbPlPUsqKJzf0+e4QGRa68Vn73+z5RnkcnPsBt7oMushSU/lap2Fw7F7lJ3X1fGt/jGfxEDtsfu6x4KIud1MSvXrojNeb7Kec= Received: by 10.100.163.12 with SMTP id l12mr3839859ane.1178425519871; Sat, 05 May 2007 21:25:19 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Sat, 5 May 2007 21:25:19 -0700 (PDT) Message-ID: Date: Sat, 5 May 2007 23:25:19 -0500 From: "Aaron Bono" To: "Tom Lane" Subject: Re: Sequence vs. Index Scan Cc: "PostgreSQL SQL List" In-Reply-To: <29507.1178410970@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_72635_23724506.1178425519807" References: <29507.1178410970@sss.pgh.pa.us> X-Google-Sender-Auth: b77f3d8ebb000e74 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/32 X-Sequence-Number: 28313 ------=_Part_72635_23724506.1178425519807 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 5/5/07, Tom Lane wrote: > > "Aaron Bono" writes: > > 9. -> Seq Scan on branch (cost=0.00..4.72rows=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? Thanks for all the help, Aaron -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_72635_23724506.1178425519807 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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?

Thanks for all the help,
Aaron

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