pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feednumerical primary key vs alphanumerical primary key
6+ messages / 6 participants
[nested] [flat]
* numerical primary key vs alphanumerical primary key
@ 2013-02-04 21:52 Anne Rosset <arosset@collab.net>
0 siblings, 2 replies; 6+ messages in thread
From: Anne Rosset @ 2013-02-04 21:52 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: numerical primary key vs alphanumerical primary key
@ 2013-02-04 21:57 Виктор Егоров <vyegorov@gmail.com>
parent: Anne Rosset <arosset@collab.net>
1 sibling, 0 replies; 6+ messages in thread
From: Виктор Егоров @ 2013-02-04 21:57 UTC (permalink / raw)
To: Anne Rosset <arosset@collab.net>; +Cc: pgsql-performance
2013/2/4 Anne Rosset <arosset@collab.net>:
> 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.
Please, take a look at this blog post:
http://www.depesz.com/2012/06/07/123-vs-depesz-what-is-faster/
--
Victor Y. Yegorov
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: numerical primary key vs alphanumerical primary key
@ 2013-02-12 13:06 Julien Cigar <jcigar@ulb.ac.be>
parent: Anne Rosset <arosset@collab.net>
1 sibling, 1 reply; 6+ messages in thread
From: Julien Cigar @ 2013-02-12 13:06 UTC (permalink / raw)
To: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: numerical primary key vs alphanumerical primary key
@ 2013-02-12 15:05 Grant Johnson <grant@amadensor.com>
parent: Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 2 replies; 6+ messages in thread
From: Grant Johnson @ 2013-02-12 15:05 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; pgsql-performance
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 <jcigar@ulb.ac.be> wrote:
>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
>>
--
Sent from my Android phone with K-9 Mail. Please excuse my brevity.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: numerical primary key vs alphanumerical primary key
@ 2013-02-12 15:14 Florent Guillaume <fg@nuxeo.com>
parent: Grant Johnson <grant@amadensor.com>
1 sibling, 0 replies; 6+ messages in thread
From: Florent Guillaume @ 2013-02-12 15:14 UTC (permalink / raw)
To: pgsql-performance
For SQL Server, having a clustered index on a numeric incrementing key
is much better than having a semi-random uuid primary key used as the
clustered index itself.
Florent
On Tue, Feb 12, 2013 at 4:05 PM, Grant Johnson <grant@amadensor.com> wrote:
> 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 <jcigar@ulb.ac.be> wrote:
>>
>> 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
>>
>>
>
> --
> Sent from my Android phone with K-9 Mail. Please excuse my brevity.
--
Florent Guillaume, Director of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com http://www.nuxeo.org +33 1 40 33 79 87
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: numerical primary key vs alphanumerical primary key
@ 2013-02-12 16:26 Claudio Freire <klaussfreire@gmail.com>
parent: Grant Johnson <grant@amadensor.com>
1 sibling, 0 replies; 6+ messages in thread
From: Claudio Freire @ 2013-02-12 16:26 UTC (permalink / raw)
To: Grant Johnson <grant@amadensor.com>; +Cc: Julien Cigar <jcigar@ulb.ac.be>; pgsql-performance
On Tue, Feb 12, 2013 at 12:05 PM, Grant Johnson <grant@amadensor.com> wrote:
> 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.
The advantage in PG also exists, only tied to size. It's not really
whether it's numeric or not, but whether values are big or not. Int or
other primitive types tend to be far faster to join because of their
fixed, small size. If you have a varchar, and if you have big values
from time to time, joining becomes heavy because the index is huge (it
has to contain the values).
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-02-12 16:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-04 21:52 numerical primary key vs alphanumerical primary key Anne Rosset <arosset@collab.net>
2013-02-04 21:57 ` Виктор Егоров <vyegorov@gmail.com>
2013-02-12 13:06 ` Julien Cigar <jcigar@ulb.ac.be>
2013-02-12 15:05 ` Grant Johnson <grant@amadensor.com>
2013-02-12 15:14 ` Florent Guillaume <fg@nuxeo.com>
2013-02-12 16:26 ` Claudio Freire <klaussfreire@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox