Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 30CE19F95B7
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 13 Aug 2007 18:25:58 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 71729-06-3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 13 Aug 2007 18:25:48 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by postgresql.org (Postfix) with ESMTP id 94E6F9F94B4
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Aug 2007 18:25:53 -0300 (ADT)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Mon, 13 Aug 2007 16:25:52 -0500
Message-Id: <46C0860D.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Mon, 13 Aug 2007 16:25:49 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,"Tom Lane" <tgl@sss.pgh.pa.us>,
	"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>
In-Reply-To: 
 <1806D1F73FCB7F439F2C842EE0627B18065F7A86@USA0300MS01.na.xerox.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/142
X-Sequence-Number: 26272

>>> 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
> Re-writing the view like this maybe bought me something.
> Tough to tell because I also increased some of the statistics.
=20
I don't know whether it was the finer-grained statistics or the simplificat=
ion,
but it bought you a new plan.  I don't know if the seven second improvement=

is real or within the run-to-run variation, though; it could be because =
you
happened to be better-cached at the time.
=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
-Kevin
=20