Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 12:44 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

> fyi, a view is nothing more than just that, a view.
> A materialized view, afaic, is a misleading name, it is actually
> a valid table and you can create indexes on them,

I disagree with the notion that defining a relation in terms of a
query (like a view) and materializing the results (like a table)
makes "materialized view" a misleading name.  I don't think I can
say it better than others already have, so I recommend reading the
first three paragraphs of the "Introduction" section of this paper:

http://homepages.inf.ed.ac.uk/wenfei/qsx/reading/gupta95maintenance.pdf
Ashish Gupta and Inderpal Singh Mumick.
Maintenance of Materialized Views: Problems, Techniques, and Applications.

> so theoretically you should be able to reduce response time on
> them.

As the above-referenced text suggests, a materialized view is
essentially a cache of the results of the specified query.  While,
in rare cases, this may be captured to provide the query results as
of some particular moment in time, the overwhelming reason for
creating a materialized view is to improve performance over a
non-materialized view.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company