From: Josh Berkus <josh@agliodbs.com>
To: vernonw@gatewaytech.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query speed joining tables
Date: Thu, 16 Jan 2003 09:46:30 -0800
Message-ID: <web-2316225@davinci.ethosmedia.com> (raw)
In-Reply-To: <LK4WXSGCFVTCBB9ZUOI71542NHVUJE.3e25a967@kimiko>
Vernon,
> What I stated is my observation on my project with over twenty
> multivalued detail tables. I have a selection query
> contained 200 characters, involving 10 tables, and using subquery.
> The performance is not bad after properly indexing,
> least than 3 second (what the planner says). I will have longer
> queries later and hope they won't have any performance
> problem.
Keep in mind that the complexity is all on your end, not the users'.
You can construct VIEWs, FUNCTIONs, TRIGGERs and RULEs which will make
the actual sophistication (i.e., 20 "detail tables") appear to the user
exactly as if there was only one flatfile table.
Frequently in database design, the design which is good for efficiency
and data integrity ... the "nromalized" design ... is baffling to
users. Fortunately, both SQL92-99 and PostgreSQL give us a whole
toolkit to let us "abstract" the normalized design into something the
users can handle. In fact, this is job #2 for the DBA in an
applications-development team (#1 is making sure all data is stored and
protected from corruption).
> Thank you for recommending another DB book after the "Database Design
> For Mere Mortals". I will read the book.
That's a great book, too. Don't start on Pascal until *after* you
have finished "database design".
-Josh Berkus
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-sql@postgresql.org
Cc: josh@agliodbs.com, vernonw@gatewaytech.com
Subject: Re: query speed joining tables
In-Reply-To: <web-2316225@davinci.ethosmedia.com>
* 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