DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=auG5le09tXXO+OB++3eO2+s18ad3senDZjcBUIT87pfbsjeyUtGxdmkdo59ffBHxo9hJy8SjN68cXp/7QZYeLwhziY6RjjDtc6uXQ3z/TaB9mok9zm783e4hNFKh7E86F0E5GLayQDyulS56Ahv4ua/RvB3SF8WMVpQMJZG3myM=;
Message-ID: <945290.45189.qm@web82104.mail.mud.yahoo.com>
Date: Sun, 2 Jan 2011 20:26:34 -0800 (PST)
From: Dennis Gearon <gearond@sbcglobal.net>
Subject: Re: uuid, COMB uuid, distributed farms
To: pgsql-general@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

One of the issues with using UUIDs for primary keys, or even in a secondary=
 key =0Ausing a unique index,would be paging of the index because of the ra=
ndomness of =0Athe values. This is what I have read, is that true for Posgr=
es?=0A=0AOn the tests in this article, doing large amounts of consecutive i=
nserts, =0Agenerating new UUIDs, and having the indexes getting updated cau=
sed a 30 =0A(THIRTY) times increase in time to do inserts. So he came up wi=
th a function =0Athat caused the lowest characters of the UUIDs to change s=
lowly according to =0Atime in microseconds. This reduced the time to using =
UUIDs for PKs for inserts =0Aand joins BOTH by only 10% relative to SEQUENC=
ES and their integer primary keys.=0A=0AThe theory being that most of hte d=
istribution in the index pages was due to the =0Alowest characters in the s=
tring.=0A=0AI've made a function in PHP that makes this same 'COMB UUID' as=
 he coined the =0Aterm. The output of a sequence of them, as fast as my box=
 could make them and =0AECHO them, and put them below. When NOT echoing the=
m, the lowest characters =0Achange VERY slowly. 1 MILLION of them can be ge=
nerated in 3 milliseconds on my =0Ahome box, (which is not doing much else)=
. to make 1 MILLION of them and store =0Athem in PHP arrays takes 3 seconds=
.=0A=0AMy question, I guess repeated, is does Postgres do index distributio=
n the same =0Away as the article suggests? So keeping the lower 12 characte=
rs sequential and =0Aslowly changing would keep index pages in memory longe=
r?=0A=0A=0A<sample values>=0A=0A58cf3d06-3d94-401d-9b34-498e87a3e469=0A981a=
b472-21fe-4b59-a224-498e87a3e473=0Adf2d2298-b549-4b8e-86d5-498e87a3e478=0A5=
cb92685-f9d3-473a-a5e8-498e87a3e47c=0A6c4b1712-dfe8-447b-8e94-498e87a3e480=
=0Ae9ab677a-812c-484e-ae71-498e87a3e484=0A2812c003-6e0b-4974-94ef-498e87a3e=
489=0A851dc2d8-9125-4410-a952-498e87a3e48d=0Aa8c695c9-6c43-4847-9bda-498e87=
a3e491=0Ac2d0d9f4-37e3-417a-9611-498e87a3e495=0A3ddfd416-6568-4c2c-8ffe-498=
e87a3e49a=0A471bf59a-f926-40b5-9990-498e87a3e49e=0A9d6a9876-3ba7-4133-a60f-=
498e87a3e4a2=0A01859d5f-3cde-4b83-8a1b-498e87a3e4a6=0A85321a94-5ae1-4b92-93=
f2-498e87a3e4aa=0Ae74e75ae-d30f-4ba3-b8b0-498e87a3e4ae=0A62ac5bc8-1498-4d2a=
-b8ef-498e87a3e4b3=0Afa597e9c-ae35-461e-b15f-498e87a3e4b7=0Af3c46abf-a6b0-4=
c9c-b22d-498e87a3e4bb=0Afc868307-d1b1-4253-91d7-498e87a3e4bf=0Ab79679a9-435=
9-42a1-bf46-498e87a3e4c3=0Ad91bf8cb-e3be-4446-be73-498e87a3e4c7=0Abec9e0a1-=
cd85-4f0c-b35b-498e87a3e4cc=0A0e0ea724-e145-4932-b0df-498e87a3e4d0=0A30ab3e=
05-26e3-44af-a82f-498e87a3e4d4=0A =0A</sample values>=0A=0ADennis Gearon=0A=
=0A=0ASignature Warning=0A----------------=0AIt is always a good idea to le=
arn from your own mistakes. It is usually a better =0Aidea to learn from ot=
hers=E2=80=99 mistakes, so you do not have to make them yourself. =0Afrom '=
http://blogs.techrepublic.com.com/security/?p=3D4501&tag=3Dnl.e036'=0A=0A=
=0AEARTH has a Right To Life,=0Aotherwise we all die.=0A