From ischtein@gmail.com Wed Feb 29 20:38:12 2012 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-- From robertmhaas@gmail.com Tue Apr 3 14:21:27 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 46083DE314D for ; Tue, 3 Apr 2012 11:21:27 -0300 (ADT) Received: from mail-gx0-f174.google.com ([209.85.161.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SF4br-0006uJ-Fv for pgsql-performance@postgresql.org; Tue, 03 Apr 2012 14:21:26 +0000 Received: by gghe5 with SMTP id e5so1771392ggh.19 for ; Tue, 03 Apr 2012 07:21:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=b5WjzCxkzDUtp5cyHCcv996VFAlkZz2vBILIKOrHvIo=; b=O5d87Gl2Fv5/jExu/IuDySriQF8t+aGiUQl7/noSrZlYMcKzP2IIqRNeoMgnvvONR8 Rmoeg/7VBM0wGJky79BbtnnKf3OZVz5i31exwlrltn/TCCUpFOSPZ7nFWcPWTjxvc5X0 LE0HnHj2yuBWCITS5l5jHOczABNtgeY7WdWz8j9Rmc8F4D9J67oSv8vv0isaN9DsFKsg 2a4qQ+hlas4kS7dL+5ubhvwV0/UF/wPkRLSxdhEcGiUO5TDNkFMyu2+UvwL7crs1Y1q2 LQTWRoY8oSs6t2TS581NaFjfZm/+HN5s8TJMWIioXAoBkG4FJt0g6UvHv4H1HETP1UUD iERg== MIME-Version: 1.0 Received: by 10.50.189.135 with SMTP id gi7mr2005971igc.37.1333462870176; Tue, 03 Apr 2012 07:21:10 -0700 (PDT) Received: by 10.50.102.10 with HTTP; Tue, 3 Apr 2012 07:21:10 -0700 (PDT) In-Reply-To: <021a01ccf722$059a2a80$10ce7f80$@gmail.com> References: <021a01ccf722$059a2a80$10ce7f80$@gmail.com> Date: Tue, 3 Apr 2012 10:21:10 -0400 Message-ID: Subject: Re: Performance of SQL Function versus View From: Robert Haas To: Igor Schtein Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201204/16 X-Sequence-Number: 46662 On Wed, Feb 29, 2012 at 3:37 PM, Igor Schtein wrote: > Do you see any performance difference between the following approaches? T= he > assumption is that most of the rows in the query will be requested often > enough. > > > > 1.=A0=A0=A0=A0=A0=A0 SQL function. > > CREATE OR REPLACE FUNCTION X(IN a_id uuid, IN b_id uuid) > > =A0RETURNS int > > =A0 STABLE > > AS $$ > > =A0 SELECT count(1) > > =A0=A0=A0=A0 FROM A, B > > =A0=A0=A0=A0 WHERE a_join_id =3D b_join_id > > =A0=A0=A0=A0=A0=A0 AND A.a_id =A0=3D a_id > > =A0=A0=A0=A0=A0=A0 AND B.b_id =3D b_id; > > $$ LANGUAGE SQL; > > > > SELECT X(a_id, b_id); > > > > 2.=A0=A0=A0=A0=A0=A0 View. > > CREATE OR REPLACE VIEW X AS > > =A0=A0SELECT a_id, b_id, count(1) cnt > > =A0=A0=A0=A0 FROM A, B > > =A0=A0=A0=A0 WHERE a_join_id =3D b_join_id > > GROUP BY (a_id, b_id) > > > > SELECT cnt FROM =A0X WHERE X.a_id =3D a_id and X.B_id =3D 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company From andrew@dunslane.net Tue Apr 3 14:30:33 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 34E8CDE314D for ; Tue, 3 Apr 2012 11:30:33 -0300 (ADT) Received: from relay4-d.mail.gandi.net ([217.70.183.196]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SF4kd-0000gA-54 for pgsql-performance@postgresql.org; Tue, 03 Apr 2012 14:30:32 +0000 X-Originating-IP: 217.70.178.131 Received: from mfilter11-d.gandi.net (mfilter11-d.gandi.net [217.70.178.131]) by relay4-d.mail.gandi.net (Postfix) with ESMTP id 6957F1720A1; Tue, 3 Apr 2012 16:30:04 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at mfilter11-d.gandi.net Received: from relay4-d.mail.gandi.net ([217.70.183.196]) by mfilter11-d.gandi.net (mfilter11-d.gandi.net [10.0.15.180]) (amavisd-new, port 10024) with ESMTP id tZt22ppmNHYd; Tue, 3 Apr 2012 16:30:03 +0200 (CEST) X-Originating-IP: 98.27.54.176 Received: from [192.168.10.146] (cpe-098-027-054-176.nc.res.rr.com [98.27.54.176]) (Authenticated sender: adsend@dunslane.net) by relay4-d.mail.gandi.net (Postfix) with ESMTPSA id 67105172092; Tue, 3 Apr 2012 16:30:02 +0200 (CEST) Message-ID: <4F7B0968.5000805@dunslane.net> Date: Tue, 03 Apr 2012 10:30:00 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.1) Gecko/20120216 Thunderbird/10.0.1 MIME-Version: 1.0 To: Robert Haas CC: Igor Schtein , pgsql-performance@postgresql.org Subject: Re: Performance of SQL Function versus View References: <021a01ccf722$059a2a80$10ce7f80$@gmail.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/17 X-Sequence-Number: 46663 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