From: | reina(at)nsi(dot)edu (Tony Reina) |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Inherited tables vs non-inherited tables |
Date: | 2002-01-02 19:00:48 |
Message-ID: | f40d3195.0201021100.a6641fa@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | James Kelty | 2002-01-02 19:34:50 | Re: Postgres RPMS for RedHat 7.2 |
Previous Message | Marc | 2002-01-02 09:47:09 | usetrace column in pg_shadow and pg_user |