Thread: Correction: datatypes are not "faster"
All, This is currently in: http://www.postgresql.org/docs/current/interactive/datatype-numeric.html#DATATYPE-INT "The type integer is the common choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally only used if disk space is at a premium. The bigint type should only be used if the integer range is insufficient, because the latter is definitely faster." This paragraph leaves a *lot* to be desired from an accuracy perspective and encourages useless byte-counting by amateur performance geeks. Instead: The integer type is the most common choice, as it covers a sufficient range for most operations requiring a non-decimal numeric value. If, however, you think you will want to store numbers greater than 2.4 billion, then use bigint. Note that bigint will require more storage space and memory. When you have several integer columns in the same table with values less than 32,000, you can sometimes achieve significant space savings by using smallint instead of integer. Otherwise, using smallint usually results in no real space or memory savings. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> "The type integer is the common choice, as it offers the best balance
> between range, storage size, and performance. The smallint type is
> generally only used if disk space is at a premium. The bigint type
> should only be used if the integer range is insufficient, because the
> latter is definitely faster."
> This paragraph leaves a *lot* to be desired from an accuracy perspective
Really? Exactly which statements will you claim are incorrect?
I notice that your proposed rewrite omits the bit about bigint being
slower, which I can only conclude means you haven't tested on any
32-bit platforms lately.
regards, tom lane
>> This paragraph leaves a *lot* to be desired from an accuracy perspective
>
> Really? Exactly which statements will you claim are incorrect?
That the int type is definitely faster on all platforms regardless of
circumstances. Especially the circumstance where the user really needs
a bigint and is doing some wonky workaround to use int instead, like the
newbie I'm chatting with on IRC right now, who did so specifically
because of that page of the docs.
Besides which, datatypes are not "faster". Specific operations with
them may be faster. They may require less storage and less RAM. But
if we call them "faster", then we're contributing to application
developer ignorance.
> I notice that your proposed rewrite omits the bit about bigint being
> slower, which I can only conclude means you haven't tested on any
> 32-bit platforms lately.
Hmmm. Yes, but that's more of an exception now than it is a common
circumstance. Change this:
"On very minimal operating systems the bigint type might not function
correctly, because it relies on compiler support for eight-byte
integers. On such machines, bigint acts the same as integer, but still
takes up eight bytes of storage. (We are not aware of any modern
platform where this is the case.)"
To this:
On 32-bit operating systems, or when PostgreSQL is complied 32-bit,
operations using bigint can be significantly slower than those with
integer. On very minimal operating systems the bigint type might not
function correctly, because it relies on compiler support for eight-byte
integers. On such machines, bigint acts the same as integer, but still
takes up eight bytes of storage. (We are not aware of any modern
platform where this is the case.)
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
> To this: > > On 32-bit operating systems, or when PostgreSQL is complied 32-bit, On 32-bit architectures, or when PostgreSQL is compiled as 32-bit binaries, operations using bigint may be slower than those with --- The problem I have with words like significant is that bigint is not noticeably slower. It is statistically slower (am I saying that right?). Sure if you "test" it, bigint will come out slower. Real world suggests that nobody is going to notice. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> To this: > > On 32-bit operating systems, or when PostgreSQL is complied 32-bit, On 32-bit architectures, or when PostgreSQL is compiled as 32-bit binaries, operations using bigint may be slower than those with --- The problem I have with words like significant is that bigint is not noticeably slower. It is statistically slower (am I saying that right?). Sure if you "test" it, bigint will come out slower. Real world suggests that nobody is going to notice. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Thu, 2010-09-02 at 15:24 -0700, Josh Berkus wrote: > >> This paragraph leaves a *lot* to be desired from an accuracy perspective > > > > Really? Exactly which statements will you claim are incorrect? > > That the int type is definitely faster on all platforms regardless of > circumstances. Especially the circumstance where the user really needs > a bigint and is doing some wonky workaround to use int instead, like the > newbie I'm chatting with on IRC right now, who did so specifically > because of that page of the docs. How do you know that the newbie, as you call him, needs a bigint. He was only trying to optimize his database according to his needs and what he read on the documentation. He might not be a postgresql master but at least he tries to use postgresql at its best. Being wrong does not make him a newbie but someone who learnt something which is a pretty common thing in computer science. The "newbie". > > Besides which, datatypes are not "faster". Specific operations with > them may be faster. They may require less storage and less RAM. But > if we call them "faster", then we're contributing to application > developer ignorance. > > > I notice that your proposed rewrite omits the bit about bigint being > > slower, which I can only conclude means you haven't tested on any > > 32-bit platforms lately. > > Hmmm. Yes, but that's more of an exception now than it is a common > circumstance. Change this: > > "On very minimal operating systems the bigint type might not function > correctly, because it relies on compiler support for eight-byte > integers. On such machines, bigint acts the same as integer, but still > takes up eight bytes of storage. (We are not aware of any modern > platform where this is the case.)" > > To this: > > On 32-bit operating systems, or when PostgreSQL is complied 32-bit, > operations using bigint can be significantly slower than those with > integer. On very minimal operating systems the bigint type might not > function correctly, because it relies on compiler support for eight-byte > integers. On such machines, bigint acts the same as integer, but still > takes up eight bytes of storage. (We are not aware of any modern > platform where this is the case.) > > > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com >