20

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.

Community
  • 1
  • 1
thames
  • 5,833
  • 6
  • 38
  • 45
  • I'll leave an answer to someone who can quote a reliable source, but 2 thoughts: 1) You could probably construct a fairly decisive test for this: create a query that hits the view's index, then change the underlying data such that the results would change. If refreshing the materialized view shows the correct data, the index has been updated. 2) From a consistency point of view, the only alternative to updating the view would seem to be to invalidate it, in which case you would see the query plan changing. An index that pointed at non-existent data would seem like a very bad idea. – IMSoP Mar 21 '14 at 15:31

2 Answers2

18

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    I believe the OP intends to know if any relevant maintenance needs to be done after a MV is refreshed, not necessarily limited to indexs. As such @thames should note that with the new support for the ["CONCURRENTLY"](https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#REFRESH_MATERIALIZED_VIEW_CONCURRENTLY) keyword in version 9.4, if that keyword is being used, then, as per documentation, the MV "needs VACUUMing due to the ... DELETEs that will leave dead tuples behind". Concurrency support is such a good thing, I would imagine it would be widely adopted, making this good to know. – Michael M May 30 '14 at 18:31
5

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.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51