Thread: [GENERAL] Materialized view vs. view

[GENERAL] Materialized view vs. view

From
Job
Date:
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.
 
Thank you!
/F

Re: [GENERAL] Materialized view vs. view

From
Adrian Klaver
Date:
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


Re: [GENERAL] Materialized view vs. view

From
"David G. Johnston"
Date:
On Tue, Jan 10, 2017 at 11:27 AM, Job <Job@colliniconsulting.it> wrote:
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.


There is no simple answer to this - it all depends upon your actual usage.  With proper indexes and a disregard for the "REFRESH MATERIALIZED VIEW ..." command cost the materialized view should always perform better.  But most people don't get to disregard the refresh dynamic imposed by materialized views.

David J.

Re: [GENERAL] Materialized view vs. view

From
Melvin Davidson
Date:


On Tue, Jan 10, 2017 at 1:36 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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

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,
so theoretically you should be able to reduce response time on them.

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Materialized view vs. view

From
Kevin Grittner
Date:
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

Re: [GENERAL] Materialized view vs. view

From
Melvin Davidson
Date:


On Tue, Jan 10, 2017 at 2:31 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
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.
--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Materialized view vs. view

From
"David G. Johnston"
Date:
On Tue, Jan 10, 2017 at 12:36 PM, Melvin Davidson <melvin6925@gmail.com> wrote:


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

​Sounds redundant - and implies that a TABLE without the materialized prefix isn't, which is not true.

The only other name I came up with was worse...CREATE VIEWTABLE​ AS

David J.

Re: [GENERAL] Materialized view vs. view

From
Kevin Grittner
Date:
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

Re: [GENERAL] Materialized view vs. view

From
Melvin Davidson
Date:

On Tue, Jan 10, 2017 at 2:53 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
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


"A rose by any other name would still smell as sweet".
I have expressed my opinion. We are getting off the point of the topic which is "What is faster, a View or a Materialized View".
Can we all agree that the "Materialized View" should be faster and stop this pointless bickering about naming convention,
which I have already stated, is just an opinion and too late to change at this point?

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Materialized view vs. view

From
Rob Sargent
Date:
>
>
> "A rose by any other name would still smell as sweet”.
Actually there’s no “still” in that line, if you’re quoting Shakespeare.  And the full “That which we call a rose …” is
trulyappropriate here. 



Re: [GENERAL] Materialized view vs. view

From
"David G. Johnston"
Date:
On Tue, Jan 10, 2017 at 1:01 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

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

 
If you add in the condition that the same answer has to be returned whether you query the view or the mat-view then no, I wouldn't agree; and the original question is, IMO, unanswerable (or at least, if answered, requires making assumptions that tend to go unstated).

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


Your opinion, or the name? :)

​David J.​

Re: [GENERAL] Materialized view vs. view

From
Kevin Grittner
Date:
On Tue, Jan 10, 2017 at 2:01 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

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

I think we have.

> and stop this pointless bickering about naming convention,
> which I have already stated, is just an opinion and too late to change at this point?

Novel opinions about what words mean can lead to confusion.  Left
alone, what you said might have confused some readers about what
"materialized" means.  "Materialized view" has been a term of art,
part of database jargon, for over 30 years.

https://en.wikipedia.org/wiki/Jargon :

"A main driving force in the creation of technical jargon is
precision and efficiency of communication when a discussion must
easily range from general themes to specific, finely differentiated
details without circumlocution."

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

Re: [GENERAL] Materialized view vs. view

From
Nicolas Paris
Date:
Le 10/33/2017 à 21:33, David G. Johnston écrivait :
>    On Tue, Jan 10, 2017 at 1:01 PM, Melvin Davidson
>    <[1]melvin6925@gmail.com> wrote:
>
>    Can we all agree that the "Materialized View" should be faster
>    ​
>
Yes.
The OP told about a 500K rows view. Every select queries on that view will
have to fetch those 500K rows before - in any case this is quite slow.
However, 500K rows do not represent a huge physical space to
materialize. But do not forget to index/refresh the materialized views,
depending on the select set of queries to run.

Answer would have been different with 50K rows I guess.

>
>    If you add in the condition that the same answer has to be returned
>    whether you query the view or the mat-view then no, I wouldn't agree;
>    and the original question is, IMO, unanswerable (or at least, if
>    answered, requires making assumptions that tend to go unstated).
>
>     is just an opinion and too late to change at this point?
>
>    Your opinion, or the name? :)
>    ​David J.​
>
> References
>
>    1. mailto:melvin6925@gmail.com