Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 063E847702C for ; Wed, 29 Jan 2003 10:58:26 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 40372476F54 for ; Wed, 29 Jan 2003 10:56:23 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0TFuR5u026668; Wed, 29 Jan 2003 10:56:27 -0500 (EST) To: Matt Mello Cc: pgsql-performance Subject: Re: 1 char in the world In-reply-to: <3E37733D.90205@spaceship.com> References: <3E37733D.90205@spaceship.com> Comments: In-reply-to Matt Mello message dated "Wed, 29 Jan 2003 00:22:53 -0600" Date: Wed, 29 Jan 2003 10:56:27 -0500 Message-ID: <26667.1043855787@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/381 X-Sequence-Number: 1028 Matt Mello writes: > 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 I don't believe those numbers for a moment. All else being equal, comparing a "char" field to a literal should be exactly the same speed as comparing a bool field to a literal (and if you'd just said "where bool", the bool field would be faster). Both ought to be markedly faster than text. Look for errors in your test procedure. One thing I'd particularly wonder about is whether the query plans are the same. In the absence of any VACUUM ANALYZE data, I'd fully expect the planner to pick a different plan for a bool field than text/char --- because even without ANALYZE data, it knows that a bool column has only two possible values. regards, tom lane