pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of SQL Function versus View
3+ messages / 3 participants
[nested] [flat]
* Performance of SQL Function versus View
@ 2012-02-29 20:37 Igor Schtein <ischtein@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Igor Schtein @ 2012-02-29 20:37 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Performance of SQL Function versus View
@ 2012-04-03 14:21 Robert Haas <robertmhaas@gmail.com>
parent: Igor Schtein <ischtein@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Haas @ 2012-04-03 14:21 UTC (permalink / raw)
To: Igor Schtein <ischtein@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Performance of SQL Function versus View
@ 2012-04-03 14:30 Andrew Dunstan <andrew@dunslane.net>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andrew Dunstan @ 2012-04-03 14:30 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Igor Schtein <ischtein@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-04-03 14:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-29 20:37 Performance of SQL Function versus View Igor Schtein <ischtein@gmail.com>
2012-04-03 14:21 ` Robert Haas <robertmhaas@gmail.com>
2012-04-03 14:30 ` Andrew Dunstan <andrew@dunslane.net>
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