Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f383IU859200 for ; Sat, 7 Apr 2001 23:18:30 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f35NOt807348 for ; Thu, 5 Apr 2001 19:24:55 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f35NBqt49877 for pgsql-sql@postgresql.org; Thu, 5 Apr 2001 19:11:52 -0400 (EDT) (envelope-from news) From: Ian Harding X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Index on View ? Date: Thu, 05 Apr 2001 16:19:02 -0700 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 31 Message-ID: <3ACCFD66.22D0240A@pakrat.com> References: <012a01c0bdac$d7864760$1001a8c0@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.61 [en] (X11; U; Linux 2.2.12-20 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200104/107 X-Sequence-Number: 2449 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" > > > 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