On Wed, Feb 29, 2012 at 3:37 PM, Igor Schtein <ischtein@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
