Skip site navigation (1) Skip section navigation (2)

Re: How does PG Inheritance work?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: me(at)alternize(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How does PG Inheritance work?
Date: 2005-11-28 14:32:37
Message-ID: 9564.1133188357@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
<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

In response to

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-11-28 16:26:15
Subject: Re: PostgreSQL 8.0.1-2 WinXP Services
Previous:From: meDate: 2005-11-28 12:01:28
Subject: Re: How does PG Inheritance work?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group