pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Keith Gray <keith@heart.com.au>
Cc: PostgreSQL <pgsql-sql@postgresql.org>
Subject: Re: Index on View ?
Date: Fri, 6 Apr 2001 09:54:40 +0100
Message-ID: <00c601c0be77$38eb3c20$1001a8c0@archonet.com> (raw)
References: <3ACBF22F.33AA9C36@heart.com.au>
	<012a01c0bdac$d7864760$1001a8c0@archonet.com>
	<3ACCFC73.1A2EEC1A@heart.com.au>

From: "Keith Gray" <keith@heart.com.au>

> 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






view thread (5+ messages)

Message-ID: <00c601c0be77$38eb3c20$1001a8c0@archonet.com>
Permalink:  ../00c601c0be77$38eb3c20$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/00c601c0be77$38eb3c20$1001a8c0@archonet.com

 · 

reply

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: dev@archonet.com, keith@heart.com.au
  Subject: Re: Index on View ?
  In-Reply-To: <00c601c0be77$38eb3c20$1001a8c0@archonet.com>
  References: <3ACBF22F.33AA9C36@heart.com.au> <012a01c0bdac$d7864760$1001a8c0@archonet.com> <3ACCFC73.1A2EEC1A@heart.com.au> <00c601c0be77$38eb3c20$1001a8c0@archonet.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