Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0315BA684DD for ; Mon, 21 May 2012 07:11:51 -0300 (ADT) Received: from mail-pz0-f46.google.com ([209.85.210.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SWPad-0003Yk-UD for pgsql-docs@postgresql.org; Mon, 21 May 2012 10:11:50 +0000 Received: by dady13 with SMTP id y13so6612745dad.19 for ; Mon, 21 May 2012 03:11:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=vhzUKKZKwZVBdh/J+yRDVphFMXCUyM6OTupoojqKKMo=; b=EOSgcTmGtjZ5oYbf0gi8GxexDZYUN57wh7vn4iNay6Yb2s1iiovvSo2inBelr6mXbT s0/THs4m+zj+W/Iaby6s/pWvAZrB+uwSK+mxCF1dameKCOALNUGmes2lEjk1BW7x5MQk VEdTmFwUHzzFYyeFV6RZWRytzULHzlNRRnQTh9pxXwW6MiAiEN+fv0b0l73vOAEinjIV UYMKjLRLXsvlg1pFzYA4Jx+SwFazTvQNJd1EDJwTexEYmFmjbNvnFwRT/uB0J+QxHvqN 0eKUCNm17BMWzeZyU7i1K+hgJPPIz178KKVVFpYZ6OCJ5xHZaWr6BhZ+1GjfxdRbL7z0 9cKw== MIME-Version: 1.0 Received: by 10.68.213.101 with SMTP id nr5mr66197478pbc.131.1337595094427; Mon, 21 May 2012 03:11:34 -0700 (PDT) Received: by 10.142.178.10 with HTTP; Mon, 21 May 2012 03:11:34 -0700 (PDT) In-Reply-To: References: Date: Mon, 21 May 2012 05:11:34 -0500 Message-ID: Subject: Re: Observation on integer types documentation From: Dan McGee To: Marcelo Sena Cc: pgsql-docs@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.7 (-) X-Archive-Number: 201205/22 X-Sequence-Number: 7347 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 w= rote: > I'm new here but your proposal makes sense to me. Are the query plans equ= al > on both architectures? > > -- > Marcelo Lacerda > > > On Mon, Apr 30, 2012 at 5:59 PM, Dan McGee wrote: >> >> Hey everyone, >> >> Reading the docs today, I came across this paragraph >> >> (http://www.postgresql.org/docs/devel/static/datatype-numeric.html#DATAT= YPE-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 gen= erally >> > only used if disk space is at a premium. The bigint type should only b= e used >> > if the range of the integer type is insufficient, because the latter i= s >> > 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 intege= rs. On >> > such machines, bigint acts the same as integer, but still takes up eig= ht >> > 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" >> =C2=A0 =C2=A0Column =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Typ= e =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 Modifiers >> --------------+------------------------+------------------------ >> =C2=A0id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null >> =C2=A0pkg_id =C2=A0 =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null >> =C2=A0is_directory | boolean =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| not null default false >> =C2=A0directory =C2=A0 =C2=A0| character varying(255) | not null >> =C2=A0filename =C2=A0 =C2=A0 | character varying(255) | >> Indexes: >> =C2=A0 =C2=A0"package_files_pkey" PRIMARY KEY, btree (id) >> =C2=A0 =C2=A0"package_files_pkg_id" btree (pkg_id) CLUSTER >> >> >> Table "public.package_files_int8" >> =C2=A0 =C2=A0Column =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Typ= e =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 Modifiers >> --------------+------------------------+------------------------ >> =C2=A0id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | bigint =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null >> =C2=A0pkg_id =C2=A0 =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null >> =C2=A0is_directory | boolean =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| not null default false >> =C2=A0directory =C2=A0 =C2=A0| character varying(255) | not null >> =C2=A0filename =C2=A0 =C2=A0 | character varying(255) | >> Indexes: >> =C2=A0 =C2=A0"package_files_int8_pkey" PRIMARY KEY, btree (id) >> =C2=A0 =C2=A0"package_files_int8_pkg_id" btree (pkg_id) CLUSTER >> >> >> # select count(*) from package_files; >> =C2=A02621418 >> # select count(*) from package_files_int8 ; >> =C2=A02621418 >> >> >> 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: >> >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 relation =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0size >> ----------------------------------+------------ >> =C2=A0public.package_files_int8 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 239 MB >> =C2=A0public.package_files =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2= 29 MB >> =C2=A0public.package_files_int8_pkey =C2=A0 | 56 MB >> =C2=A0public.package_files_int8_pkg_id | 45 MB >> =C2=A0public.package_files_pkey =C2=A0 =C2=A0 =C2=A0 =C2=A0| 45 MB >> =C2=A0public.package_files_pkg_id =C2=A0 =C2=A0 =C2=A0| 45 MB >> >> archweb=3D> \timing on >> Timing is on. >> archweb=3D> \t >> Showing only tuples. >> archweb=3D> select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 516.558 ms >> archweb=3D> select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 519.720 ms >> archweb=3D> select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 533.330 ms >> archweb=3D> select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 519.095 ms >> archweb=3D> select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 520.253 ms >> >> archweb=3D> select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 731.194 ms >> archweb=3D> select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 730.329 ms >> archweb=3D> select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 724.646 ms >> archweb=3D> select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 710.815 ms >> archweb=3D> select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> 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: >> >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 relation =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0size >> ----------------------------------+------------ >> =C2=A0public.package_files_int8 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 245 MB >> =C2=A0public.package_files =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2= 34 MB >> =C2=A0public.package_files_int8_pkey =C2=A0 | 56 MB >> =C2=A0public.package_files_pkg_id =C2=A0 =C2=A0 =C2=A0| 56 MB >> =C2=A0public.package_files_int8_pkg_id | 56 MB >> =C2=A0public.package_files_pkey =C2=A0 =C2=A0 =C2=A0 =C2=A0| 56 MB >> >> dmcgee=3D# select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 177.078 ms >> dmcgee=3D# select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 176.109 ms >> dmcgee=3D# select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 177.478 ms >> dmcgee=3D# select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 176.639 ms >> dmcgee=3D# select count(*) from (select * from package_files where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 176.453 ms >> >> dmcgee=3D# select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 185.768 ms >> dmcgee=3D# select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 185.159 ms >> dmcgee=3D# select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 184.407 ms >> dmcgee=3D# select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 184.555 ms >> dmcgee=3D# select count(*) from (select * from package_files_int8 where >> pkg_id in (48024, 48025, 40343) order by id) a; >> =C2=A0119325 >> Time: 184.290 ms >> >> >> Overall results: >> >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 i686 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0x86_64 >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0int4 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0int8 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0int4 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0int8 >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0516.558 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 731.194 =C2=A0 =C2=A0 =C2=A0 =C2=A0 177.078 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 185.768 >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0519.72 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0730.329 =C2=A0 =C2=A0 =C2=A0 =C2=A0 176.109 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 185.159 >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0533.33 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0724.646 =C2=A0 =C2=A0 =C2=A0 =C2=A0 177.478 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 184.407 >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0519.095 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 710.815 =C2=A0 =C2=A0 =C2=A0 =C2=A0 176.639 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 184.555 >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0520.253 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 724.519 =C2=A0 =C2=A0 =C2=A0 =C2=A0 176.453 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 184.29 >> >> >> >> Average =C2=A0 =C2=A0 =C2=A0 =C2=A0 521.7912 =C2=A0 =C2=A0 =C2=A0 =C2=A0= 724.3006 =C2=A0 =C2=A0 =C2=A0 =C2=A0176.7514 =C2=A0 =C2=A0 =C2=A0 =C2=A0184= .8358 >> Stddev =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A06.6040841681 =C2=A0 =C2=A08.153= 0512264 =C2=A0 =C2=A00.5359499044 >> =C2=A00.619288059 >> Ratio =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.3= 881042839 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A01.0457388173 >> >> -- >> Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-docs > >