pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jim@nasby.net>
To: pgsql-performance@postgresql.org
Subject: Re: How are null's stored? -- Some numbers
Date: Tue, 13 May 2003 16:55:41 -0500
Message-ID: <20030513215541.GC40542@flake.decibel.org> (raw)
In-Reply-To: <20030512190156.GA40542@flake.decibel.org>
References: <20030512190156.GA40542@flake.decibel.org>

I did two experiments. First, as someone mentioned, changing between
char and varchar made absolutely no difference size-wise. In some other
RDBMSes, performance wise char might still win out because the database
wouldn't have to do the math to figure out where in the tuple the fields
are. I know it's splitting hairs, but on what will be a 40M row table...

Second, I modified the table (see below; all fields were originally
nullable):

Before:
usps=# vacuum full analyze verbose zip4_detail;
INFO:  --Relation public.zip4_detail--
INFO:  Pages 12728: Changed 0, reaped 1, Empty 0, New 0; Tup 467140: Vac
0, Keep/VTL 0/0, UnUsed 19, MinLen 154, MaxLen 302; Re-using:
Free/Avail. Space 1009820/264028; EndEmpty/Avail. Pages 0/1521.
        CPU 0.65s/0.86u sec elapsed 1.51 sec.
INFO:  Rel zip4_detail: Pages: 12728 --> 12728; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing public.zip4_detail


After:
INFO:  --Relation public.zip4_detail--
INFO:  Pages 13102: Changed 0, reaped 6961, Empty 0, New 0; Tup 467140:
Vac 0, Keep/VTL 0/0, UnUsed 31795, MinLen 166, MaxLen 306; Re-using:
Free/Avail. Space 1136364/190188; EndEmpty/Avail. Pages 0/1056.
        CPU 0.41s/0.79u sec elapsed 1.20 sec.
INFO:  Rel zip4_detail: Pages: 13102 --> 13102; Tuple(s) moved: 0.
        CPU 0.59s/10.02u sec elapsed 18.17 sec.
INFO:  Analyzing public.zip4_detail

As you can see, space useage actually went up, by 2.9% (pages). In other
words, it appears to be more efficient to store a null than to store an
empty string in a varchar.

usps=# select count(*) from zip4_detail where street_pre_drctn_abbrev=''
and street_suffix_abbrev='' and street_post_drctn_abbrev='';
-------
  9599

usps=# select count(*) from zip4_detail where street_pre_drctn_abbrev=''
or street_suffix_abbrev='';
--------
 128434

(all rows have at least one of the 3 fields empty)

Hope someone finds this info useful... :)

                  Table "public.zip4_detail"
          Column           |         Type          | Modifiers
---------------------------+-----------------------+-----------
 zip_code                  | character varying(5)  |
 update_key_no             | character varying(10) |
 action_code               | character varying(1)  |
 record_type_code          | character varying(1)  |
 carrier_route_id          | character varying(4)  |
 street_pre_drctn_abbrev   | character varying(2)  | not null
 street_name               | character varying(28) |
 street_suffix_abbrev      | character varying(4)  | not null
 street_post_drctn_abbrev  | character varying(2)  | not null
 addr_primary_low_no       | character varying(10) |
 addr_primary_high_no      | character varying(10) |
 addr_prmry_odd_even_code  | character varying(1)  |
 building_or_firm_name     | character varying(40) |
 addr_secondary_abbrev     | character varying(4)  |
 addr_secondary_low_no     | character varying(8)  |
 addr_secondary_high_no    | character varying(8)  |
 addr_secny_odd_even_code  | character varying(1)  |
 zip_add_on_low_no         | character varying(4)  |
 zip_add_on_high_no        | character varying(4)  |
 base_alt_code             | character varying(1)  |
 lacs_status_ind           | character varying(1)  |
 govt_bldg_ind             | character varying(1)  |
 finance_no                | character varying(6)  |
 state_abbrev              | character varying(2)  |
 county_no                 | character varying(3)  |
 congressional_dist_no     | character varying(2)  |
 muncipality_ctyst_key     | character varying(6)  |
 urbanization_ctyst_key    | character varying(6)  |
 prefd_last_line_ctyst_key | character varying(6)  |

-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"




Message-ID: <20030513215541.GC40542@flake.decibel.org>
Permalink:  ../20030513215541.GC40542@flake.decibel.org/
Also on:    postgresql.org/message-id/20030513215541.GC40542@flake.decibel.org

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jim@nasby.net
  Subject: Re: How are null's stored? -- Some numbers
  In-Reply-To: <20030513215541.GC40542@flake.decibel.org>
  References: <20030512190156.GA40542@flake.decibel.org> <20030513215541.GC40542@flake.decibel.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox