What's the cost of a few extra columns?

From: "Announce" <truthhurts(at)insightbb(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: What's the cost of a few extra columns?
Date: 2005-10-10 03:03:33
Message-ID: KBEKKNMFLELKGIADDEPEAEEPCBAA.truthhurts@insightbb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

What's goin on pg-people?

I have a table PRODUCTIONS that is central to the DB and ties a lot of other
information together:

PRODUCTIONS (table)
----------------------------------
prod_id primary key
type_id foreign key
level_id foreign key
tour_id foreign key
show_id foreign key
venue_id foreign key
title varchar(255); not null indexed
version char;
details text
open_date date
close_date date
preview_open date
preview_close date
perform_tot int
preview_tot int
park_info text
phone_nos text
some_other_info text
seating_info text
this text
that text
create_tstmp timestamptz; NOW()
mod_tstmp timestamptz;triggered
delete_tstmp timestamptz;default null
is_complete bool

As it stands now, there are approximately 25-30 columns on the table. Since
this table is very central to the database, would it be more efficient to
break some of the columns (especially the TEXT ones) out into a separate
INFO table since some queries on the web will not care about all of these
text columns anyway? I know that pg can handle A LOT more columns and if
there IS no performance hit for keeping them all on the same table, I would
like to do that because the relation between PRODUCTIONS and the INFO will
always be 1-to-1.

My implementation of this INFO table would look a little somethin' like
this:

PROD_INFO (table)
-------------------------------
prod_id pkey/fkey
open_date date
close_date date
preview_open date
preview_close date
perform_tot int
preview_tot int
park_info text
phone_nos text
some_other_info text
seating_info text
this text
that text
(the rest would stay in in the original PRODUCTIONS table)

I am open to ANY suggestions, criticisms, mockery, etc.

Thanks,

Aaron

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andy 2005-10-10 08:39:45 Server misconfiguration???
Previous Message Jim C. Nasby 2005-10-08 22:51:55 Re: [HACKERS] A Better External Sort?