Re: How does PG Inheritance work?

<me(at)alternize(dot)com> writes:
> 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...

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