Re: How does PG Inheritance work?

hi tom

can you explain why querying EMPLOYEE will scan all three tables? how are
inherited table data stored?
is there all data in the 2 "child" tables PROGRAMMER and REPRESENTATIVE?

i'm currently looking into the inheritance thing for our system here, too. i
always thought the fields belonging to the inherited main table is stored in
the main table and the additional fields in the child table...

thanks,
thomas



----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Announce" <truthhurts@insightbb.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Monday, November 28, 2005 7:00 AM
Subject: Re: [NOVICE] How does PG Inheritance work?


> "Announce" <truthhurts@insightbb.com> writes:
>> How does Postgres internally handle inheritance under the following
>> scenario?
>> Using sample tables similar to a previous post:
>
>>  CREATE TABLE employee(id primary key, name varchar, salary
>> numeric(6,2));
>>  CREATE TABLE programmer(language varchar, project varchar) INHERITS
>> (employee);
>>  CREATE TABLE representative (region varchar) INHERITS (employee);
>
>> Let's say for example's sake, there are 10 million rows of PROGRAMMER
>> data
>> but only 100 rows of representative data.  Will a query (select, update,
>> insert, etc) on the REPRESENTATIVE table take a performance hit because
>> of
>> this?
>
> No.
>
>> It seems like the child-table is really not concrete.
>
> What makes you think that?
>
> In this example, queries against EMPLOYEE take a performance hit due to
> the existence of the child tables, because they end up scanning all
> three tables.  Queries directly against a child table do not notice the
> inheritance relationship at all.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>