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