| 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: | Whole Thread | Raw Message | 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
| 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? |