Inherited tables vs non-inherited tables

Inherited tables vs non-inherited tables

Post by Tony Rei » Fri, 04 Jan 2002 04:00:48



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

 
 
 

1. Inherited tables

Hello,
I'm trying to inherit primary key constraint from a table.
I'm using DS/UDO 9.14.UC4.
The SQL code is something like this:

CREATE TABLE Tables OF TYPE Tables_t
  (PRIMARY KEY (name));

CREATE TABLE CatTable OF TYPE CatTable_t UNDER Tables;

where:
  1. Tables_t is a row type having a field named "name"
  2. CatTable_t is a sub-type of Tables_t

Reading the documentation I supposed to have a primary key on the name
field in the CatTable table too, but that doesn't happen!!
I'm wrong or something in the server is wrong?

Thanks in advance
Best regards
  Lucio

--
(
  Lucio Benfante
     http://www.dei.unipd.it/~benfante
                                          )

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. istar data loads

3. Inherited tables....

4. Multiple character sets in one SQL6.5 table

5. Inherited Table

6. *how to use encrypted ms access database

7. foreign keys with inherited tables

8. Connection Problem

9. child table doesn't inherit PRIMARY KEY?

10. Problem with referential integrity and inherited tables

11. getting inherited table name

12. Inherited tables and NOT NULL (pg 7.2.1)

13. references constraint on inherited tables?