Thread: Index + View Question

Hi,<br /><br />If I've got several tables, each with their own indexes. I also have a view that aggregates several of
thesetables. My question is, as novice-like as it may sound, do I need to create new indexes on the view or do the
indexesfrom the orig. tables apply somehow when I select data from the view? <br /><br />Cheers,<br />Dylan<br /> 

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

When you select from views, you are infact selecting from the
underlying tables. Indexes created on those tables are used
ultimately.

--Imad
www.EnterpriseDB.com


On 11/4/06, Dylan Lorimer <edylan@google.com> wrote:
> Hi,
>
> 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?
>
> Cheers,
> Dylan
>