Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Steve Crawford <scrawford@pinpointresearch.com>,
	"Girish Bajaj" <gbajaj@tietronix.com>, <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Fri, 18 Jul 2003 08:06:58 +0100
User-Agent: KMail/1.4.3
References: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
	<200307171239.51837.scrawford@pinpointresearch.com>
In-Reply-To: <200307171239.51837.scrawford@pinpointresearch.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307180806.58921.dev@archonet.com>

On Thursday 17 Jul 2003 8:39 pm, Steve Crawford wrote:
> OK, so basically you are trying to keep a hundered some odd attributes on
> everyone in the US. It's possible that a 150 column table is properly
> normalized (I have a similar situation) but it is rare.
>
> Suppose it is really properly normalized. You can still benefit from
> indexes on just some of the columns by choosing those most commonly used =
in
> queries. You may also want to research partial indexes (create index foo
> ... where bar=3Dbaz) which can under certain circumstances be far smaller=
 and
> faster than full indexes.

The other issue is, that even if your table is normalised you may want to=
=20
split vertically. That's going to depend on usage patterns, and I don't kno=
w=20
what you've got, but say you used three tables:

contact_address
contact_personal
contact_bank_details

Now a search by sales would be interested in ...address & ...personal where=
as=20
accounts would look at ...address & ...bank_details.

This _might_ make sense, but probably only if you can group columns into=20
related groups and users are more interested in some groups than others. If=
=20
you are lucky the gains might be in cache usage, whereas the costs will be =
in=20
joining groups for results.

I'm not saying you should do this just to try and improve performance, but =
it=20
might make sense if users look at it that way.

> Review your structure carefully. Plan on $$$ for the hardware.

Or remind your users that patience is a virtue ;-)

--=20
  Richard Huxton