Re: query speed joining tables

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