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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
