Inherited tables....

Inherited tables....

Post by Greg Patnud » Tue, 20 Aug 2002 06:03:32

I'd like to know exactly what happens when I insert a row into an inherited
table.... Can anyone explain ?

I am creating a schema for an FAQ system where there are default FAQ's
associated with an insurance carrier (cb_carrier_plan_faq) and the carrier's
defaults can be used, not used, or extended for each employer using the
insurance plan... So... my question is

What is going to happen when I "INSERT INTO cb_employer_plan_faq ()...."
does it automatically include columns from the parent table ?

Also -- how many levels if inheritance is safe ????

CREATE TABLE "cb_carrier_plan_faq" (
 "id"                int4 DEFAULT nextval('carrier_plan_faq_seq')  NOT NULL
 "plan_id" int4 not null,
 "faq_item_id" int4 not null,
 "active_flag"       bool DEFAULT 't' ,
 "create_dt"         timestamptz DEFAULT now() ,
CONSTRAINT "cb_carrier_plan_faq_pkey" PRIMARY KEY ("id", "plan_id",

CREATE TABLE "cb_employer_plan_faq" (
 "employer_plan_id" int4 not null,
 "use_plan_faq" bool default 't',
 "employer_faq_item_id" int4,
 "emp_plan_active_flag"       bool DEFAULT 't' ,
 "emp_plan_create_dt"         timestamptz DEFAULT now() ,
CONSTRAINT "cb_employer_plan_faq_pkey" PRIMARY KEY ("id",
"employer_plan_id", "employer_faq_item_id")
) inherits (cb_carrier_plan_faq);


Greg Pat*


1. Inherited tables vs non-inherited tables

I've been trying an inherited-table schema for my database and seem to
be getting a performance hit. The old table looked like this:

CREATE TABLE center_out (
    subject                     text,
    arm                         char,
    target                      int4,
    rep                         int4,
    success                     int2,       -- end of primary key
    exp_date                    date,
    exp_time                    time,  
    inter_target_radius         int4,
    target_radius               int4);

Since the fields subject, arm, target, and rep appeared in just about
every other table as the primary key, I made it an inherited table for
the new schema:

CREATE TABLE center_out (
        subject    text,
        arm        char,
        target     int2,
        rep        int4,
        success    int2    

CREATE UNIQUE INDEX pkcenter_out ON center_out (subject, arm, target,
rep, success);

CREATE TABLE center_out_behavior (
    exp_date                     date,
    exp_time                     time,
    inter_target_radius          int2,
    target_radius                int2
) INHERITS (center_out);

However, queries such as "SELECT DISTINCT subject FROM center_out"
seem to take 2-3 times longer in the new schema. Does this make sense?
I was hoping that the inherited information would lead to an
easier-to-follow, smaller, and faster database since there wouldn't be
redundant information across tables.


 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

2. Parameterized Queries in DTS

3. Inherited Table

4. Reading MSWorks files from Paradox

5. getting inherited table name

6. oraperl help

7. Problem with referential integrity and inherited tables

8. Oracle SQL/PLUS Functional Authority, Airline Systems, UK

9. references constraint on inherited tables?

10. plpgsql, fk inherited tables

11. Problem with referential integrity and inherited tables in 7.1.1

12. foreign keys with inherited tables

13. Inherited tables