Re: Drastic performance loss in assert-enabled build in HEAD

On Fri, Apr 05, 2013 at 11:17:30AM +0200, Nicolas Barbier wrote:
> 2013/4/5 Noah Misch <noah(at)leadboat(dot)com>:
> > On Thu, Apr 04, 2013 at 12:28:01PM +0200, Nicolas Barbier wrote:
> >> +1. Having unlogged matviews without having incremental updates yet,
> >> isn't super useful anyway.
> >
> > I would have surmised the opposite: since an unlogged MV requires a full
> > refresh at unpredictable moments, logged MVs will be preferred where a refresh
> > is prohibitively expensive.
>
> That sounds like a good reason to choose your matview to be logged indeed.
>
> I.e., very expensive to rebuild → choose logged
>
> The opposite is also true: If your matview is not so expensive to
> rebuild, why would it matter that much if it is logged? (Rebuilding
> would be a tad slower, but it is not that slow to start with, so who
> cares?)
>
> I.e., not so expensive to rebuild → logged or unlogged are fine
>
> This would mean “always choose logged,” except for the restricted case
> of “incremental updates of a matview that is not so expensive to
> rebuild” that I describe next:

Cheap is good, but cheaper is better. Since a refresh locks out readers, mild
wins do count.

> > Why might unlogged-MV applications desire incremental updates more acutely
> > than logged-MV applications?
>
> My reasoning was more like: If you have incremental updates, there
> will probably be some overhead linked to executing any transaction
> that updates the base tables, namely for storing the changesets
> somewhere. I imagined it could at least be this storing of changesets
> that one would want to be unlogged, lest it slowing down the commit of
> most transactions that don’t even touch the matview.

That's a good point. However, the same holds when refreshing a logged MV,
especially at wal_level > minimal. The refresh exerts pressure on the WAL
stream, likely delaying other WAL-using transactions.

> As a sidenote, I see two ways to avoid storing changesets as part of a
> commit that changes the base tables. Choosing any of those would
> invalidate my previous logic, and even more diminish the need for
> unlogged matviews:

Regardless of the change storage method, actually applying incremental changes
to a logged MV will require WAL. UNLOGGED MVs will stay relevant.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com