I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system.
Obviously when I query my main (non-aggregated) table, the planner uses any indexes it deems appropriate. Does that extend to materialized views? If I query the main table and the query involves aggregates, does it recognize that my materialized view already has the values it needs?
Best Answer
No. How would the query processor recognize that it should use the materialized view. The materialized view is another object.
See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html
In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view."
So materialized views are useful, but you need to program not only for their maintenance, but also doing the necessary coding to use the materialized view.