Skip site navigation (1) Skip section navigation (2)

Re: function slower than the same code in an sql file

From: CS DBA <cs_dba(at)consistentstate(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: function slower than the same code in an sql file
Date: 2011-10-28 13:39:40
Message-ID: 4EAAB09C.9060202@consistentstate.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 10/27/2011 11:10 PM, Tom Lane wrote:
> CS DBA<cs_dba(at)consistentstate(dot)com>  writes:
>> I have code that drops a table, re-create's it (based on a long set of
>> joins) and then re-creates the indexes.
>> It runs via psql in about 10 seconds.  I took the code and simply
>> wrapped it into a plpgsql function and the function version takes almost
>> 60 seconds to run.
>> I always thought that functions should run faster than psql... am I wrong?
> Did you really just put the identical queries into a function, or did
> you parameterize them with values passed to the function?
>
> Parameterized queries are often slower due to the planner not knowing
> the specific constant values that are used in the actual calls.  There's
> some work done for 9.2 to improve that, but in existing releases you
> typically have to construct dynamic queries and EXECUTE them if you run
> into this problem.
>
> 			regards, tom lane


No parameters,  one of them looks like this:


CREATE or REPLACE FUNCTION refresh_xyz_view_m() RETURNS TRIGGER  AS $$
BEGIN

DROP TABLE xyz_view_m ;
CREATE TABLE xyz_view_m AS
SELECT
         pp.id, pp.name, pp.description, pp.tariff_url, ppe.account_id, 
pp.active, ppe.time_zone
FROM
         tab1 pp, enrollment ppe
WHERE
         ((pp.id = ppe.pp_id) AND pp.active);

create index xyz_m_id_idx on xyx_view_m(id);


analyze xyz_view_m;

RETURN NULL;
END
$$
LANGUAGE plpgsql;

-- 
---------------------------------------------
Kevin Kempter       -       Constent State
A PostgreSQL Professional Services Company
           www.consistentstate.com
---------------------------------------------

In response to

Responses

pgsql-performance by date

Next:From: Marcus EngeneDate: 2011-10-28 15:28:23
Subject: WAL in RAM
Previous:From: David BorehamDate: 2011-10-28 13:22:04
Subject: Re: Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group