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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/i...l-inherit.html

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

Sean


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match