Skip site navigation (1) Skip section navigation (2)

Re: plpgsql functions vs. embedded queries

From: "Wright, George" <George(dot)Wright(at)infimatic(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "John DeSoi" <desoi(at)pgedit(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: plpgsql functions vs. embedded queries
Date: 2008-09-30 13:25:33
Message-ID: 51548D6D5BEB57468163194A8C1A0E980161A5ED@MAGPTCPEXC02.na.mag-ias.net (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2008-09-30 13:37:14
Subject: Re: plpgsql functions vs. embedded queries
Previous:From: Shabala DeshpandeDate: 2008-09-30 09:12:08
Subject: !!! Compress individual tables with postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group