Re: Performance problems inside a stored procedure.

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.
>
>