Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When refreshing a materialized view, are indexes also refreshed automatically?

I'm currently using Postgres 9.3.3.

If you refresh a materialized view, are the indexes on that materialized view also reindexed? Or do the indexes need to be manually reindexed?

In looking at the following question, it looks like it but there is no answer to it and I cannot seem to find any supporting documentation.

like image 239
thames Avatar asked Mar 21 '14 15:03

thames


2 Answers

The manual:

Once an index is created, no further intervention is required: the system will update the index when the table is modified ...

No exceptions. A materialized view is just another table with attached recipe how to refresh it. All indexes are either recreated from scratch or updated. You never need to do anything about existing indexes manually.

The manual once more:

When a materialized view is referenced in a query, the data is returned directly from the materialized view, like from a table; the rule is only used for populating the materialized view.

like image 69
Erwin Brandstetter Avatar answered Dec 15 '22 22:12

Erwin Brandstetter


The indexes will of course be kept up-to-date.

A quick test seems to indicate they maintain their size after each REFRESH too, which suggests they are being recreated from scratch. That would make sense because I think the view is created anew for each refresh and basically renamed into place.

like image 30
Richard Huxton Avatar answered Dec 15 '22 23:12

Richard Huxton