3

I am new to using Postgres and I have a question concerning Materialized views. I am currently using Postgres 9.6.0 and PGAdmin 4.

I have a query that is composed of a number of joins and when it's executed in PGAdmin, it takes approximately 13 seconds. This is true, considering the correct indices are set.

The next approach is to use Materialized Views. By using Materialized Views and setting the appropriate indices, I would have assumed that running a query would be a lot faster given that the data would be cached/ temporarily stored. However after running the query, it looks like it still takes 13 seconds.....

Unless my understanding of Materialized Views is not correct, can someone please explain why executing the materialized view would take as long as running the query. Could it be PGAdmin causing this issue? Or is there a setting within Postgres that needs to be set perhaps for data to be persistent in the materialized view?

Any advice is appreciated. Thanks.

rm12345
  • 1,089
  • 3
  • 18
  • 32
  • 1
    Are you sure that you are creating a materialized view (CREATE MATERIALIZED VIEW...) and not just a regular view (CREATE VIEW...)? Refreshing the materialized view should take approximately the 13 seconds, but after it is refreshed, selects should be faster. – Jeremy Apr 25 '19 at 20:35
  • 1
    You did change your query to use the MView, did you? Postgres (unlike Oracle) will not magically re-write a query if there is a mview that happens to have the same query. –  Apr 25 '19 at 20:39
  • Hi @Jeremy, yes i did use the CREATE MATERIALIZED VIEW mv_view AS statement. Interestingly enough, running SQL statements against this view didnt help – rm12345 Apr 25 '19 at 21:09
  • Please, if a query is slow **always** include at least the query itself and output of `EXPLAIN (ANALYZE, BUFFERS) `. – Ancoron Apr 26 '19 at 03:52

1 Answers1

1

This is expected behavior for the simple case. This means that the materialized view, which is a result of a query against indexes, performs exactly like the indexes from which it was derived. This makes a lot of sense if you think about it.

Materialized views don't necessarily have better performance than the underlying query for the first access. The complexity of assembly of the data may be trivial. A number of joins that use indices would be much less intensive than a complex set of criteria, or something that requires functional processing or sorting for example.

What you have found in this instance, is that the underlying query is just as good as a materialized view for the same dataset. You might want to consider a simple view for this case.

Kirk Roybal
  • 17,273
  • 1
  • 29
  • 38
  • Interesting. I don't fully understand given that the results of the materialised view would have been cached......therefore shouldn't retrieval be faster? – rm12345 Apr 25 '19 at 21:20
  • materialized views are backed by a table. You can even create indexes against them. After the first access, it is possible that the results are also in shared_buffers. In some cases, this can also help access, but there are no guarantees that it will be cached. – Kirk Roybal Apr 25 '19 at 21:38
  • ok, so just to confirm, it is not always the case that the results are cached? Because that isn't a materialized view. Would it be better to just store the query results into one table, where queries are only executed on that one table? – rm12345 Apr 25 '19 at 22:39
  • You both got some things wrong and @KirkRoybal assumed too much without actually knowing about the original query, the view or the data. This is just wild guessing, sorry. When using materialized views it is possible that accessing it is slower than the query to create it, but that needs to be validated against actual data provided by the OP. – Ancoron Apr 26 '19 at 03:54
  • I answered the question for the simple case, made no assumptions, and said so explicitly. @ancoron Your criticism is not justified. I would challenge you to explain "you both got some things wrong", which suffers from exactly the assumptions that you are accusing others of making. – Kirk Roybal Apr 26 '19 at 14:31
  • @rm12345 , Materialized does not have to be in buffers. In this case, it is materialized to disk first, and potentially to buffers depending on read traffic to that disk entity. If it were materialized only to buffers, it would be transient, which is not the design objective. – Kirk Roybal Apr 26 '19 at 14:34