Thread: Performance problems inside a stored procedure.

Hi ms

I have a query which runs pretty quick ( 0.82ms) but when I put it
inside a stored procedure  it takes 10 times as long (11.229ms).  Is
this what you would expect and is there any way that I can get around
this time delay?

postgres.conf changes.

shared_buffers = 500MB
work_mem = 10MB
maintenance_work_mem = 100MB
effective_cache_size = 2048MB
default_statistics_target = 1000

Thanks for any help.

Regards
Matthew.

Matthew Lunnon wrote:
> I have a query which runs pretty quick ( 0.82ms) but when I put it
> inside a stored procedure  it takes 10 times as long (11.229ms).  Is
> this what you would expect and is there any way that I can get around
> this time delay?

It depends. You'll need to show us the function. Also, what version of
Postgres are you running?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Ahh, sorry, I have been too aggressive with my cutting, I am running
8.2.6 and the function is below.

Thanks.
Matthew

CREATE OR REPLACE FUNCTION sp_get_price_panel_id(int4, "varchar",
"varchar", "varchar", bpchar)
  RETURNS SETOF t_market_price_panel AS
$BODY$
SELECT *
   FROM market mrkt
   JOIN market_group_relation mgr USING (market_id)
   JOIN market_group mg USING (market_group_id)
   JOIN market_group_price_relation mgpr USING (market_group_id)
   JOIN accommodation_price_panel app ON
app.accommodation_price_panel_id = mgpr.price_panel_id
WHERE mrkt.live <> 'X'::bpchar AND mg.live <> 'X'::bpchar AND app.live
<> 'X'::bpchar
    AND MARKET_ID = $1
    AND CODE = $2
    AND CODE_TYPE = $3::CHAR(2)
    AND CONTRACT_ID = $4
    AND ( PRICE_PANEL_TYPE = 'B' OR PRICE_PANEL_TYPE = $5 );
$BODY$
  LANGUAGE 'sql' VOLATILE;


Heikki Linnakangas wrote:
> Matthew Lunnon wrote:
>> I have a query which runs pretty quick ( 0.82ms) but when I put it
>> inside a stored procedure  it takes 10 times as long (11.229ms).  Is
>> this what you would expect and is there any way that I can get around
>> this time delay?
>
> It depends. You'll need to show us the function. Also, what version of
> Postgres are you running?
>

Matthew Lunnon wrote:

> Ahh, sorry, I have been too aggressive with my cutting, I am running
> 8.2.6 and the function is below.
>
<snip>

> $BODY$
>  LANGUAGE 'sql' VOLATILE;
                  ^^^^^^^^^^
I suspect that it's because you're using VOLATILE (so no good
optimizations is done); did you try STABLE? Could you show us the
EXPLAIN ANALYZE of query and function?


--
   Euler Taveira de Oliveira
   http://www.timbira.com/

Thanks Euler,

I made the change to STABLE but it didn't seem to make any difference.
On closer inspection it seems to have been a casting problem, I was
passing a varchar into the function and then testing this for equality
with an integer.  The planner seems to have been unable to use this to
access the index and so was returning too many rows and then filtering
them.  It looks like I still have to take a hit of 2ms or so to call the
function but I guess that is not unreasonable.

Thanks for your help and to everyone who answered this thread.

Regards
Matthew.

Euler Taveira de Oliveira wrote:
> Matthew Lunnon wrote:
>
>> Ahh, sorry, I have been too aggressive with my cutting, I am running
>> 8.2.6 and the function is below.
>>
> <snip>
>
>> $BODY$
>>  LANGUAGE 'sql' VOLATILE;
>                  ^^^^^^^^^^
> I suspect that it's because you're using VOLATILE (so no good
> optimizations is done); did you try STABLE? Could you show us the
> EXPLAIN ANALYZE of query and function?
>
>

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