Обсуждение: [GENERAL] Materialized view vs. view

Hi guys,

 

i am making some tests with a massive number of "select" queries (only for reading datas) on a view and a materialized view.

We use Postgresql 9.6.1 on a 64bit server.

 

Only for "select" queries, which one is faster and less expensive as resources cost?
The view or the materialized view?

The view has about 500K lines.

On 01/10/2017 10:27 AM, Job wrote:

Hi guys,

iam making some tests with a massive number of "select" queries (only
for reading datas) on a view and a materialized view.
We use Postgresql 9.6.1 on a 64bit server.

Only for "select" queries, which one is faster and less expensive as
resources cost?
The view or the materialized view?

If are running tests as you state above, you should know or am I missing something?

The view has about 500K lines.

Thank you!
/F

--
Adrian Klaver
adrian.klaver@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

>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.

IMHO, I disagree. I feel a better name would be "materialized table".

However, it is too late to change that now. Just my personal opinion.

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

> IMHO, I disagree. I feel a better name would be "materialized
> table".

The dictionary defines "materialize" as meaning "become actual
fact" or "appear in bodily form".  In the database environment, it
generally means that the data is actually stored, rather than being
something which can be generated.  For example, in query execution
the relation produced by an execution node may feed into a
Materialize node if the generated relation is expected to be
scanned multiple times by a higher-level node and scanning a stored
copy of the relation each time is expected to be faster than
regenerating the relation each time.  "Materialized table" would be
redundant; a table is always materialized.  A view is data
generated by running a query. In the simple case, the resulting
relation is not stored, but is regenerated on each reference.  The
"materialized view" feature lets you materialize it, like a table.

If you don't think materializing data means storing a copy of it
for re-use, I'm not sure what you think it means.

This is not to beat up on you, but to try to keep terminology
clear, to facilitate efficient communication.  There are some terms
we have been unable to avoid using with different meanings in
different contexts (e.g., "serialization"); that's unfortunate, but
hard to avoid.  I want to keep it to the minimum necessary by
avoiding creep of other terms to multiple definitions.

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

Can we all agree that the "Materialized View" should be faster

​

 is just an opinion and too late to change at this point?