Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 424ACAD3D76 for ; Wed, 29 Feb 2012 16:38:12 -0400 (AST) Received: from mail-pw0-f46.google.com ([209.85.160.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S2qHq-0003vd-Br for pgsql-performance@postgresql.org; Wed, 29 Feb 2012 20:38:11 +0000 Received: by pbcup15 with SMTP id up15so5007405pbc.19 for ; Wed, 29 Feb 2012 12:37:57 -0800 (PST) Received-SPF: pass (google.com: domain of ischtein@gmail.com designates 10.68.129.73 as permitted sender) client-ip=10.68.129.73; Authentication-Results: mr.google.com; spf=pass (google.com: domain of ischtein@gmail.com designates 10.68.129.73 as permitted sender) smtp.mail=ischtein@gmail.com; dkim=pass header.i=ischtein@gmail.com Received: from mr.google.com ([10.68.129.73]) by 10.68.129.73 with SMTP id nu9mr11297246pbb.100.1330547877549 (num_hops = 1); Wed, 29 Feb 2012 12:37:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=from:to:subject:date:message-id:mime-version:content-type:x-mailer :thread-index:content-language; bh=mkYTvwXYT0rIFfhsXYzfWL5GroqB6cdZNCfPFPzLGNQ=; b=WfdZJAmwKftjoa9i0NuwSrpvho+Xset2tmGO3tpQcXtwxTvFeVIhPv8t1+bytkeCPQ r2nKVs7dK0DPaYf6LIKP26kSzpM+hkz68pwebP6SbjZ2fhwKrzS5jM9kF/fOCg7u3bnS WdZOHxCXbjPmtAQguiZxU+0aqRri0cIgFhz+g= Received: by 10.68.129.73 with SMTP id nu9mr9433033pbb.100.1330547877476; Wed, 29 Feb 2012 12:37:57 -0800 (PST) Received: from IGORLP (adsl-99-105-53-176.dsl.pltn13.sbcglobal.net. [99.105.53.176]) by mx.google.com with ESMTPS id e6sm19524586pbr.74.2012.02.29.12.37.55 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 29 Feb 2012 12:37:56 -0800 (PST) From: "Igor Schtein" To: Subject: Performance of SQL Function versus View Date: Wed, 29 Feb 2012 12:37:56 -0800 Message-ID: <021a01ccf722$059a2a80$10ce7f80$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_021B_01CCF6DE.F78B35D0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Acz3IezTCy5TeK46SKGot/kC9gZ2kw== Content-Language: en-us X-Pg-Spam-Score: -1.2 (-) X-Archive-Number: 201202/274 X-Sequence-Number: 46446 This is a multipart message in MIME format. ------=_NextPart_000_021B_01CCF6DE.F78B35D0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_021B_01CCF6DE.F78B35D0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D b_join_id

AND A.a_id = =3D a_id

AND B.b_id = =3D 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 =3D = b_join_id

GROUP BY (a_id, = b_id)

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

Thank = you,

Igor

------=_NextPart_000_021B_01CCF6DE.F78B35D0--