[atombender]

The challenge with materialization views is how to determine how any given row change should modify the view. For simple queries with a predicate or two, this isn't too hard; as you say, it's similar to an index. But views can contain complex joins, which isn't true for indexes (or I've never seen a database that supports joins in indexes), and which makes them much harder to materialize, since you essentially have to track dependencies between rows. Views can also use LIMIT and ORDER BY; you can have a "top 100 most listened to songs" or whatever, and determining whether a view at any point of create/update/delete should include or not include a specific song is also a challenging problem in this space. Window functions, grouping aggregation, and other SQL features also make materializing a hard problem.

[zozbot234]

Generally you would use indexes to speed up a complex join, to the point where the overhead of the join itself would just not matter and there would be no point to having an index on it. Many databases also support partial indexes already, which could go a long way towards supporting the "top # rows" use case.