Re: Performance of SQL Function versus View

On Wed, Feb 29, 2012 at 3:37 PM, Igor Schtein <ischtein(at)gmail(dot)com> wrote:
> 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;

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