Received: from www-uk-05.digiserve.com ([195.97.231.11]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3695m886903 for ; Fri, 6 Apr 2001 05:06:03 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id KAA12457; Fri, 6 Apr 2001 10:05:45 +0100 (BST) Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id BD0652A2E3; Fri, 6 Apr 2001 09:54:45 +0100 (BST) Message-ID: <00c601c0be77$38eb3c20$1001a8c0@archonet.com> From: "Richard Huxton" To: "Keith Gray" Cc: "PostgreSQL" References: <3ACBF22F.33AA9C36@heart.com.au> <012a01c0bdac$d7864760$1001a8c0@archonet.com> <3ACCFC73.1A2EEC1A@heart.com.au> Subject: Re: Index on View ? Date: Fri, 6 Apr 2001 09:54:40 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200104/94 X-Sequence-Number: 2434 From: "Keith Gray" > 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); OK - makes sense. > > 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? You can't do this at all I'm afraid. You can only index actual data. In this case you already have an index on clientID so you're covered. For your 15-day query, if it looks something like: SELECT * FROM invoices WHERE status='UNPAID' AND inv_date < CURRENT_DATE-'15 days' you might want indexes on inv_date and status. This doesn't mean that they will definitely be used though - it depends on how many records you have and how many the query returns. It *is* possible to define an index on a function, so you could in theory write a quite_old(inv_date) function and index that, but I doubt it makes much sense in your case. - Richard Huxton