Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U5FZm-0007um-OP for pgsql-performance@arkaria.postgresql.org; Tue, 12 Feb 2013 13:07:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U5FZl-0007C6-Pq for pgsql-performance@arkaria.postgresql.org; Tue, 12 Feb 2013 13:07:09 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U5FZk-0007C1-PK for pgsql-performance@postgresql.org; Tue, 12 Feb 2013 13:07:09 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U5FZj-0001Hg-0Z for pgsql-performance@postgresql.org; Tue, 12 Feb 2013 13:07:07 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApMBAPI8GlGkD30E/2dsb2JhbAANN4JJuwuDfYMSAQEBBC1cCwQUCSUPAkYTCAEBs2GDMYcDiQ+OW4MqA5JqiRiNfA Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by smtp.ulb.ac.be with ESMTP/TLS/DHE-RSA-CAMELLIA256-SHA; 12 Feb 2013 14:07:05 +0100 Message-ID: <511A3E71.10604@ulb.ac.be> Date: Tue, 12 Feb 2013 14:06:57 +0100 From: Julien Cigar User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:17.0) Gecko/20130211 Thunderbird/17.0.2 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: numerical primary key vs alphanumerical primary key References: <9A6B86D66BD3C2438AFACFA09354890F20698DDB@EXCH02.sp.corp.collab.net> In-Reply-To: <9A6B86D66BD3C2438AFACFA09354890F20698DDB@EXCH02.sp.corp.collab.net> Content-Type: multipart/alternative; boundary="------------020702020108070609060904" X-Pg-Spam-Score: -4.2 (----) 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 This is a multi-part message in MIME format. --------------020702020108070609060904 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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: > > 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 > --------------020702020108070609060904 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

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:

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

--------------020702020108070609060904--