Skip site navigation (1) Skip section navigation (2)

Re: Configuration Advice

From: "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com>
To: Steve <cheetah(at)tanabi(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Configuration Advice
Date: 2007-01-18 00:01:55
Message-ID: 81961ff50701171601n5ce08c2dq5b56729b59468448@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 1/17/07, Steve <cheetah(at)tanabi(dot)org> wrote:
>
>         However, I will look into this and see if I can figure out this
> average value.  This may be a valid idea, and I'll look some more at it.


It must be, Oracle sells it pretty heavily as a data warehousing feature
;).  Oracle calls it a materialized view, and the basic premise is you have
a "change" log (called a materialized log by Oracle) and you have a job that
runs through the change log and applies the changes to the materialized
view.

If you are using aggregates, be careful and make sure you use simple forms
of those aggregates.  For example, if you are using an "average" function
then you should have two columns sum and count instead.  Some aggregates are
too complex and cannot be represented by this solution and you will find
that you can't update the summary tables, so definitely try to stay away
from complex aggregates if you do not need them.

Here is a link to a PL/pgSQL effort that tries to simulate materialized
views:

http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

I don't know how complete it is, and it looks like there was a project
started but has been abandoned for the last 3 years.

-- 
Chad
http://www.postgresqlforums.com/

In response to

pgsql-performance by date

Next:From: SteveDate: 2007-01-18 00:27:22
Subject: Re: Configuration Advice
Previous:From: SteveDate: 2007-01-17 23:58:52
Subject: Re: Configuration Advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group