Re: [HACKERS] Fwd: Joins and links - Mailing list pgsql-hackers
From | Leon |
---|---|
Subject | Re: [HACKERS] Fwd: Joins and links |
Date | |
Msg-id | 3784E9AB.D85C25AE@udmnet.ru Whole thread Raw |
In response to | Re: [HACKERS] Fwd: Joins and links (Bob Devine <devine@cs.utah.edu>) |
List | pgsql-hackers |
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.
pgsql-hackers by date: