Обсуждение: plpgsql functions vs. embedded queries
Are funtions in plpgsql always slower than embedding sql queries in string form in code like PHP?
I ran several tests and they seemed to always perform that way. I would have thought the pre-planning would have made them faster.
Would re-writing the functions C make them faster than the embedded queries?
On Sep 29, 2008, at 9:50 AM, Wright, George wrote: > Are funtions in plpgsql always slower than embedding sql queries in > string form in code like PHP? > I ran several tests and they seemed to always perform that way. I > would have thought the pre-planning would have made them faster. > Would re-writing the functions C make them faster than the embedded > queries? I'd say no, they should not always be slower. Did you try preparing your PHP call to the function? Did you mark your function as STABLE if you are not modifying the database? John DeSoi, Ph.D.
I did neither but will try both, thank you. The functions took on average more than 10 times as long. -----Original Message----- From: John DeSoi [mailto:desoi@pgedit.com] Sent: Monday, September 29, 2008 1:16 PM To: Wright, George Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] plpgsql functions vs. embedded queries On Sep 29, 2008, at 9:50 AM, Wright, George wrote: > Are funtions in plpgsql always slower than embedding sql queries in > string form in code like PHP? > I ran several tests and they seemed to always perform that way. I > would have thought the pre-planning would have made them faster. > Would re-writing the functions C make them faster than the embedded > queries? I'd say no, they should not always be slower. Did you try preparing your PHP call to the function? Did you mark your function as STABLE if you are not modifying the database? John DeSoi, Ph.D.
"Wright, George" <George.Wright@infimatic.com> writes:
>> Are funtions in plpgsql always slower than embedding sql queries in
>> string form in code like PHP?
> The functions took on average more than 10 times as long.
That suggests that you're getting a radically different, less efficient
plan for the "same" query inside a function. The exact reasons why are
hard to diagnose without a concrete example, but usually the story has
to do with comparing parameterized queries inside a function to
not-parameterized queries elsewhere. There are various workarounds
but the best choice depends on details you've not shown us.
regards, tom lane
Hope this isn't too much detail. The prepared statement was barely
faster and the raw stored proc was much slower.
An example run:
Duration for 17 native embedded queries : 0.15884709358215 seconds
Duration for 17 native prep-stmt queries : 0.13436007499695 seconds
Duration for 17 native stored-proc queries : 3.0338139533997 seconds
-- Stored Procedure:
CREATE OR REPLACE FUNCTION TestStoredProc(integer, text, text) RETURNS
integer AS $$
BEGIN
PERFORM a.id,
EXTRACT(EPOCH FROM ad.start_time),
EXTRACT(EPOCH FROM ad.end_time - ad.start_time) + 1 as duration,
a.code, a.description, a.severity
FROM alert_data AS ad
INNER JOIN alerts AS a
ON ad.alert_id = a.id
WHERE ad.asset_id = $1
AND ad.start_time >= $2
AND ad.start_time < $3;
RETURN 0;
END
$$ LANGUAGE 'plpgsql';
PHP:
$query = "Select TestStoredProc(710006, '2008-09-01', '2008-09-10');";
$result = pg_query($dbconn, $query);
-- Prepared Statement:
$prep1 = "SELECT a.id,
EXTRACT(EPOCH FROM ad.start_time),
EXTRACT(EPOCH FROM ad.end_time - ad.start_time) + 1 as duration,
a.code, a.description, a.severity
FROM alert_data AS ad
INNER JOIN alerts AS a ON ad.alert_id = a.id
WHERE ad.asset_id = $1 AND ad.start_time >= $2 AND ad.start_time < $3;";
PHP:
pg_prepare($dbconn, 'prep1', $prep1);
$data = array(710006, '2008-09-01', '2008-09-10');
$result = pg_execute($dbconn, 'prep1', $data);
Table structure:
civet=> \d alerts
Table "public.alerts"
Column | Type | Modifiers
-------------+-----------------------+----------------------------------
------------------------
id | integer | not null default
nextval(('alerts_seq'::text)::regclass)
code | character varying(30) | not null
description | text | not null default 'NO DESCRIPTION
PROVIDED'::text
severity | character varying(5) | not null default 0
Indexes:
"alerts_pkey" PRIMARY KEY, btree (id)
civet=> \d alert_data
Table "public.alert_data"
Column | Type |
Modifiers
------------+--------------------------+--------------------------------
------------------------------
id | integer | not null default
nextval(('alert_data_seq'::text)::regclass)
asset_id | integer | not null
alert_id | integer | not null
start_time | timestamp with time zone | not null
end_time | timestamp with time zone |
Indexes:
"alert_data_pkey" PRIMARY KEY, btree (id)
"alert_data_idx1" btree (end_time, start_time, asset_id) CLUSTER
"alert_data_idx2" btree (asset_id, start_time, end_time)
Foreign-key constraints:
"alert_data_fkalert_id" FOREIGN KEY (alert_id) REFERENCES alerts(id)
MATCH FULL
"alert_data_fkasset_id" FOREIGN KEY (asset_id) REFERENCES assets(id)
MATCH FULL
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 29, 2008 7:07 PM
To: Wright, George
Cc: John DeSoi; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] plpgsql functions vs. embedded queries
"Wright, George" <George.Wright@infimatic.com> writes:
>> Are funtions in plpgsql always slower than embedding sql queries in
>> string form in code like PHP?
> The functions took on average more than 10 times as long.
That suggests that you're getting a radically different, less efficient
plan for the "same" query inside a function. The exact reasons why are
hard to diagnose without a concrete example, but usually the story has
to do with comparing parameterized queries inside a function to
not-parameterized queries elsewhere. There are various workarounds
but the best choice depends on details you've not shown us.
regards, tom lane
"Wright, George" <George.Wright@infimatic.com> writes:
> Hope this isn't too much detail. The prepared statement was barely
> faster and the raw stored proc was much slower.
Well, here's your problem:
> CREATE OR REPLACE FUNCTION TestStoredProc(integer, text, text) RETURNS
$2 and $3 presumably ought to be timestamp with time zone, not text.
In the prepared statement those parameters default to being of the same
type as what they're compared to. Here, you've forced a textual
comparison to occur, which doesn't match the index on alert_data,
so you end up with a slow seqscan ... and possibly not even the
right answers, if the supplied dates are formatted at all strangely.
(8.3 would have saved you from this mistake, btw, because it won't
do implicit casts to text.)
regards, tom lane
Thank you! That's a great reason to upgrade.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, September 30, 2008 9:37 AM
To: Wright, George
Cc: John DeSoi; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] plpgsql functions vs. embedded queries
"Wright, George" <George.Wright@infimatic.com> writes:
> Hope this isn't too much detail. The prepared statement was barely
> faster and the raw stored proc was much slower.
Well, here's your problem:
> CREATE OR REPLACE FUNCTION TestStoredProc(integer, text, text) RETURNS
$2 and $3 presumably ought to be timestamp with time zone, not text.
In the prepared statement those parameters default to being of the same
type as what they're compared to. Here, you've forced a textual
comparison to occur, which doesn't match the index on alert_data,
so you end up with a slow seqscan ... and possibly not even the
right answers, if the supplied dates are formatted at all strangely.
(8.3 would have saved you from this mistake, btw, because it won't
do implicit casts to text.)
regards, tom lane