Matt Mello <alien@spaceship.com> 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