From: Ian Harding <iharding@pakrat.com>
To: pgsql-sql@postgresql.org
Subject: Re: Index on View ?
Date: Thu, 05 Apr 2001 16:19:02 -0700
Message-ID: <3ACCFD66.22D0240A@pakrat.com> (raw)
References: <012a01c0bdac$d7864760$1001a8c0@archonet.com>
The Brand-X DBMS have 'indexed views' but in all their explanations I can't
see where they would be useful. SQL Server 2000 creates a 'clustered index'
on the view, then lets you create other unclustered indexes in addition to
it. Any time one of the source tables is updated, the clustered index needs
to be updated, which to me means instantiating the view, which means tons of
overhead. They talk about it being handy if there are aggregates in the
view, but why not create a table to hold the aggregated data and updated
with a trigger/rule?
Richard Huxton wrote:
> From: "Keith Gray" <keith@heart.com.au>
>
> > Is it possible (feasible) to create an index on a view.
> >
> > We have a large table and a defined sub-set (view)
> > from this table, would it be possible to keep an index
> > of the sub-set.
>
> I don't think so - the view is basically just a select rule that rewrites
> queries based on it.
>
> Indexes on underlying tables should be used though. Difficult to suggest
> what indices you might need without knowing the view/tables/queries
> involved.
>
> - Richard Huxton
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
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-sql@postgresql.org
Cc: iharding@pakrat.com
Subject: Re: Index on View ?
In-Reply-To: <3ACCFD66.22D0240A@pakrat.com>
References: <012a01c0bdac$d7864760$1001a8c0@archonet.com> <3ACCFD66.22D0240A@pakrat.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