Hi! On Fri, Dec 21, 2018 at 11:10 PM George Neuner <gneuner2@xxxxxxxxxxx> wrote: > A materialized view IS exactly such a deliberate cache of results from > applying a view. It is a real table that can be monitored for changes > using INSERT, UPDATE and/or DELETE triggers. Caching is needed if you want to compute a difference between previous version and new. But if you want to just know new value, then I could imagine that (a simple implementation would) on every change to any underlying table check if this change matches selectors of the query and if such apply its operations/projections and produce the new value. So yes, you need caching if you want to decrease CPU use, but you could also see it as new values being computed again and again through query. Would such caching you are mentioning really improve performance, I do not know, so it might be premature optimization? If we do not go down the cache path, then it seems there is no other way to have this "apply this query again on those updated rows from table". In a way I see query as a transformation (in relational algebra) of original tables to results and I would need to be able to apply that transformation again on new rows. And if some additional data is necessary (some rows for other non-changed tables) it would just recompute that again, instead of using cache. If we do go down the cache path, then I agree, materialized views seems nice, but I would prefer temporary materialized views: they should be cleaned up at the end of the session. Moreover, they should be ideally just in memory, not really on disk. Materialized views are currently stored to disk, no? > With a materialized view, you must apply the view code again (call > REFRESH) to see changes to the underlying tables - it doesn't happen > automagically. But when refreshed, triggers on the cache table would > react to changes. So you are saying I could use triggers on the cache table to know what really changed instead of having to compute diff myself? Interesting. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m