X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D3AB89DC84E for ; Mon, 16 Jan 2006 12:27:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83239-07 for ; Mon, 16 Jan 2006 12:27:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id B86649DC82D for ; Mon, 16 Jan 2006 12:27:00 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id EC7CD308DA; Mon, 16 Jan 2006 17:26:59 +0100 (MET) From: Michael Riess X-Newsgroups: pgsql.performance Subject: Re: Materialized Views Date: Mon, 16 Jan 2006 17:26:59 +0100 Organization: Hub.Org Networking Services Lines: 49 Message-ID: References: <20060116094036.12a05a8c.frank@wiles.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5 (Windows/20051201) In-Reply-To: <20060116094036.12a05a8c.frank@wiles.org> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.03 required=5 tests=[AWL=0.030] X-Spam-Score: 0.03 X-Spam-Level: X-Archive-Number: 200601/201 X-Sequence-Number: 16679 Thanks! Of course I know that I can build materialized views with triggers, but so far I've avoided using triggers altogether ... I would really appreciate something like "create view foo (select * from b) materialize on query". But I'll look into your blog entry, thanks again! Mike > On Mon, 16 Jan 2006 15:36:53 +0100 > Michael Riess wrote: > >> Hi, >> >> I've been reading an interesting article which compared different >> database systems, focusing on materialized views. I was wondering how >> the postgresql developers feel about this feature ... is it planned >> to implement materialized views any time soon? They would greatly >> improve both performance and readability (and thus maintainability) >> of my code. >> >> In particular I'm interested in a view which materializes whenever >> queried, and is invalidated as soon as underlying data is changed. > > You can already build materialized views in PostgreSQL, but you > end up doing the "heavy lifting" yourself with triggers. You put > insert/update/delete triggers on the underlying tables of your > view that "do the right thing" in your materialized view table. > > I wrote a blog entry about this recently, > http://revsys.com/blog/archive/9, where I used a very simple > materialized view to achieve the performance I needed. It has links > to the relevant documentation you'll need however to build triggers > for a more complex situation. > > Hope this helps! > > --------------------------------- > Frank Wiles > http://www.wiles.org > --------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >