public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dan McGee <[email protected]>
To: Marcelo Sena <[email protected]>
Cc: [email protected]
Subject: Re: Observation on integer types documentation
Date: Mon, 21 May 2012 05:11:34 -0500
Message-ID: <CAEik5nN7rG=7VpOdgMe=GYeANgNtAEw1NCk2q1sNiawtMgTFZA@mail.gmail.com> (raw)
In-Reply-To: <CAPmRTtMY2sE_dXtNUZYapDArnhebzU2Any5w3F17yz6O67OOUw@mail.gmail.com>
References: <CAEik5nNxPd7NqvJHEnQmHh4oB1fuX3a1cAgSpUCJRaeNtQs1sQ@mail.gmail.com>
<CAPmRTtMY2sE_dXtNUZYapDArnhebzU2Any5w3F17yz6O67OOUw@mail.gmail.com>
Yes, I didn't see any difference in query plans between the two.
-Dan
On Sun, May 13, 2012 at 7:08 PM, Marcelo Sena <[email protected]> wrote:
> I'm new here but your proposal makes sense to me. Are the query plans equal
> on both architectures?
>
> --
> Marcelo Lacerda
>
>
> On Mon, Apr 30, 2012 at 5:59 PM, Dan McGee <[email protected]> wrote:
>>
>> Hey everyone,
>>
>> Reading the docs today, I came across this paragraph
>>
>> (http://www.postgresql.org/docs/devel/static/datatype-numeric.html#DATATYPE-INT),
>> which goes back several major versions:
>>
>> > 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 range of the integer type is insufficient, because the latter is
>> > definitely faster.
>>
>> A few thoughts on this.
>> 1) the use of the word "latter" isn't totally clear, or at least I had
>> to re-read it to realize former was 'bigint' and latter was 'integer'.
>> It might just be the style of writing.
>> 2) I'm less than convinced this note belongs in modern documentation,
>> and set out to test that theory. My full results are below, but the
>> summary is this: on a 64-bit system, there seems to be only a minimal
>> measurable performance difference (< 5%) and very little size
>> difference. In the case of the indexes, the size difference is zero.
>> This is not true for a 32-bit system (where it is 39% slower), but the
>> blanket statement doesn't hold true, which is why I'm writing all this
>> up.
>>
>> On a final note, the following paragraph also seems like it has
>> outlived its useful life:
>>
>> > 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.)
>>
>> Thanks!
>> -Dan
>>
>>
>> Table setup (only difference is type of 'id' column):
>>
>> Table "public.package_files"
>> Column | Type | Modifiers
>> --------------+------------------------+------------------------
>> id | integer | not null
>> pkg_id | integer | not null
>> is_directory | boolean | not null default false
>> directory | character varying(255) | not null
>> filename | character varying(255) |
>> Indexes:
>> "package_files_pkey" PRIMARY KEY, btree (id)
>> "package_files_pkg_id" btree (pkg_id) CLUSTER
>>
>>
>> Table "public.package_files_int8"
>> Column | Type | Modifiers
>> --------------+------------------------+------------------------
>> id | bigint | not null
>> pkg_id | integer | not null
>> is_directory | boolean | not null default false
>> directory | character varying(255) | not null
>> filename | character varying(255) |
>> Indexes:
>> "package_files_int8_pkey" PRIMARY KEY, btree (id)
>> "package_files_int8_pkg_id" btree (pkg_id) CLUSTER
>>
>>
>> # select count(*) from package_files;
>> 2621418
>> # select count(*) from package_files_int8 ;
>> 2621418
>>
>>
>> All runs below were done after issuing a few warm up queries, and both
>> tables went through a VACUUM/CLUSTER/ANALYZE sequence.
>>
>> 32-bit P4 2.4 GHz (single core). no enabled CPU frequency scaling, 1GB
>> total ram, shared_buffers 128MB, work_mem 4MB:
>>
>> relation | size
>> ----------------------------------+------------
>> public.package_files_int8 | 239 MB
>> public.package_files | 229 MB
>> public.package_files_int8_pkey | 56 MB
>> public.package_files_int8_pkg_id | 45 MB
>> public.package_files_pkey | 45 MB
>> public.package_files_pkg_id | 45 MB
>>
>> archweb=> \timing on
>> Timing is on.
>> archweb=> \t
>> Showing only tuples.
>> archweb=> select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 516.558 ms
>> archweb=> select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 519.720 ms
>> archweb=> select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 533.330 ms
>> archweb=> select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 519.095 ms
>> archweb=> select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 520.253 ms
>>
>> archweb=> select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 731.194 ms
>> archweb=> select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 730.329 ms
>> archweb=> select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 724.646 ms
>> archweb=> select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 710.815 ms
>> archweb=> select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 724.519 ms
>>
>>
>>
>>
>> 64-bit Core2 Quad 2.66 GHz (four core), CPU freq scaling disabled
>> (performance governor used), 8GB total ram, shared_buffers 128MB,
>> work_mem 4MB:
>>
>> relation | size
>> ----------------------------------+------------
>> public.package_files_int8 | 245 MB
>> public.package_files | 234 MB
>> public.package_files_int8_pkey | 56 MB
>> public.package_files_pkg_id | 56 MB
>> public.package_files_int8_pkg_id | 56 MB
>> public.package_files_pkey | 56 MB
>>
>> dmcgee=# select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 177.078 ms
>> dmcgee=# select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 176.109 ms
>> dmcgee=# select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 177.478 ms
>> dmcgee=# select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 176.639 ms
>> dmcgee=# select count(*) from (select * from package_files where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 176.453 ms
>>
>> dmcgee=# select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 185.768 ms
>> dmcgee=# select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 185.159 ms
>> dmcgee=# select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 184.407 ms
>> dmcgee=# select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 184.555 ms
>> dmcgee=# select count(*) from (select * from package_files_int8 where
>> pkg_id in (48024, 48025, 40343) order by id) a;
>> 119325
>> Time: 184.290 ms
>>
>>
>> Overall results:
>>
>> i686 x86_64
>> int4 int8 int4 int8
>> 516.558 731.194 177.078 185.768
>> 519.72 730.329 176.109 185.159
>> 533.33 724.646 177.478 184.407
>> 519.095 710.815 176.639 184.555
>> 520.253 724.519 176.453 184.29
>>
>>
>>
>> Average 521.7912 724.3006 176.7514 184.8358
>> Stddev 6.6040841681 8.1530512264 0.5359499044
>> 0.619288059
>> Ratio 1.3881042839 1.0457388173
>>
>> --
>> Sent via pgsql-docs mailing list ([email protected])
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-docs
>
>
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Observation on integer types documentation
In-Reply-To: <CAEik5nN7rG=7VpOdgMe=GYeANgNtAEw1NCk2q1sNiawtMgTFZA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox