Performance of SQL Function versus View

From: "Igor Schtein" <ischtein(at)gmail(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Performance of SQL Function versus View
Date: 2012-02-29 20:37:56
Message-ID: 021a01ccf722$059a2a80$10ce7f80$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Marc Mamin 2012-02-29 20:40:22 Re: text search: tablescan cost for a tsvector
Previous Message Robert Haas 2012-02-29 18:45:25 Re: Vacuuming problems on TOAST table