pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matt Mello <alien@spaceship.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: 1 char in the world
Date: Wed, 29 Jan 2003 00:22:53 -0600
Message-ID: <3E37733D.90205@spaceship.com> (raw)

TEXT vs "char" ... vs BOOLEAN

I am porting from Informix to PG.  In doing so, I had to pick some data 
types for fields, and began wondering about the performance of char/text 
fields with one character.  For example, I have a field which has one of 
the following values/states: {'A', 'D', 'F', 'U'}.  Since CHAR(n), 
VARCHAR, and TEXT are all supposed to have the same performance 
according to the docs, it seems that they will all perform the same. 
For this reason, I did not squabble over which one of these to use. 
However, since "char" is implemented differently, I thought I would 
compare it to one of the others.  I chose to pit TEXT against "char".

Test query = explain analyze select count(*) from table where onechar='D';
Table size = 512 wide [mostly TEXT] * 400000 rows
Performance averages:
  "char"  44ms
  TEXT    63ms

This seems somewhat reasonable, and makes me want to use "char" for my 
single-char field.  Does everyone else find this to be reasonable?  Is 
this pretty much the behavior I can expect on extraordinarily large 
tables, too?  And, should I worry about things like the backend 
developers removing "char" as a type later?

--

This naturally led me to another question.  How do TEXT, "char", and 
BOOLEAN compare for storing t/f values.  The test results I saw were 
surprising.

Test query=
  "char"/TEXT: explain analyze select count(*) from table where bool='Y';
  boolean:    explain analyze select count(*) from table where bool=true;
Table size (see above)
Performance averages:
  TEXT    24ms
  BOOLEAN 28ms
"char"   17ms

Why does boolean rate closer to TEXT than "char"?  I would think that 
BOOLEANs would actually be stored like "char"s to prevent using the 
extra 4 bytes with TEXT types.

Based on these results, I will probably store my booleans as "char" 
instead of boolean.  I don't use stored procedures with my application 
server, so I should never need my booleans to be the BOOLEAN type.  I 
can convert faster in my own code.

--

NOTE: the above tests all had the same relative data in the different 
fields (what was in TEXT could be found in "char", etc.) and were all 
indexed equally.


Thanks!

-- 
Matt Mello







view thread (7+ messages)  latest in thread

Message-ID: <3E37733D.90205@spaceship.com>
Permalink:  ../3E37733D.90205@spaceship.com/
Also on:    postgresql.org/message-id/3E37733D.90205@spaceship.com

 · 

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: alien@spaceship.com
  Subject: Re: 1 char in the world
  In-Reply-To: <3E37733D.90205@spaceship.com>
  References: <3E37733D.90205@spaceship.com>

* 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