Message-ID: <47A82D25.9000002@rwa-net.co.uk>
Date: Tue, 05 Feb 2008 09:32:21 +0000
From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
Reply-To: mlunnon@rwa-net.co.uk
Organization: RWA
User-Agent: Thunderbird 1.5.0.13 (Windows/20070809)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Performance problems inside a stored procedure.
References: <479DC452.8010400@rwa-net.co.uk>
	<78700072.20080205084422@sart.must-ipra.com>
In-Reply-To: <78700072.20080205084422@sart.must-ipra.com>
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: 8bit

Thanks for your help Андрей your English is easily understandable and 
much better than my ... (Russian?). I managed to get the results of an 
analyze and this showed that an index was not being used correctly. It 
seems that I was passing in a varchar and not casting it to an int and 
this stopped the index from being used.  I suppose this is a change in 
the implicit casting rules between version 7.4.7 and 8.x.

Once I added the explicit cast the function now uses the correct plan 
and returns in about 3 ms which I suppose is the performance hit that a 
function call has.

Anyway thanks very much for your time.

Regards
Matthew

Андрей Репко wrote:
> Hello Matthew,
>
> Monday, January 28, 2008, 2:02:26 PM, Вы писали:
>
> ML> I have a query which runs pretty quick ( 0.82ms) but when I put it
> ML> inside a stored procedure  it takes 10 times as long (11.229ms).  Is 
> ML> this what you would expect and is there any way that I can get around 
> ML> this time delay?
>
> ML> postgres.conf changes.
>
> ML> shared_buffers = 500MB
> ML> work_mem = 10MB
> ML> maintenance_work_mem = 100MB
> ML> effective_cache_size = 2048MB
> ML> default_statistics_target = 1000
>
> ML> Thanks for any help.
> When you run it outside stored procedure optimizer know about your
> parameters, and know what rows (estimate count) will be selected, so
> it can create fine plan. When you put it into SP optimizer don't know
> nothing about value of your parameters, but MUST create plan for it.
> If table is frequently updateable plan, what was created for SP
> became bad, and need replaning.
>
> It's sample for obtaining plan (LeXa NalBat):
>
> create function f1 ( integer, integer )
>   returns void language plpgsql as $body$
> declare
>   _rec record;
> begin
>   for _rec in explain
>
>   -- put your query here
>     select count(*) from t1 where id between $1 and $2
>
>   loop
>     raise info '%', _rec."QUERY PLAN";
>   end loop;
>   return;
> end;
> $body$;
>
> Sorry for bad English.
>
>