From: Announce <truthhurts@insightbb.com>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: What's the cost of a few extra columns?
Date: Mon, 10 Oct 2005 21:14:12 -0500
Message-ID: <KBEKKNMFLELKGIADDEPEOEFHCBAA.truthhurts@insightbb.com> (raw)
In-Reply-To: <20051011001249.GJ39569@pervasive.com>
Thanks a lot.
Well, if I'm understanding you correctly, then doing the vertical splitting
for some of the text columns WOULD decrease the average row size returned in
my slimmer PRODUCTIONS table. I don't plan on using any of the "prod_info"
columns in a WHERE clause (except open_date and close_date now that I think
of it so they would stay in the original table).
There will be a lot of queries where I just want to return quick pri-key,
prod_name and prod_date results from a PRODUCTION search. Then, there would
be a detail query that would then need all of the PRODUCTION and INFO data
for a single row.
Thanks again,
Aaron
-----Original Message-----
Subject: Re: [PERFORM] What's the cost of a few extra columns?
What you're describing is known as vertical partitioning (think of
splitting a table vertically), and can be a good technique for
increasing performance when used properly. The key is to try and get the
average row size down, since that means more rows per page which means
less I/O. Some things to consider:
First rule of performance tuning: don't. In other words, you should be
able to verify with benchmark numbers that a) you need to do this and b)
how much it's actually helping.
How will splitting the table affect *_tstmp, especially mod_tstmp?
How will you handle inserts and joining these two tables together? Will
you always do a left join (preferably via a view), or will you have a
trigger/rule that inserts into production_info whenever a row is
inserted into productions?
On Sun, Oct 09, 2005 at 10:03:33PM -0500, Announce wrote:
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, jnasby@pervasive.com
Subject: Re: What's the cost of a few extra columns?
In-Reply-To: <KBEKKNMFLELKGIADDEPEOEFHCBAA.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