pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chad Wagner <chad.wagner@gmail.com>
To: Steve <cheetah@tanabi.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
Date: Wed, 17 Jan 2007 19:01:55 -0500
Message-ID: <81961ff50701171601n5ce08c2dq5b56729b59468448@mail.gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0701171831310.4471@kingcheetah.tanabi.org>
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
	<81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>
	<1169002739.19758.1169636285@webmail.messagingengine.com>
	<1169012767.6271.7.camel@localhost.localdomain>
	<1169044649.19974.1169715743@webmail.messagingengine.com>
	<1169050746.9586.44.camel@state.g2switchworks.com>
	<45AE79D5.9080000@fuzzy.cz>
	<Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org>
	<81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com>
	<Pine.GSO.4.64.0701171831310.4471@kingcheetah.tanabi.org>

On 1/17/07, Steve <cheetah@tanabi.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/

view thread (31+ messages)  latest in thread

Message-ID: <81961ff50701171601n5ce08c2dq5b56729b59468448@mail.gmail.com>
Permalink:  ../81961ff50701171601n5ce08c2dq5b56729b59468448@mail.gmail.com/
Also on:    postgresql.org/message-id/81961ff50701171601n5ce08c2dq5b56729b59468448@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chad.wagner@gmail.com, cheetah@tanabi.org
  Subject: Re: Configuration Advice
  In-Reply-To: <81961ff50701171601n5ce08c2dq5b56729b59468448@mail.gmail.com>
  References: <1168982580.8275.1169588211@webmail.messagingengine.com> <81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com> <1169002739.19758.1169636285@webmail.messagingengine.com> <1169012767.6271.7.camel@localhost.localdomain> <1169044649.19974.1169715743@webmail.messagingengine.com> <1169050746.9586.44.camel@state.g2switchworks.com> <45AE79D5.9080000@fuzzy.cz> <Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org> <81961ff50701171334t6bb6a402gc0d671643ffd714a@mail.gmail.com> <Pine.GSO.4.64.0701171831310.4471@kingcheetah.tanabi.org> <81961ff50701171601n5ce08c2dq5b56729b59468448@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox