Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U5HQg-0001C9-KD for pgsql-performance@arkaria.postgresql.org; Tue, 12 Feb 2013 15:05:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U5HQg-0000cD-3j for pgsql-performance@arkaria.postgresql.org; Tue, 12 Feb 2013 15:05:54 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U5HQd-0000aN-O5 for pgsql-performance@postgresql.org; Tue, 12 Feb 2013 15:05:52 +0000 Received: from amadensor.com ([200.46.208.228]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U5HQb-00034L-JZ for pgsql-performance@postgresql.org; Tue, 12 Feb 2013 15:05:50 +0000 Received: from maia.hub.org (unknown [200.46.151.188]) by amadensor.com (Postfix) with ESMTP id C4C661FA45BB for ; Tue, 12 Feb 2013 15:05:47 +0000 (UTC) Received: from amadensor.com ([200.46.208.228]) by maia.hub.org (mx1.hub.org [200.46.151.188]) (amavisd-maia, port 10024) with ESMTP id 86474-03; Tue, 12 Feb 2013 15:05:41 +0000 (UTC) Received: from [10.132.3.162] (unknown [166.230.240.15]) by amadensor.com (Postfix) with ESMTPA id 742661FA45BA; Tue, 12 Feb 2013 15:05:40 +0000 (UTC) User-Agent: K-9 Mail for Android In-Reply-To: <511A3E71.10604@ulb.ac.be> References: <9A6B86D66BD3C2438AFACFA09354890F20698DDB@EXCH02.sp.corp.collab.net> <511A3E71.10604@ulb.ac.be> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----IVQ3RH1YC7V64D9B1RS5B3E2WWDH49" Subject: Re: numerical primary key vs alphanumerical primary key From: Grant Johnson Date: Tue, 12 Feb 2013 08:05:29 -0700 To: Julien Cigar ,pgsql-performance@postgresql.org Message-ID: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.082 tagged_above=-5 required=4.9 tests=ALL_TRUSTED=-1, BAYES_00=-1.9, HTML_MESSAGE=0.001, HTML_TAG_BALANCE_HEAD=0.817 X-Spam-Level: X-Pg-Spam-Score: -1.1 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------IVQ3RH1YC7V64D9B1RS5B3E2WWDH49 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable My experience has been that the performance advantage for numeric keys is= primarily an Oracle thing. However, Oracle is popular enough for people= to assume that it applies to databases in general. Julien Cigar wrote: >The biggest difference in performance between text and integer keys is=20 >usually down to whether you're inserting in order or not. Inserting in=20 >order is tons faster regardless of the type, since it keeps the index=20 >unfragmented and doesn't cause page splits. > >On 02/04/2013 22:52, Anne Rosset wrote: >> >> Hi, >> >> I have read a lot of different information about the benefits of >using=20 >> numerical primary key vs alphanumerical primary key(small size). And=20 >> what I am gathering is that for performance there is no more great=20 >> advantage. >> >> It seems like now RDBMS in general, postgres in particular handles=20 >> pretty well joins on text indexes. >> >> Did I understand correctly? >> >> Thanks, >> >> Anne >> --=20 Sent from my Android phone with K-9 Mail. Please excuse my brevity. ------IVQ3RH1YC7V64D9B1RS5B3E2WWDH49 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable My experienc= e has been that the performance advantage for numeric keys is primarily a= n Oracle thing. However, Oracle is popular enough for people to assume t= hat it applies to databases in general.

Julien Cigar <jcigar@ulb.ac.be> wrote:
=20 =20 =20 =20
The biggest difference in performance between text and integer keys is usually down to whether you're inserting in order or not. Inserting in order is tons faster regardless of the type, since it keeps the index unfragmented and doesn't cause page splits.

On 02/04/2013 22:52, Anne Rosset wrote:
=20 =20

Hi,

I have read a lot of different information about the benefits of using numerical primary key vs alphanumerical primary key(small size). And what I am gathering is that for performance there is no more great advantage.

It seems like now RDBMS in general, postgres in particular handles pretty well joins on text indexes.

Did I understand correctly?

Thanks,

Anne


=20

--
Sent from my Android phone with K-9 Mail. Please excuse my brevity. ------IVQ3RH1YC7V64D9B1RS5B3E2WWDH49--