Re: Postgresql Materialized views

On Wed, 2008-01-16 at 11:16 +0000, Heikki Linnakangas wrote:
> Merlin Moncure wrote:
> > On Jan 12, 2008 4:19 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:
> >>
> >>> Please pick-up this important issue for developpers. There is no need to
> >>> concentrate on complex issues, when handling materialized views could
> >>> boost somme web apps. by a factor of 10 or more.
> >> It's more complex than you think, but the main reason was that HOT was a
> >> prerequisite for making summary tables work efficiently, which is only
> >> now just about to go live into 8.3
> >
> > +1
>
> I don't quite agree with that. HOT certainly speeds up UPDATEs on small
> tables, like you a summary table, but there's a lot of use cases like
> data warehousing, where the summary tables are not updated that often
> for the updates to become a bottleneck.

I should have said that was *my* reason for not doing it sooner.

My thinking was if you load a 1000 rows and they all have the same key
in your summary table then you'll be doing 1000 updates on a single row.
With HOT that will cause the block to fill up and then we wipe out the
previously updated rows, since they are inserted and deleted in same
transaction. So all 1000 updates can occur without going beyond that
single block. Much faster, no mess.

Before HOT, large loads were worse, since there was no chance of
VACUUMing the table between updates.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com