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