Materialized views Performance

Question

Postgres’ docs note:

While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current;

https://www.postgresql.org/docs/9.6/rules-materializedviews.html

Why is it “often much faster?”

Answer 1

The data of a MV is stored in a regular table, there is no magic to that. But access is typically (much) faster for multiple possible reasons:

• multiple tables joined already
• smaller row size with only relevant columns for common queries
• pre-computed values
• pre-selected rows, possibly in expensive ways
• possibly much less bloat (fewer dead tuples)
• multiple of the above items result in potentially much smaller table size as compared to underlying tables
• rows physically sorted favorably (clustered), so that queries only have to read few data pages
• size of indexes can be much smaller accordingly
• some kinds of indexes only become possibly this way, like a multicolumn index on columns from multiple underlying tables

In short: most expensive work of sophisticated queries on underlying tables is already done, which allows potentially much faster access.

Answer 2

Materialized Views help you pre-calculate data. If you don't use that tool correctly, it may be slower to use them.

Here is an example:

test=> create table my_table
  (id integer generated always as identity,
   time timestamptz);
CREATE TABLE                                           ^
test=> alter table my_table add constraint pk_my_table primary key (id);
ALTER TABLE
test=> insert into my_table (time)
test->  (select time_series
test(>   from generate_series('2019-02-25','2019-03-25', '1 minutes'::interval) as time_series);
INSERT 0 40321
                                                             ^
test=> explain analyze select * from my_table where time between now() and now() + '1 minute'::interval;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on my_table  (cost=0.00..1125.22 rows=2 width=12) (actual time=0.093..4.642 rows=1 loops=1)
   Filter: (("time" >= now()) AND ("time" <= (now() + '00:01:00'::interval)))
   Rows Removed by Filter: 40320
 Planning Time: 0.132 ms
 Execution Time: 4.657 ms
(5 rows)

test=> create materialized view my_materialized_view as
test-> (select * from my_table);
SELECT 40321
test=> explain analyze select * from my_materialized_view where time between now() and now() + '1 minute'::interval;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on my_materialized_view  (cost=0.00..1218.62 rows=222 width=12) (actual time=0.859..28.983 rows=1 loops=1)
   Filter: (("time" >= now()) AND ("time" <= (now() + '00:01:00'::interval)))
   Rows Removed by Filter: 40320
 Planning Time: 0.352 ms
 Execution Time: 29.053 ms
(5 rows)

If your materialized view is poorly designed (or not design for that particular query) or if you don't have enough data, you may have slower query by using a materialized view.