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?

It seems like the child-table is really not concrete. It seems like it is
just a "pointer" to certain rows in the parent table which is then able to
discern which inherited "table-type" it is.

Thanks,

Aaron


---------------------------(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

"Announce" <truthhurts (AT) insightbb (DOT) com> writes:
No.

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

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.pa.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?




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

<me (AT) alternize (DOT) com> writes:
There might be systems out there that do it that way, but not Postgres.
Each table is completely independent as far as storage and indexes go.
The inheritance association is implemented by having the planner change
a query that scans a parent table to also scan its child tables. You
can see this happening if you examine the query plan with EXPLAIN:

regression=# create table p(f1 int);
CREATE TABLE
regression=# create index pi on p(f1);
CREATE INDEX
regression=# explain select * from p where f1 = 42;
QUERY PLAN
--------------------------------------------------------------
Index Scan using pi on p (cost=0.00..29.53 rows=11 width=4)
Index Cond: (f1 = 42)
(2 rows)

regression=# create table c(f2 text) inherits(p);
CREATE TABLE
regression=# create index ci on c(f1);
CREATE INDEX
regression=# explain select * from p where f1 = 42;
QUERY PLAN
---------------------------------------------------------------------------
Result (cost=0.00..49.08 rows=17 width=4)
-> Append (cost=0.00..49.08 rows=17 width=4)
-> Index Scan using pi on p (cost=0.00..29.53 rows=11 width=4)
Index Cond: (f1 = 42)
-> Index Scan using ci on c p (cost=0.00..19.54 rows=6 width=4)
Index Cond: (f1 = 42)
(6 rows)

regards, tom lane

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

Thanks for all of your help on this forum, Tom. This really helps to clear
things up.

I guess I thought that the child tables weren't 'concrete' because it
appeared that operations such as primary key uniqueness across the related
tables was being managed by the parent table for both the parent and all of
its children.

Unless what you said is in the docs under inheritance (I could have missed
it), it would be useful to give scenarios such as that and go into that
amount of detail when describing exactly how inheritance works in Postgres.

-Aaron
-----Original Message-----
From: pgsql-novice-owner (AT) postgresql (DOT) org
[mailtogsql-novice-owner (AT) postgresql (DOT) org]On Behalf Of Tom Lane
Sent: Monday, November 28, 2005 12:01 AM
To: Announce
Cc: pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] How does PG Inheritance work?


"Announce" <truthhurts (AT) insightbb (DOT) com> writes:
No.

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005


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