Message-ID: <42B828C7.8040402@archonet.com>
Date: Tue, 21 Jun 2005 15:48:39 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Amit V Shah <ashah@tagaudit.com>
Cc: "'pgsql-performance@postgresql.org'" <pgsql-performance@postgresql.org>
Subject: Re: Do Views execute underlying query everytime ??
References: <0C072E7CC947D511AC9600A0CC7341200256CF51@vpn.tagaudit.com>
In-Reply-To: <0C072E7CC947D511AC9600A0CC7341200256CF51@vpn.tagaudit.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Amit V Shah wrote:
> Hi all,
> 
> I have like a repository table with is very very huge with atleast a few
> hundreds of millions, may be over that. The information is stored in form of
> rows in these tables. I need to make that information wide based on some
> grouping and display them as columns on the screen.
> 
> I am thinking of having a solution where I create views for each screen,
> which are just read only.
> 
> However, I donot know if the query that creates the view is executed
> everytime I select something from the view. Because if that is the case,
> then I think my queries will again be slow. But if that is the way views
> work, then what would be the point in creating them ..

That's exactly how they work. You'd still want them because they let you 
simplify access control (user A can only see some rows, user B can see 
all rows) or just make your queries simpler.

Sounds like you want what is known as a "materialised view" which is 
basically a summary table that is kept up to date by triggers. You query 
the table instead of actually recalculating every time. Perhaps google 
for "postgresql materialized view" (you might want a "z" or "s" in 
materialised).

-- 
   Richard Huxton
   Archonet Ltd