On Wed, Feb 29, 2012 at 3:37 PM, Igor Schtein <ischtein@gmail.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