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@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