Re: On the performance of views

Bill,

> > SELECT a.cola, b.colb, c.colc
> > FROM a JOIN b JOIN c
> > WHERE a.prikey=$1

If your views are simple, PostgreSQL will be able to "push down" any filter
criteria into the view itself.   For example,

CREATE view_a AS
SELECT a.cola, b.colb, c.colc
FROM a JOIN b JOIN c;

SELECT * FROM view_a
WHERE a.prikey = 2334432;

will execute just like:

SELECT a.cola, b.colb, c.colc
FROM a JOIN b JOIN c
WHERE a.prikey = 2334432;

However, this does not work for really complex views, which have to be
materialized or executed as a sub-loop.

The "Procedures faster than views" thing is a SQL Server peculiarity which is
a result of MS's buggering up views since they bought the code from Sybase.

> To my understanding, views are expanded at runtime and considered while
> preparing plan for the complete (and possibly bigger) query(Consider a view
> joined with something else). That is not as easy/possible if at all, when it
is
> function. For postgresql query planner, the function is a black box(rightly
so,
> I would say).

Well, as of 7.4 SQL functions are inlined.   And simple PL/pgSQL functions
will be "prepared".   So it's possible that either could execute as fast as a
view.

Also, if your client is really concerned about no-holds-barred speed, you
should investigate prepared queries.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco