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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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")
    – user1822
    Commented Nov 21, 2014 at 19:12

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.