Date: Fri, 3 Nov 2006 14:36:00 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Index + View Question
Message-ID: <20061103193600.GB32617@phlogiston.dyndns.org>
References: <3ddac38d0611031117v742c5c84p3264c894709b559d@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3ddac38d0611031117v742c5c84p3264c894709b559d@mail.gmail.com>
User-Agent: Mutt/1.5.9i

On Fri, Nov 03, 2006 at 11:17:30AM -0800, Dylan Lorimer wrote:
> If I've got several tables, each with their own indexes. I also have a view
> that aggregates several of these tables. My question is, as novice-like as
> it may sound, do I need to create new indexes on the view or do the indexes
> from the orig. tables apply somehow when I select data from the view?

You _can't_ create an index on the view.  Depending on what version
of PostgreSQL you are using, it will be more or less intelligent
about using the indexes in your views.  But keep in mind that there
are some cases where the indexes on several tables simply won't work
-- if the condition is such that all the tables need to be compared
to one another, for instant, no index will help you, because you'll
end up pulling everything into memory before anything else can
happen.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?
		--attr. John Maynard Keynes