From: Aaron Bono <postgresql@aranya.com>
To: Jaime Casanova <systemguards@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: Re: Sequence vs. Index Scan
Date: Sun, 6 May 2007 13:45:54 -0500
Message-ID: <bf05e51c0705061145s43815026q49394acba8407c1f@mail.gmail.com> (raw)
In-Reply-To: <c2d9e70e0705052137i44d56415ud7ec0a8fa9250e23@mail.gmail.com>
References: <bf05e51c0705051405p29ee139fn30c9979f3772a1d4@mail.gmail.com>
<29507.1178410970@sss.pgh.pa.us>
<bf05e51c0705052125h33c1579ch652c978647d530d9@mail.gmail.com>
<c2d9e70e0705052137i44d56415ud7ec0a8fa9250e23@mail.gmail.com>
On 5/5/07, Jaime Casanova <systemguards@gmail.com> wrote:
>
> 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'
>
select pronamespace, provolatile, proname
from pg_proc where proname = 'get_branch_for_zip';
pronamespace | provolatile | proname
--------------+-------------+--------------------
26644852 | s | get_branch_for_zip
26644856 | s | get_branch_for_zip
The select is using the function on the slow schema as if it were volatile
but as stable on the fast schema.
I did a restart of the service and that didn't help.
Then I inserted 150 more records in the slow schema and pow - it started
working like the fast schema.
So my conclusion is that the function is being treated as volatile even
though it is stable because the number of records is small. Is there any
way to tell PostgreSQL that when I say stable I really mean stable?
I am getting close to a solution. Thanks again for 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, systemguards@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Sequence vs. Index Scan
In-Reply-To: <bf05e51c0705061145s43815026q49394acba8407c1f@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