Re: Postgresql Materialized views

Mark Mielke wrote:
> 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.

Regarding up-to-dateness note that there is a pgfoundry project that
helps there. http://pgfoundry.org/projects/pgmemcache/ The other
advantages of doing the caching outside the database is that (a) the
memory for the cached results don't have to sit in the database machine,
and (b) you can cache post-processed (rendered into HTML or gifs)
fragments rather than raw data.