Re: Indexing a boolean

On Thu, Aug 21, 2003 at 15:47:39 -0500,
Kris Kiger <kris(at)musicrebellion(dot)com> 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!

You probably don't want to use an index unless one of the two values
is much more common than the other. In that case you want to use a partial
index on the rare condition.