pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance problems inside a stored procedure.
6+ messages / 3 participants
[nested] [flat]
* Performance problems inside a stored procedure.
@ 2008-01-28 12:02 Matthew Lunnon <mlunnon@rwa-net.co.uk>
0 siblings, 2 replies; 6+ messages in thread
From: Matthew Lunnon @ 2008-01-28 12:02 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance problems inside a stored procedure.
@ 2008-01-28 12:06 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Matthew Lunnon <mlunnon@rwa-net.co.uk>
1 sibling, 1 reply; 6+ messages in thread
From: Heikki Linnakangas @ 2008-01-28 12:06 UTC (permalink / raw)
To: mlunnon@rwa-net.co.uk; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance problems inside a stored procedure.
@ 2008-01-28 12:10 Matthew Lunnon <mlunnon@rwa-net.co.uk>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 6+ messages in thread
From: Matthew Lunnon @ 2008-01-28 12:10 UTC (permalink / raw)
To: pgsql-performance; +Cc: Heikki Linnakangas <heikki@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?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance problems inside a stored procedure.
@ 2008-01-29 13:59 Euler Taveira de Oliveira <euler@timbira.com>
parent: Matthew Lunnon <mlunnon@rwa-net.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Euler Taveira de Oliveira @ 2008-01-29 13:59 UTC (permalink / raw)
To: mlunnon@rwa-net.co.uk; +Cc: pgsql-performance@postgresql.org, Heikki Linnakangas <heikki@enterprisedb.com>
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance problems inside a stored procedure.
@ 2008-01-29 17:38 Matthew Lunnon <mlunnon@rwa-net.co.uk>
parent: Euler Taveira de Oliveira <euler@timbira.com>
0 siblings, 0 replies; 6+ messages in thread
From: Matthew Lunnon @ 2008-01-29 17:38 UTC (permalink / raw)
To: Euler Taveira de Oliveira <euler@timbira.com>; +Cc: pgsql-performance@postgresql.org, Heikki Linnakangas <heikki@enterprisedb.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?
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Performance problems inside a stored procedure.
@ 2008-02-05 09:32 Matthew Lunnon <mlunnon@rwa-net.co.uk>
parent: Matthew Lunnon <mlunnon@rwa-net.co.uk>
1 sibling, 0 replies; 6+ messages in thread
From: Matthew Lunnon @ 2008-02-05 09:32 UTC (permalink / raw)
To: pgsql-performance
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.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-02-05 09:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-28 12:02 Performance problems inside a stored procedure. Matthew Lunnon <mlunnon@rwa-net.co.uk>
2008-01-28 12:06 ` Heikki Linnakangas <heikki@enterprisedb.com>
2008-01-28 12:10 ` Matthew Lunnon <mlunnon@rwa-net.co.uk>
2008-01-29 13:59 ` Euler Taveira de Oliveira <euler@timbira.com>
2008-01-29 17:38 ` Matthew Lunnon <mlunnon@rwa-net.co.uk>
2008-02-05 09:32 ` Matthew Lunnon <mlunnon@rwa-net.co.uk>
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