On Fri, Jan 13, 2006 at 12:42:55PM +0000, Leandro Guimarães Faria Corcete DUTRA wrote: > Rod Taylor <pg <at> rbt.ca> writes: > > The basic idea is that most of us break out schemas by creating fake > > primary keys for the purpose of obtaining performance because using the > > proper primary key (single or multiple columns) is often very slow. > This is one thing I simply can't understand.
> If you still declare the natural key(s) as UNIQUEs, you have just made > performance worse. Now there are two keys to be checked on UPDATEs and > INSERTs, two indexes to be updated, and probably a SEQUENCE too. Not to completely defend the practice - but in some applications, INSERT is much less frequent than UPDATE, and that UPDATE requires a unique check on the primary key and the surrogate key, as well as an update, should be considered (and I believe is considered) a PostgreSQL performance bug. It's undesirable and unnecessary behaviour for the majority of uses (where they key does not change as a part of the update). > Certainly decoupling presentation from storage would be nice, but even before > that generalised use of surrogate keys seems to me a knee-jerk reaction. Yes, I agree. As per a previous thread, I'm one of those using it to generalize my query / update implementation into common base code. I have other reasons - but I confess to this being the real reason. In my case, the cost of maintaining the code that queries / updates is more expensive than the cost of having an extra unique index, and the storage and performance impacts this has on my data. :-) Is my primary reason good on its own, without the other more legitimate justifications? It's good enough for me. I expect others to strongly disagree. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq