Materialized views Performance

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?”

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.

StackExchange Database Administrators Q#230665, answer score: 12