pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Mike Relyea <Mike.Relyea@xerox.com>
Subject: Re: Help optimize view
Date: Mon, 13 Aug 2007 16:25:49 -0500
Message-ID: <46C0860D.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1806D1F73FCB7F439F2C842EE0627B18065F7A86@USA0300MS01.na.xerox.net>
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>

>>> On Mon, Aug 13, 2007 at  4:00 PM, in message
<1806D1F73FCB7F439F2C842EE0627B18065F7A86@USA0300MS01.na.xerox.net>, "Relyea,
Mike" <Mike.Relyea@xerox.com> wrote: 
> 
> Re-writing the view like this maybe bought me something.
> Tough to tell because I also increased some of the statistics.
 
I don't know whether it was the finer-grained statistics or the simplification,
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.
 
> From what Tom
> says, it sounds like if I want the data returned faster I'm likely to
> have to get beefier hardware.
 
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.
 
Such redundancies violate the normalization rules which are generally used
in database design, but some denormalization is often needed for acceptable
performance.
 
-Kevin
 





view thread (14+ messages)  latest in thread

Message-ID: <46C0860D.EE98.0025.0@wicourts.gov>
Permalink:  ../46C0860D.EE98.0025.0@wicourts.gov/
Also on:    postgresql.org/message-id/46C0860D.EE98.0025.0@wicourts.gov

 · 

reply
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: Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us, Mike.Relyea@xerox.com
  Subject: Re: Help optimize view
  In-Reply-To: <46C0860D.EE98.0025.0@wicourts.gov>

* 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