Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Type: text/plain
MIME-Version: 1.0
From: "Phil Endecott" <spam_from_postgresql_sql@chezphil.org>
To: pgsql-sql@postgresql.org
Subject: Need indexes on inherited tables?
Date: Sat, 26 Jun 2004 15:29:19 -0400
Message-Id: <1902614@chezphil.org>

Dear Postgresql experts,

I have a base table that declares a primary key spanning a couple of columns:

create table B (
  id integer,
  xx someothertype,
  .....
  primary key (id, xx)
);

and a number of derived tables that inherit from B:

create table T (
 ....
) inherits (B);

An index is automatically created for B because of the primary key.

If I search for something in T using the key columns, e.g. I do

  select * from T where id=1 and xx=something;

will the index be used?  Or must I explicity create an index on id and xx for T and each of the other derived tables?

Is it any different if I search in B and find rows that are actually in T?

(Slightly unrelated: does the index on (id,xx) help when I am searching only on id?)

Thanks for any insight anyone can offer.

--Phil.