Thread: Views

Views

From
David Saracini
Date:
Hello All,

With Postgres, is there anything to be aware of when using/creating views.  Any performance considerations?  In my experience, this is an areas where performance can vary greatly across DBMSs.

Many thanks (in advance)!


Re: Views

From
Joshua Tolley
Date:
On Sat, Jun 13, 2009 at 08:45:22AM -0700, David Saracini wrote:
>    Hello All,
>    With Postgres, is there anything to be aware of when using/creating views.
>     Any performance considerations?  In my experience, this is an areas where
>    performance can vary greatly across DBMSs.
>    Many thanks (in advance)!

Using a view in a query is effectively the same thing as using the view's query
as a subquery. In other words, if view foo is built on query bar, as in CREATE
VIEW foo AS bar, when you say "SELECT * FROM foo", the backend rewrites your
query as "SELECT * FROM (bar) foo". So the mechanics of using a view don't
make any noticeable difference in query parsing. There are sometimes cases
where queries involving several views that reference the same set of tables
can be rewritten more efficiently without views, because the optimizer isn't
clever enough do some of the collapsing that could be done, but that's no
differnet than if you composed a query from a bunch of subqueries.

- Josh / eggyknap

Attachment