Re: [HACKERS] Fwd: Joins and links - Mailing list pgsql-hackers

Leon wrote:
> If I denormalize my tables, they will grow some five to ten
> times in size.
> 
> But simply think what you are proposing: you are proposing
> exactly to break RDBMS "alphabet" to gain performance! This
> means that even SQL warriors see RDBMS's ideology as not
> proper and as corrupt, because it hinders performance.

and he wrote:
> After thinking a bit, it became clear to me that we are flaming 
> senselessly here. So can anyone do a fast hack to test links
> for speed? Especially with three or more tables being joined.

and in another message:
> Of course you tried to implement links and failed, didn't you?
> It such case personally I and maybe others want to hear what
> can go wrong, in order to benefit from your mistakes and lessons. 

It's a good idea to test it out.  My guess is that a hard link
between tables would speed up the join a small amount.

The bigger drawbacks are:
1) the application design is now encoded in the database structure.
Using link forces your _one_ application's need to affect all other
users of that table.  Each affected table would be bloated with
at least one more column.  All updates now affect multiple tables
leading to more locking, paging, and synchronization overhead. Etc.

2) adding performance tweaks for a version condemns you to always
be aware of it for future versions.  I know of many cases where
people now hate the idea of a database performance "improvement"
that has prevented them from modifying the database schema.
One person's company is still using a database that everyone hates
because one critical application prevents them from changing it.
Indexes are about the only useful physical level hack that have
survived the test of time.  An index is not part of relational
databases but are universally implemented because they yield
a huge payback.

3) Be aware of hardware improvements.  System performance is
still doubling every 18 months.  If a software hack can't match
that rate, it is probably not worth doing.


In my experience, old style network and hierarchical databases
are still faster than relational systems.  Just like OO DBMSs
can be faster.  However, the non-relational databases gain their
speed by optimizing for a single application instead of being a
more general purpose approach.  Nearly every company that uses
databases realizes that flexibility is more important than a bit
more speed unless that business has already maxed out their
computer's performance and are desparate for that extract bit.

It is my many years of watching databases in use that suggest
that links are not worth the overhead.  My gut feeling is that
links would speed up a simple join by only 10% and there are
many other ways to speed up joins.

--
Bob Devine  devine@cs.utah.edu