Re: plpgsql functions vs. embedded queries

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, September 29, 2008 7:07 PM
To: Wright, George
Cc: John DeSoi; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] plpgsql functions vs. embedded queries

"Wright, George" <George(dot)Wright(at)infimatic(dot)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