Received: from gw.heart.com.au (mail.hcs.com.au [203.12.235.237]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f35NBQ805495 for ; Thu, 5 Apr 2001 19:11:27 -0400 (EDT) (envelope-from keith@heart.com.au) Received: from tardis.heart.com.au (IDENT:root@tardis.heart.com.au [192.168.0.42]) by gw.heart.com.au (8.9.3/8.9.3) with ESMTP id JAA14954; Fri, 6 Apr 2001 09:22:35 +1000 Received: from heart.com.au ([192.168.0.65]) by tardis.heart.com.au (8.9.3/8.8.7) with ESMTP id JAA03565; Fri, 6 Apr 2001 09:11:13 +1000 Message-ID: <3ACCFC73.1A2EEC1A@heart.com.au> Date: Fri, 06 Apr 2001 09:14:59 +1000 From: Keith Gray X-Mailer: Mozilla 4.74 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Richard Huxton CC: PostgreSQL Subject: Re: Index on View ? References: <3ACBF22F.33AA9C36@heart.com.au> <012a01c0bdac$d7864760$1001a8c0@archonet.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200104/90 X-Sequence-Number: 2430 Richard Huxton wrote: > > Indexes on underlying tables should be used though. Difficult to suggest > what indices you might need without knowing the view/tables/queries > involved. As an example I may have an "Invoice" table with several thousand invoices. I could design a query/view "Aged" to get all unpaid invoices greater than 15 days old. I would often look for Invoices per Client and should have an index on Invoice(ClientID). e.g. CREATE INDEX Invoice_ClientID ON Invoice(ClientID); Is there any advantage in having an index on ClientID for the Aged query? e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID); Would this index be continually maintained by the RDBMS or only on lookup? Keith