Thread

Re: [HACKERS] Fwd: Joins and links

Leon <leon@udmnet.ru> — 1999-07-08T18:10:51Z

Bob Devine wrote:

> The bigger drawbacks are:
> 1) the application design is now encoded in the database structure.

This is true.

> 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.

In fact link is intended to replace foreign key in a given table 
and not coexist with it. Given that it eliminates the need of 
index, there is even a small space gain.

>  All updates now affect multiple tables
> leading to more locking, paging, and synchronization overhead. Etc.

Oh, no :)  After a short discussion it became clear that there
must not be a link rewrite in a referencing table during update. 
So update goes as usual, involving only one table. Instead we have 
a chain of referenced tuples left after update. VACUUM eliminates
these. 

> 
> 2) adding performance tweaks for a version condemns you to always
> be aware of it for future versions.

Absolutely right. If we started a talk on general matters, let me 
clear my position. 

Every tool is suitable for it's purpose. No one walks from city
to city and uses car instead. And no one takes a car to get into
neighbor's home for evening tea :)  So. There are tasks of 
different kind. Some are flexible and require redesigning of 
relationships often. But there are other, which are well known
and explored well, and have well known structure. Accounting is
some of them. There are a lot others, without doubt. What is 
proposed is a tool to handle tasks of the second sort effectively,
since general RDBMS is a tool for other, flexible tasks. This is a 
matter of design and designer's job to choose the right tool.
If designer made a wrong choice, it is a problem of him an his
kicked ass. You should give designer as many tools as possible 
and let him choose. They will love you for that :)


> 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.

Oh, that argument again :)  I'll tell you - sooner or later
this development will stop. There are purely physical obstacles
that prevent manufacturing of silicon chips with frequencies much
higher than 10 gigahertz.

> 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.

Let's count. We have two tables, joined by link. What is the
cost of lookup? First there is an index scan, which is between
2 and 5 iterations, and link lookup which is 1 iteration. Average
is 4 iterations. And if we don't have link, there is 6 iterations.
More than 10% already! We still didn't consider joining multiple
tables and big tables. So the gain will be big anyway.

That is not to consider the optimizer (do I sound like a broken
record? :)  To be sincere, current Postgres optimizer sucks heavily
and in most cases can't figure out the fastest way. Implementing
links is a quick and cheap way to get a performance gain on 
a wide range of tasks. I am obliged to repeat this again and again, 
because every day there appears a new developer who didn't hear
that yet :)

-- 
Leon.