pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction slower than the same code in an sql file
8+ messages / 5 participants
[nested] [flat]
* function slower than the same code in an sql file
@ 2011-10-28 04:54 CS DBA <cs_dba@consistentstate.com>
0 siblings, 2 replies; 8+ messages in thread
From: CS DBA @ 2011-10-28 04:54 UTC (permalink / raw)
To: pgsql-performance
Hi All ;
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?
Thanks in advance
--
---------------------------------------------
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
---------------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: function slower than the same code in an sql file
@ 2011-10-28 05:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: CS DBA <cs_dba@consistentstate.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2011-10-28 05:10 UTC (permalink / raw)
To: CS DBA <cs_dba@consistentstate.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: function slower than the same code in an sql file
@ 2011-10-28 05:10 Pavel Stehule <pavel.stehule@gmail.com>
parent: CS DBA <cs_dba@consistentstate.com>
1 sibling, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2011-10-28 05:10 UTC (permalink / raw)
To: CS DBA <cs_dba@consistentstate.com>; +Cc: pgsql-performance
Hello
plpgsql uses a cached prepared plans for queries - where optimizations
is based on expected values - not on real values. This feature can do
performance problems some times. When you have these problems, then
you have to use a dynamic SQL instead. This generate plans for only
one usage and then there optimization can be more exact (but it repeat
a plan generation)
http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Regards
Pavel Stehule
2011/10/28 CS DBA <cs_dba@consistentstate.com>:
> Hi All ;
>
> 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?
>
> Thanks in advance
>
> --
> ---------------------------------------------
> Kevin Kempter - Constent State
> A PostgreSQL Professional Services Company
> www.consistentstate.com
> ---------------------------------------------
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: function slower than the same code in an sql file
@ 2011-10-28 13:39 CS DBA <cs_dba@consistentstate.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: CS DBA @ 2011-10-28 13:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
---------------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: function slower than the same code in an sql file
@ 2011-11-03 14:42 Robert Haas <robertmhaas@gmail.com>
parent: CS DBA <cs_dba@consistentstate.com>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Haas @ 2011-11-03 14:42 UTC (permalink / raw)
To: CS DBA <cs_dba@consistentstate.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Fri, Oct 28, 2011 at 9:39 AM, CS DBA <cs_dba@consistentstate.com> wrote:
> No parameters, one of them looks like this:
>
> [ code snippet ]
It's hard to believe this is the real code, because SELECT without
INTO will bomb out inside a PL/pgsql function, won't it?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: function slower than the same code in an sql file
@ 2011-11-03 15:31 Rodrigo Gonzalez <rjgonzale@estrads.com.ar>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rodrigo Gonzalez @ 2011-11-03 15:31 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: CS DBA <cs_dba@consistentstate.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
El 03/11/11 11:42, Robert Haas escribió:
> On Fri, Oct 28, 2011 at 9:39 AM, CS DBA<cs_dba@consistentstate.com> wrote:
>> No parameters, one of them looks like this:
>>
>> [ code snippet ]
> It's hard to believe this is the real code, because SELECT without
> INTO will bomb out inside a PL/pgsql function, won't it?
>
But he's using CREATE TABLE xyz_view_m AS
So it seems correct to me
Regards
Rodrigo
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: function slower than the same code in an sql file
@ 2011-11-03 15:40 Robert Haas <robertmhaas@gmail.com>
parent: Rodrigo Gonzalez <rjgonzale@estrads.com.ar>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Haas @ 2011-11-03 15:40 UTC (permalink / raw)
To: Rodrigo Gonzalez <rjgonzale@estrads.com.ar>; +Cc: CS DBA <cs_dba@consistentstate.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Thu, Nov 3, 2011 at 11:31 AM, Rodrigo Gonzalez
<rjgonzale@estrads.com.ar> wrote:
> El 03/11/11 11:42, Robert Haas escribió:
>
> On Fri, Oct 28, 2011 at 9:39 AM, CS DBA <cs_dba@consistentstate.com> wrote:
>
> No parameters, one of them looks like this:
>
> [ code snippet ]
>
> It's hard to believe this is the real code, because SELECT without
> INTO will bomb out inside a PL/pgsql function, won't it?
>
> But he's using CREATE TABLE xyz_view_m AS
>
> So it seems correct to me
Oh, right, I missed that.
That seems pretty mysterious then. But is it possible the function is
getting called more times than it should? I notice that it's set up
as a trigger; is it FOR EACH ROW when it should be a statement-level
trigger or something like that? Maybe run EXPLAIN ANALYZE on the
query that's invoking the trigger to get some more detail on what's
going on?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: function slower than the same code in an sql file
@ 2011-11-03 22:42 CS DBA <cs_dba@consistentstate.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: CS DBA @ 2011-11-03 22:42 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On 11/03/2011 09:40 AM, Robert Haas wrote:
> On Thu, Nov 3, 2011 at 11:31 AM, Rodrigo Gonzalez
> <rjgonzale@estrads.com.ar> wrote:
>> El 03/11/11 11:42, Robert Haas escribió:
>>
>> On Fri, Oct 28, 2011 at 9:39 AM, CS DBA<cs_dba@consistentstate.com> wrote:
>>
>> No parameters, one of them looks like this:
>>
>> [ code snippet ]
>>
>> It's hard to believe this is the real code, because SELECT without
>> INTO will bomb out inside a PL/pgsql function, won't it?
>>
>> But he's using CREATE TABLE xyz_view_m AS
>>
>> So it seems correct to me
> Oh, right, I missed that.
>
> That seems pretty mysterious then. But is it possible the function is
> getting called more times than it should? I notice that it's set up
> as a trigger; is it FOR EACH ROW when it should be a statement-level
> trigger or something like that? Maybe run EXPLAIN ANALYZE on the
> query that's invoking the trigger to get some more detail on what's
> going on?
I'll give it a shot ...
--
---------------------------------------------
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
---------------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-11-03 22:42 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-28 04:54 function slower than the same code in an sql file CS DBA <cs_dba@consistentstate.com>
2011-10-28 05:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-10-28 13:39 ` CS DBA <cs_dba@consistentstate.com>
2011-11-03 14:42 ` Robert Haas <robertmhaas@gmail.com>
2011-11-03 15:31 ` Rodrigo Gonzalez <rjgonzale@estrads.com.ar>
2011-11-03 15:40 ` Robert Haas <robertmhaas@gmail.com>
2011-11-03 22:42 ` CS DBA <cs_dba@consistentstate.com>
2011-10-28 05:10 ` Pavel Stehule <pavel.stehule@gmail.com>
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