Inherited tables vs non-inherited tables

Поиск
Список
Период
Сортировка
Искать

Inherited tables vs non-inherited tables

От:
reina@nsi.edu (Tony Reina)
Дата:
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
FAQ