Re: index not used with inherited tables

On 5/10/06 9:04 AM, "Verena Ruff" <lists(at)triosolutions(dot)at> wrote:

> Hi,
>
> there is a table person, which has some child tables. the definition
> (part of):
>
> CREATE TABLE person
> (
> pers_id serial NOT NULL,
> -- some other fields --
> CONSTRAINT person_pk PRIMARY KEY (pers_id)
> )
>
> one of these child tables is mitarbeiter:
> CREATE TABLE mitarbeiter
> (
> -- some fields --
> CONSTRAINT pers_id_pk_mitarbeiter PRIMARY KEY (pers_id)
> ) INHERITS (person)
>
> as you see the inherited column pers_id is used as primary key in the
> child table as well. if I do
> EXPLAIN SELECT * FROM mitarbeiter WHERE pers_id=7; I see that PostgreSQL
> does a seq scan on mitarbeiter instead of an index scan. There are some
> other child tables and it seems like in some cases the index is used, in
> others not, but I have no idea why.
>
> Any hints?

Hi, Verena. See here:

http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html

Notice the "Caveats" section at the bottom. Indexes are not inherited.

Sean