Are funtions in plpgsql always slower than embedding sql queries in
string form in code like PHP?

I ran several tests and they seemed to always perform that way. I would
have thought the pre-planning would have made them faster.

Would re-writing the functions C make them faster than the embedded
queries?

Search Discussions

  • John DeSoi at Sep 29, 2008 at 5:16 pm

    On Sep 29, 2008, at 9:50 AM, Wright, George wrote:

    Are funtions in plpgsql always slower than embedding sql queries in
    string form in code like PHP?
    I ran several tests and they seemed to always perform that way. I
    would have thought the pre-planning would have made them faster.
    Would re-writing the functions C make them faster than the embedded
    queries?
    I'd say no, they should not always be slower. Did you try preparing
    your PHP call to the function? Did you mark your function as STABLE if
    you are not modifying the database?


    John DeSoi, Ph.D.
  • Wright, George at Sep 29, 2008 at 5:52 pm
    I did neither but will try both, thank you.

    The functions took on average more than 10 times as long.


    -----Original Message-----
    From: John DeSoi
    Sent: Monday, September 29, 2008 1:16 PM
    To: Wright, George
    Cc: pgsql-novice@postgresql.org
    Subject: Re: [NOVICE] plpgsql functions vs. embedded queries

    On Sep 29, 2008, at 9:50 AM, Wright, George wrote:

    Are funtions in plpgsql always slower than embedding sql queries in
    string form in code like PHP?
    I ran several tests and they seemed to always perform that way. I
    would have thought the pre-planning would have made them faster.
    Would re-writing the functions C make them faster than the embedded
    queries?
    I'd say no, they should not always be slower. Did you try preparing
    your PHP call to the function? Did you mark your function as STABLE if
    you are not modifying the database?


    John DeSoi, Ph.D.
  • Tom Lane at Sep 29, 2008 at 11:07 pm

    "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
  • Wright, George at Sep 30, 2008 at 1:25 pm
    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
    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
  • Tom Lane at Sep 30, 2008 at 1:37 pm

    "Wright, George" <George.Wright@infimatic.com> writes:
    Hope this isn't too much detail. The prepared statement was barely
    faster and the raw stored proc was much slower.
    Well, here's your problem:
    CREATE OR REPLACE FUNCTION TestStoredProc(integer, text, text) RETURNS
    $2 and $3 presumably ought to be timestamp with time zone, not text.
    In the prepared statement those parameters default to being of the same
    type as what they're compared to. Here, you've forced a textual
    comparison to occur, which doesn't match the index on alert_data,
    so you end up with a slow seqscan ... and possibly not even the
    right answers, if the supplied dates are formatted at all strangely.

    (8.3 would have saved you from this mistake, btw, because it won't
    do implicit casts to text.)

    regards, tom lane
  • Wright, George at Sep 30, 2008 at 1:42 pm
    Thank you! That's a great reason to upgrade.


    -----Original Message-----
    From: Tom Lane
    Sent: Tuesday, September 30, 2008 9:37 AM
    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:
    Hope this isn't too much detail. The prepared statement was barely
    faster and the raw stored proc was much slower.
    Well, here's your problem:
    CREATE OR REPLACE FUNCTION TestStoredProc(integer, text, text) RETURNS
    $2 and $3 presumably ought to be timestamp with time zone, not text.
    In the prepared statement those parameters default to being of the same
    type as what they're compared to. Here, you've forced a textual
    comparison to occur, which doesn't match the index on alert_data,
    so you end up with a slow seqscan ... and possibly not even the
    right answers, if the supplied dates are formatted at all strangely.

    (8.3 would have saved you from this mistake, btw, because it won't
    do implicit casts to text.)

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 29, '08 at 1:50p
activeSep 30, '08 at 1:42p
posts7
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase