From: CS DBA <cs_dba@consistentstate.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: function slower than the same code in an sql file
Date: Fri, 28 Oct 2011 07:39:40 -0600
Message-ID: <4EAAB09C.9060202@consistentstate.com> (raw)
In-Reply-To: <14399.1319778608@sss.pgh.pa.us>
References: <4EAA3597.3020202@consistentstate.com>
<14399.1319778608@sss.pgh.pa.us>
On 10/27/2011 11:10 PM, Tom Lane wrote:
> CS DBA<cs_dba@consistentstate.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
---------------------------------------------
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: cs_dba@consistentstate.com, tgl@sss.pgh.pa.us
Subject: Re: function slower than the same code in an sql file
In-Reply-To: <4EAAB09C.9060202@consistentstate.com>
* 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