query executions

Why would a query in the form of an embedded string in a PHP page
perform about the same as a prepared statement and significantly faster
than the same query in the form of a stored procedure - all called from
PHP pages? (I'm only interested in the database aspect)

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

I tried these in sets with different data for each call, opening the
connection only once for all sets. I would expect the embedded to be the
slowest since I believe the query would have to be planned every time.
We're running Suse 10.2, PG 8.1.5, and the DB and app live on the same
box.

-- 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;";

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