Message-ID: <479DC630.9010707@rwa-net.co.uk>
Date: Mon, 28 Jan 2008 12:10:24 +0000
From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
Reply-To: mlunnon@rwa-net.co.uk
Organization: RWA
User-Agent: Thunderbird 1.5.0.13 (Windows/20070809)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
CC: Heikki Linnakangas <heikki@enterprisedb.com>
Subject: Re: Performance problems inside a stored procedure.
References: <479DC452.8010400@rwa-net.co.uk> <479DC558.30409@enterprisedb.com>
In-Reply-To: <479DC558.30409@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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