From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kgrittn.CCAP.Courts@wicourts.gov>
To: Mike Relyea <Mike.Relyea@xerox.com>
Subject: Re: Help optimize view
Date: Mon, 13 Aug 2007 17:37:33 -0500
Message-ID: <46C096DD.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <46C0860D.EE98.0025.0@wicourts.gov>
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>
>>> On Mon, Aug 13, 2007 at 4:25 PM, in message
<46C0860D.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:
>>>> On Mon, Aug 13, 2007 at 4:00 PM, in message
> <1806D1F73FCB7F439F2C842EE0627B18065F7A86@USA0300MS01.na.xerox.net>, "Relyea,
> Mike" <Mike.Relyea@xerox.com> wrote:
>
>> 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.
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.
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.
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.
-Kevin
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, Kgrittn.CCAP.Courts@wicourts.gov, Mike.Relyea@xerox.com
Subject: Re: Help optimize view
In-Reply-To: <46C096DD.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