Postgresql – Materialized views Performance

materialized-viewpostgresql

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

Best Answer

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.

Related Question