The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6):
The danger with using views is filtering a query against a view,
expecting to read a very small portion of a very large table. Any filtering should be done within
the view because any filtering against the view itself is applied after the query in the view has
completed execution. Views are typically useful for speeding up the development process but
in the long run can completely kill database performance.
The following is an excerpt from PostgreSQL 9.5 Documentation:
Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate
the details of the structure of your tables, which might change as your application evolves,
behind consistent interfaces.
The two sources seem to contradict each other ("do not design with views" vs. "do design with views").
However, in PG views are implemented using the rule system. So, possibly (and this is my question) any filtering against the view is rewritten as a filter within the view, resulting in a single query execution against the underlying tables.
Is my interpretation correct and PG combines WHERE clauses into and out of the view? Or does it run them separately, one after another? Any short, self contained, correct (compilable), examples?
Best Answer
The book is wrong.
Selecting from a view is exactly as fast or slow as running the underlying SQL statement – you can easily check that using
explain analyze.The Postgres optimizer (and the optimizer for many other modern DBMSes) will be able to push down predicates on the view into the actual view statement – provided this is a simple statement (again, this can be verified using
explain analyze).The "bad reputation" regarding performance stems – I think – from when you overuse views and start building views that use views that use views. Very often that results in statements that do too much compared to a statement that was hand-tailored without the views e.g. because some intermediate tables wouldn't be needed. In almost all cases the optimizer is not smart enough to remove those unneeded tables/joins or to push down predicates over multiple levels of views (this is true for other DBMSes as well).