4

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?

5

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.

  • 4
    Oracle actually does this. When the optimizer sees a query that matches a MVIEW query, it will use the mview instead if that is cheaper (called "query rewrite") – a_horse_with_no_name Nov 21 '14 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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