Message-ID: <4B53B7BF.9050907@postnewspapers.com.au>
Date: Mon, 18 Jan 2010 09:22:07 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Seb <spluque@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: indexes
References: <87iqb3z74n.fsf@kolob.sebmags.homelinux.org>
	<4B53885C.3060706@gmail.com>
	<87fx64jk0f.fsf@kolob.sebmags.homelinux.org>
In-Reply-To: <87fx64jk0f.fsf@kolob.sebmags.homelinux.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Seb wrote:
> On Mon, 18 Jan 2010 08:59:56 +1100,
> Chris <dmagick@gmail.com> wrote:
> 
>>> o Should all foreign keys have an index?
> 
>> Not necessarily, you might just want the db to enforce the restriction
>> but not actually use the data in it. For example, keep a userid (and
>> timestamp) column of the last person to update a row. You may need it
>> to say "aha - this was last changed on this date and by person X", but
>> you'll never generally use it.
> 
>> If you never have a where clause with that column, no need to index
>> it. If you're using it in a join all the time, then yes it would be
>> better to index it.
> 
> Thanks for all your pointers!
> 
> Do views use the indexes in the underlying tables, whenever say a SELECT
> operation is called on the view?  If so, indexes on views don't make any
> sense right?

A view WILL use an index(es) on the underlying table(s) if the planner
thinks the use of those indexes will be helpful. In general it's as if
you substituted the text of the view's query into the query using the
view and executed that composite query.

Sometimes views are very expensive to compute, and avoiding computing
values you're not interested in for a particular query would be very
handy. It's not always possible to tack on a WHERE clause that gets
applied as part of a big complex join; sometimes you land up computing a
lot of data then throwing the vast majority of it away. That's not
really desirable.

If you have very expensive views, a good way to handle this is to
maintain a materialized view and index the materialized view. Pg doesn't
currently have any support for creating and maintaining materialized
views automatically, but it's easy enough to do it with a few triggers
(and you can often do it more efficiently/intelligently than the
database could do in the general case).

I have a couple of materialized views in my schema that make queries
several orders of magnitude faster (!) when querying for current data by
eliminating the need for tree-like multiple self joins. Updates to these
views are cheap, because the triggers on the source tables can
selectively update only the mat.view entries they know are affected by a
given update/insert/delete.

--
Craig Ringer