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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Announce" <truthhurts(at)insightbb(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, November 28, 2005 7:00 AM
Subject: Re: [NOVICE] How does PG Inheritance work?

> "Announce" <truthhurts(at)insightbb(dot)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
>