Re: 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(at)agliodbs(dot)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

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
>