[PERFORM] Performance of SQL Function versus View

[Igor Schtein]

Do you see any performance difference between the following approaches? The assumption is that most of the rows in the query will be requested often enough.

 

1.       SQL function.

CREATE OR REPLACE FUNCTION X(IN a_id uuid, IN b_id uuid)

 RETURNS int

  STABLE

AS $$

  SELECT count(1)

     FROM A, B

     WHERE a_join_id = b_join_id

       AND A.a_id  = a_id

       AND B.b_id = b_id;

$$ LANGUAGE SQL;

 

SELECT X(a_id, b_id);

 

2.       View.

CREATE OR REPLACE VIEW X AS

  SELECT a_id, b_id, count(1) cnt

     FROM A, B

     WHERE a_join_id = b_join_id

GROUP BY (a_id, b_id)

 

SELECT cnt FROM  X WHERE X.a_id = a_id and X.B_id = b_id;

 

Thank you,

Igor

[Robert Haas]

You should probably test this in your environment, but I'd expect the
view to be better. Wrapping logic inside PL/pgsql functions
needlessly rarely turn outs to be a win.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-performance mailing list (pgsql-pe...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Andrew Dunstan]

On 04/03/2012 10:21 AM, Robert Haas wrote:
>
> You should probably test this in your environment, but I'd expect the
> view to be better. Wrapping logic inside PL/pgsql functions
> needlessly rarely turn outs to be a win.

Right, But also note that auto_explain is very useful in getting plans
and times of queries nested in functions which can't easily be got
otherwise.

cheers

andrew