Thread: index not used with inherited tables

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?

Regards,
Verena

On 5/10/06 9:04 AM, "Verena Ruff" <lists@triosolutions.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

Hi,

Sean Davis schrieb:
>> one of these child tables is mitarbeiter:
>> CREATE TABLE mitarbeiter
>> (
>>   -- some fields --
>>   CONSTRAINT pers_id_pk_mitarbeiter PRIMARY KEY (pers_id)
>> ) INHERITS (person)
>>
> Hi, Verena.  See here:
>
> http://www.postgresql.org/docs/8.1/interactive/ut ddl-inherit.html
>
> Notice the "Caveats" section at the bottom.  Indexes are not inherited
But I define a new primary key constraint in the child table, using the
column frm the parent. Shouldn't create this an index implicitly?

Regards,
Verena

Verena Ruff <lists@triosolutions.at> writes:
> But I define a new primary key constraint in the child table, using the
> column frm the parent. Shouldn't create this an index implicitly?

Yes, and I'm sure it did.  The more relevant question is how much data
do you have in the child table?  The planner generally won't bother with
an indexscan if the table is only one or two disk pages; trying to use
the index in such cases would end up fetching *more* disk pages overall.

            regards, tom lane

Hi,

Tom Lane schrieb:
> Yes, and I'm sure it did.  The more relevant question is how much data
> do you have in the child table?  The planner generally won't bother with
> an indexscan if the table is only one or two disk pages; trying to use
> the index in such cases would end up fetching *more* disk pages overall.
>
Yes, there isn't much data in those child tables. It seems like this is
the reason why. Thanks for this information!

Regards,
Verena