Inherited tables vs non-inherited tables - Mailing list pgsql-admin

From reina@nsi.edu (Tony Reina)
Subject Inherited tables vs non-inherited tables
Date
Msg-id f40d3195.0201021100.a6641fa@posting.google.com
Whole thread Raw
List pgsql-admin
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.

Thanks.
-Tony

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

pgsql-admin by date:

Previous
From: "Sipos Andras"
Date:
Subject: posgreSql doesn't start
Next
From: Oleg Lebedev
Date:
Subject: Re: Postgres RPMS for RedHat 7.2