On Wed, Aug 29, 2012 at 06:38:47PM -0400, Bruce Momjian wrote: > > I have developed the attached patch based on your observations.
Applied. --------------------------------------------------------------------------- > > On Mon, Apr 30, 2012 at 03:59:20PM -0500, Dan McGee 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 > > -- > Bruce Momjian <[email protected]> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml > new file mode 100644 > index 7f6e661..71cf59e > *** a/doc/src/sgml/datatype.sgml > --- b/doc/src/sgml/datatype.sgml > *************** > *** 453,470 **** > The type <type>integer</type> is the common choice, as it offers > the best balance between range, storage size, and performance. > The <type>smallint</type> type is generally only used if disk > ! space is at a premium. The <type>bigint</type> type should only > ! be used if the range of the <type>integer</type> type is insufficient, > ! because the latter is definitely faster. > ! </para> > ! > ! <para> > ! On very minimal operating systems the <type>bigint</type> type > ! might not function correctly, because it relies on compiler support > ! for eight-byte integers. On such machines, <type>bigint</type> > ! acts the same as <type>integer</type>, but still takes up eight > ! bytes of storage. (We are not aware of any modern > ! platform where this is the case.) > </para> > > <para> > --- 453,460 ---- > The type <type>integer</type> is the common choice, as it offers > the best balance between range, storage size, and performance. > The <type>smallint</type> type is generally only used if disk > ! space is at a premium. The <type>bigint</type> type is designed to be > ! used when the range of the <type>integer</type> type is insufficient. > </para> > > <para> > > -- > Sent via pgsql-docs mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-docs -- Bruce Momjian <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
