Re: Is there any way to index or cache a view, or function results?

Thanks that is basically what I was looking for I will investigate further.
I appreciate your response.

Thank you for your time,

Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.supernovasoftware.com
HJBUG Founder and President
http://www.hjbug.com

-----Original Message-----
From: Richard Broersma Jr [mailto:rabroersma(at)yahoo(dot)com]
Sent: Saturday, July 01, 2006 4:49 PM
To: jason(at)supernovasoftware(dot)com; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Is there any way to index or cache a view, or
function results?

> I was hoping for something a bit more automatic with less maintenance from
> me. Thank you for your reply.
> On 7/1/06, Jason Long <jason(at)supernovasoftware(dot)com> wrote:
> >
> > Is it possible to define a function or view that performs fairly
intensive
> > calculations and then index or cache these results?
> >
> > The data I have will be accessed more than modified, but still will be
> > modified semi regularly.
> >
> > Would someone please enlighten me on my options for improving
performance
> is
> > this situation?
>
> You could create a new table to store the results in and refresh it's
> contents every time the original data changes and you can index
> whatever you want, as long as you take into account that frequent and
> extensive changes to a table with a couple of indexes might slow
> things down a bit, depending on the number of changed records, the
> number of indices you define on the table and so on...
> You can keep the data in sync using triggers or with a periodic update
> task, depending on what kind of precision you need.

Perhaps an implemenation of a materialized view is more what you would like?
http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

Regards,
Richard Broersma jr.

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006