Re: counting algorithm for incremental matview maintenance

Kevin,

It's fairly common for matviews to be constructed such that updates to
them are strictly appends. For example, a matview which has a daily
summary would just get appended to each day, and existing rows would not
change barring a major historical database cleanup.

It seems like we could ... and ought to ... optimize for this pattern
somehow for incremental updates. That is, if the user knows that we're
going to be only appending new rows and not modifying any old ones, s/he
ought to be able to tell the database that somehow and avoid the
overhead of checking. While the overhead of checking a count wouldn't
be that high for a few hundred rows, I've dealt with matviews which were
thousands to millions of rows.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com