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
