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