From: Igor Schtein <ischtein@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Performance of SQL Function versus View
Date: Wed, 29 Feb 2012 12:37:56 -0800
Message-ID: <021a01ccf722$059a2a80$10ce7f80$@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ischtein@gmail.com
Subject: Re: Performance of SQL Function versus View
In-Reply-To: <021a01ccf722$059a2a80$10ce7f80$@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox