Re: Is a randomized default value primary key
От | Ron Johnson |
---|---|
Тема | Re: Is a randomized default value primary key |
Дата | |
Msg-id | 1021853908.10284.123.camel@rebel обсуждение исходный текст |
Ответ на | Re: Is a randomized default value primary key (April L <april@i-netco.com>) |
Список | pgsql-novice |
On Sun, 2002-05-19 at 17:19, April L wrote: > I made the primary key > > "authkey" bigint DEFAULT trunc(random()*10^15) NOT NULL > > Does that seem reasonable? bigint is 8 bytes. I came up with this prior to > receiving your reply. > > Since I do have to use the authkey to find records, it seems I would still > benefit by having an index for it even if I had a separate 4 byte primary > key - so I don't understand how it would save resources or increase > performance to avoid making this column the primary key? Admittedly, I > don't understand indexes in depth yet, I just assumed that every additional > index means additional housekeeping activities each time a record is > changed or added. You are right that more indexes means more work. Another benefit of pseudo-random numbers is that keys will be inserted into the tree in, well, pseudo-random order... So what? Keys that are inserted into the tree in ascending order all get inserted into the right side of the tree. Therefore, postgres must do extra work to keep the tree balanced. (That's the B in b-tree). Random numbers get inserted all over the tree, thus minimizing the work needed to keep the tree balanced. If your transactions are SERIALIZABLE, then, since ascending order keys all get inserted into the right side of the tree, all users are trying to insert into the same nodes, thus causing rollbacks. Keys that go all over the tree will minimize this problem. -- +---------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://ronandheather.dhs.org:81 | | | | "I have created a government of whirled peas..." | | Maharishi Mahesh Yogi, 12-May-2002, | ! CNN, Larry King Live | +---------------------------------------------------------+
В списке pgsql-novice по дате отправления: