Re: counting algorithm for incremental matview maintenance

Kevin,

> The count_t column saves us from having to scan foo for all the old
> val values. It does not require any scan of the entire bar
> matview. It allows us to zero in on exactly the right rows, and
> lets us know what needs doing.

This sounds like a fairly good approach. It would require a couple of
things though:

1) admins would need to be able to enable and disable incremental
updating of matviews, so that if the creation of delta tables is bogging
down writes, they can disable them temporarily as a performance workaround.

2) if an admin enables incremental updating of an existing matview, it
would need to be REFRESHed immediately before we could start
incrementally updating it. So an ENABLE should call a REFRESH.

> Hopefully this makes it fairly clear that the only thing that an
> optimization around the "append-only" assertion for a matview would
> be the ability to skip the probe for an existing record *related to
> rows which are in the delta*. As long as there is reasonable
> indexing on the matview, maintenance for the append-only case would
> not involve scanning the entire matview.

Yeah, given what you've explained, my first inclination would be just to
let the counting do its thing and see how slow/expensive it is before we
try further optimizations.

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