From: | Matt Mello <alien(at)spaceship(dot)com> |
---|---|
To: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | 1 char in the world |
Date: | 2003-01-29 06:22:53 |
Message-ID: | 3E37733D.90205@spaceship.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andras Kadinger | 2003-01-29 07:52:08 | Re: Query plan and Inheritance. Weird behavior |
Previous Message | Tom Lane | 2003-01-29 05:05:15 | Re: Query plan and Inheritance. Weird behavior |