Re: Indexing a boolean

On Thu, 21 Aug 2003, Kris Kiger wrote:

> I would appreciate it if I could get some thoughts on indexing a field
> with only two values? For example, I have a table with a few million
> rows in it. All items in this table are broken up into two categories
> using 'T' or 'F'. It seems logical to me that an index on this field
> would create two logical 'buckets', so that one could say, "I want all
> 'T' values", or "I want all 'F' values" and merely have to look in the
> appropriate bucket, rather than have to perform a sequential scan
> through three million items every time a request is made based on 'T' or
> 'F'. If I were to create an index on a char(1) field that contains only
> values of 'T' or 'F', would the query analyzer override the use of this
> index? How does Postgres address this problem and what are all of your
> thoughts on this issue? I appreciate the help!

Often the best approach here is to make a partial index:

create index table_dx on table (bool_field) where bool_field IS TRUE;

This works well if you have a large portion of the boolean fields set to
FALSE, and want to find the few that are TRUE. Reverse the TRUE and false
for other situations.