Querying on top of that gives me the same query plan as before. Trying to create a materialized view out of this fails, because of the subquery expression, I’ll have to express the view in terms of joins, instead. Like this:

But now, when we re-run the materialized view query, we see the results as they were at the creation of the view.

There appears to be a set of options to control that, but the one that I want (RERESH FAST), which update the view as soon as data changes will not work with this query, since it consider it too complex. I didn’t investigate too deeply, but it seems that this is another dead end.

I'm with Justin - relational databases have an existing, proven, built-in mechanism to implement what you're trying to accomplish; they're called triggers.

On one occasion I remember fiddling around with indexed views for a week before giving up and implementing a trigger solution. Quickest, easiest, most sensible and most performant change I ever made to that system.

@Tobi: materialized/indexed views are by definition used in functionality which consumes large buckets of data and processes them for a lengthy period of time for readonly purposes. This alone makes it already not necessary to have the views be 'up to date' at all time, as that's only possible with a live query.

many people think data has to be 'non-stale' at any given moment, but they forget that only some data has to be 'non-stale'. All the rest can be consumed from for example materialized/indexed views. The post count is a good example for data which is totally acceptable to be 'stale': who's going to check whether user X has indeed 103 posts? no-one.

It therefore comes down to:

decide what data elements can be stale

make sure the non-stale data is fast and easy to retrieve and manipulate.

And with 'stale' I don't mean data which is a year old, but for example materialized views (or your own tables with duplicated denormalized data, same thing, if your db doesn't support materialized views) which are refreshed every 10 minutes. For reports about monthly sales etc., that's perfectly fine.

This also leads to the point where one could decide to use two distinct databases instead of 1: one for the non-stale data and one for the stale data, which is for example updated every night.

It's true that this means one has to think about what the data in a system really means at time T and time T+t. But IMHO that's a good thing, it makes one realize that a database is more than just a bucket to store bits in, and actually a machine which can make things really easy.

"many people think data has to be 'non-stale' at any given moment, but they forget that only some data has to be 'non-stale'. All the rest can be consumed from for example materialized/indexed views. The post count is a good example for data which is totally acceptable to be 'stale': who's going to check whether user X has indeed 103 posts? no-one."

@Frans Bouma: Materialized views are not only used for aggregation. You can also save tons of join with them. Example: A forum thread. At least the following tables contribute one row for each post: ForumPosts, Users, UserAvatarImages. Then the resultset has to be sorted by datetime. All of this can be materialized so that the forum page needs exactly one range seek. Very cheap, can do that 2k times per second per core.

Have you tried using count(1) instead of count(*) and seeing if it makes a difference to your execution time? Also if it is ok for the count to be a little stale you could store it as a normal numerical field that gets updated by a batch process (database job in sql server) thats runs every so often?