Re: Postgresql Materialized views

Mark Mielke wrote:
> Counts, because as we all know, PostgreSQL count(*) is slow, and in
> any case, my count(*) is not on the whole table, but on a subset.
> Doing this in a general way seems complex to me as it would need to be
> able to evaluate whether a given INSERT or UPDATE or one of the
> dependent tables would impact the WHERE clause for the materialized
> view, and it still wouldn't know which rows to add/update/remove
> without detailed analysis, so it would either be throwing out the
> entire materialized view and recreating it on INSERT or UPDATE (or
> deferring until the next query?) in which case it may be very slow, or
> it may be very complex.

Bah. I forgot to add: The feature I've been wondering about (and not
necessarily looking for somebody else to do, although I don't think I
know the code well enough to do it at this point):

Web applications often make the same queries over and over. While
memcache can be used to cache results, the memcache interface is
different from the web application interfere requiring complex code, and
as well, one loses the transaction guarantees as the memcache results
are not guaranteed to be up-to-date with the database. I see the
greatest overall performance gain for web applications to be for
PostgreSQL to hang on to the results of the previous X queries along
with transactions numbers of each of the dependent tables as of the
snapshot of the table that is used, and if one of them matches, return
the results immediately. I believe MySQL does this (although not sure
how reliable their implementation is). I believe I have seen this
subject talked about on this list in the past. For web applications, I
believe this gives most of the benefits that materialized views would
provide, with less of the costs?

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>