From: | <me(at)alternize(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: How does PG Inheritance work? |
Date: | 2005-11-28 12:01:28 |
Message-ID: | 008401c5f413$7767e260$6402a8c0@iwing |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-11-28 14:32:37 | Re: How does PG Inheritance work? |
Previous Message | Nikola Milutinovic | 2005-11-28 11:28:49 | Re: Linux Format Gambas Easy Database Access! |