Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5F6D2477109 for ; Wed, 29 Jan 2003 03:14:07 -0500 (EST) Received: from ms-smtp-01.texas.rr.com (ms-smtp-01.texas.rr.com [24.93.36.229]) by postgresql.org (Postfix) with ESMTP id B8FA147A11A for ; Wed, 29 Jan 2003 01:22:53 -0500 (EST) Received: from spaceship.com (cs24243214-140.austin.rr.com [24.243.214.140]) by ms-smtp-01.texas.rr.com (8.12.5/8.12.2) with ESMTP id h0T6I0Hs016054 for ; Wed, 29 Jan 2003 01:18:00 -0500 (EST) Message-ID: <3E37733D.90205@spaceship.com> Date: Wed, 29 Jan 2003 00:22:53 -0600 From: Matt Mello User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance Subject: 1 char in the world Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/377 X-Sequence-Number: 1024 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