5

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

2 Answers 2

12

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.

2
  • but when you refresh a materialized view, wont it just drop everything and recalculate? lets say you are storing likes and dislikes counts in a mat view. a person likes a post so you just need to add 1 more like to a single post id but the mat view ll recompute votes for every post correct?
    – PirateApp
    Sep 12, 2022 at 2:24
  • 1
    @PirateApp: The MV never refreshes by itself. Only when you tell it to do so, with REFRESH MATERIALIZED VIEW. But yes, the whole table is re-computed. Notably, if very few rows actually change, using REFRESH MATERIALIZED VIEW CONCURRENTLY is typically faster, as it computes the new table in the background and then updates rows that actually changed. Sep 12, 2022 at 3:48
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.