From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: Performance problems inside a stored procedure.
Date: Tue, 05 Feb 2008 09:32:21 +0000
Message-ID: <47A82D25.9000002@rwa-net.co.uk> (raw)
In-Reply-To: <78700072.20080205084422@sart.must-ipra.com>
References: <479DC452.8010400@rwa-net.co.uk>
<78700072.20080205084422@sart.must-ipra.com>
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.
>
>
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-performance@postgresql.org
Cc: mlunnon@rwa-net.co.uk
Subject: Re: Performance problems inside a stored procedure.
In-Reply-To: <47A82D25.9000002@rwa-net.co.uk>
* 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