Message-Id: <46C096DD.EE98.0025.0@wicourts.gov>
Date: Mon, 13 Aug 2007 17:37:33 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,"Tom Lane" <tgl@sss.pgh.pa.us>,
	"Kevin Grittner" <Kgrittn.CCAP.Courts@wicourts.gov>,
	"Mike Relyea" <Mike.Relyea@xerox.com>
Subject: Re: Help optimize view
References: 
 <1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net>
	<14336.1186782218@sss.pgh.pa.us>
	<1806D1F73FCB7F439F2C842EE0627B18065BF2C0@USA0300MS01.na.xerox.net>
	<46C03F8C.EE98.0025.0@wicourts.gov>
	<1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>
	<46C07277.EE98.0025.0@wicourts.gov>
	<1806D1F73FCB7F439F2C842EE0627B18065F7A86@USA0300MS01.na.xerox.net>
	<46C0860D.EE98.0025.0@wicourts.gov>
In-Reply-To: <46C0860D.EE98.0025.0@wicourts.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Mon, Aug 13, 2007 at  4:25 PM, in message
<46C0860D.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:=20
>>>> On Mon, Aug 13, 2007 at  4:00 PM, in message
> <1806D1F73FCB7F439F2C842EE0627B18065F7A86@USA0300MS01.na.xerox.net>, =
"Relyea,
> Mike" <Mike.Relyea@xerox.com> wrote:=20
> =20
>> From what Tom
>> says, it sounds like if I want the data returned faster I'm likely to
>> have to get beefier hardware.
> =20
> That's not what he suggested.  If you introduce redundancy in a =
controlled
> fashion, you could have a single table with an index to more quickly get =
you
> to the desired set of data.  That can be maintained on an ongoing basis
> (possibly using triggers) or could be materialized periodically or prior =
to
> running a series of reports or queries.
> =20
> Such redundancies violate the normalization rules which are generally =
used
> in database design, but some denormalization is often needed for =
acceptable
> performance.
=20
One last thought regarding your table structure -- I noticed you were =
often
joining on column names ending in "ID" and selecting using column names
ending in "Name", where the values for the name columns were only a few
characters long.  It is not always a good idea to create a meaningless ID
number for a primary key if you have a meaningful value (or combination of
values) which would uniquely identify a row.
=20
If you were able to use the columns in your search criteria as keys, you
would have them in the Measurements table without creating any troublesome
redundancy.  You could then add Measurements indexes on these columns, and
your query might run in under a second.
=20
The down side of meaningful keys (oft cited by proponents of the technique)=

is that if you decide that everything with an AnalysisModuleName" name of
'NMF' should now be named 'NMX', you would have to update all rows which
contain the old value.  To be able to do this safely and reliably, you =
would
want to use DOMAIN definitions rigorously.  If you link through meaningless=

ID numbers (and what would be the point of changing those?) you can change
'NMF' to 'NMX' in one place, and everything would reflect the new value,
since it would always join to one place for those characters.
=20
-Kevin
=20