X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 344F1D1B860; Tue, 24 Feb 2004 21:49:56 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 12267-05; Tue, 24 Feb 2004 17:49:55 -0400 (AST)
Received: from bramble.mmrd.com (unknown [65.217.53.66])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 02038D1B4B7; Tue, 24 Feb 2004 17:49:52 -0400 (AST)
Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100])
	by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id i1OLIPcM030530;
	Tue, 24 Feb 2004 16:18:25 -0500
Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55])
	by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id i1OLmol11668;
	Tue, 24 Feb 2004 16:48:50 -0500
Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP
	(Microsoft Exchange Internet Mail Service Version 5.5.2657.72)
	id XT88H14Y; Tue, 24 Feb 2004 16:48:47 -0500
Subject: Re: [HACKERS] [SQL] Materialized View Summary
From: Robert Treat <xzilla@users.sourceforge.net>
To: Richard Huxton <dev@archonet.com>
Cc: "Jonathan M. Gardner" <jgardner@jonathangardner.net>,
	pgsql-hackers@postgresql.org, pgsql-sql@postgresql.org,
	pgsql-performance@postgresql.org
In-Reply-To: <200402241711.20947.dev@archonet.com>
References: <200402240811.13013.jgardner@jonathangardner.net>
	<200402241711.20947.dev@archonet.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.8 
Date: 24 Feb 2004 16:48:49 -0500
Message-Id: <1077659329.15366.6061.camel@camel>
Mime-Version: 1.0
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/290
X-Sequence-Number: 5814

On Tue, 2004-02-24 at 12:11, Richard Huxton wrote:
> On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote:
> >
> > I've written a summary of my findings on implementing and using
> > materialized views in PostgreSQL. I've already deployed eagerly updating
> > materialized views on several views in a production environment for a
> > company called RedWeek: http://redweek.com/. As a result, some queries
> > that were taking longer than 30 seconds to run now run in a fraction of a
> > millisecond.
> >
> > You can view my summary at
> > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html


have you done much concurrency testing on your snapshot views? I
implemented a similar scheme in one of my databases but found problems
when I had concurrent "refresh attempts".  I ended up serializing the
calls view LOCKing, which was ok for my needs, but I thought potentially
problematic in other cases.

> 
> Interesting (and well written) summary. Even if not a "built in" feature, I'm 
> sure that plenty of people will find this useful. Make sure it gets linked to 
> from techdocs.

Done. :-)

> 
> If you could identify candidate keys on a view, you could conceivably automate 
> the process even more. That's got to be possible in some cases, but I'm not 
> sure how difficult it is to do in all cases.
>

it seems somewhere between Joe Conways work work arrays and polymorphic
functions in 7.4 this should be feasible. 

 
Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL