From: Announce <truthhurts@insightbb.com>
To: pgsql-performance@postgresql.org
Subject: What's the cost of a few extra columns?
Date: Sun, 9 Oct 2005 22:03:33 -0500
Message-ID: <KBEKKNMFLELKGIADDEPEAEEPCBAA.truthhurts@insightbb.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: truthhurts@insightbb.com
Subject: Re: What's the cost of a few extra columns?
In-Reply-To: <KBEKKNMFLELKGIADDEPEAEEPCBAA.truthhurts@insightbb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox