Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id F10329FB30F for ; Sun, 6 May 2007 00:29:03 -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 56392-10 for ; Sun, 6 May 2007 00:28:57 -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.244]) by postgresql.org (Postfix) with ESMTP id C586D9FB310 for ; Sun, 6 May 2007 00:28:57 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so39742and for ; Sat, 05 May 2007 20:28:57 -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=iBTI/9nkieVgb/mnak0c1iM3DQAQjSpb/JzDEaT5oBfkzDLUdxXjFki7EYhZb8ErbX1DSyh5QoO+VKGeIHe/N0JXUnbvZ/yhcFi4XUYfH2cwPlFxqsG3tFrNf4yX3BCsIvwrfztTuY/zwdIFQsVOxTvTdjETEf/t3E2aUuXb6jQ= 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=nWPCQFPqQ9yTWnX3rUkuxNmqdz/hW5Tlkyhqcf8BEMrMQakUODdFjV42UaGSq6tO7fHaR+eO6xTBQWZU7Va45H48c+4GwHvSx7MmcUV41Mv36wnIidRY7/BS19NOhsCYt7EZFBJXJ2XHLA+KW0bKGnOHQFHXevilAxgizOjTYfs= Received: by 10.100.199.12 with SMTP id w12mr3829063anf.1178422136967; Sat, 05 May 2007 20:28:56 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Sat, 5 May 2007 20:28:56 -0700 (PDT) Message-ID: Date: Sat, 5 May 2007 22:28:56 -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_72379_24519407.1178422136915" References: <29507.1178410970@sss.pgh.pa.us> X-Google-Sender-Auth: 1f391f1a64b58512 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/30 X-Sequence-Number: 28311 ------=_Part_72379_24519407.1178422136915 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? Yes, the first thing I did was run it on its own. It takes about 2-6 seconds to run which is not great but acceptable and the functions are marked as stable. It is the same +/- a second or two on each of the two schemas. The function is a plpgsql function that executes a dynamic SQL string. Would it be better to change it to a straight SQL function? Would that help the planner? I may be able to change it if this will help. 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 just switched the functions in both schemas to be stable (before I posted the original question), they were volatile. After doing that, the fast schema worked great but the slow one is still under performing. I even went back to the slow schema and dropped and recreated the function with no discernible difference. This is for PostgreSQL 8.1.3 and the results are the same on Windows and Linux versions. The windows version is a restore from the Linux version's backup. -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_72379_24519407.1178422136915 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?

Yes, the first thing I did was run it on its own.  It takes about 2-6 seconds to run which is not great but acceptable and the functions are marked as stable.  It is the same +/- a second or two on each of the two schemas.  The function is a plpgsql function that executes a dynamic SQL string.  Would it be better to change it to a straight SQL function?  Would that help the planner?  I may be able to change it if this will help.

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 just switched the functions in both schemas to be stable (before I posted the original question), they were volatile.  After doing that, the fast schema worked great but the slow one is still under performing.  I even went back to the slow schema and dropped and recreated the function with no discernible difference.

This is for PostgreSQL 8.1.3 and the results are the same on Windows and Linux versions.  The windows version is a restore from the Linux version's backup.

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