PostgreSQL: Create index for boolean column

PostgreSQL will use an index only if it thinks it will be cheaper that way.
An index on a boolean column, which can only take two possible values, will almost never be used, because it is cheaper to sequentially read the whole table than to use random I/O on the index and the table if a high percantage of the table has to be retrieved.

An index on a boolean column is only useful

1. in data warehouse scenarios, where it can be combined with other indexes via a bitmap index scan.

2. if only a small fraction of the table has the value TRUE (or FALSE for that matter). In this case it is best to create a partial index like

    CREATE INDEX ON mytab((1)) WHERE boolcolumn;
   

   That indexed value is the constant 1 (index expressions that are not column references or look like a function call have to be in an extra pair of parentheses for syntactical reasons). The only relevant part about this index is its WHERE condition. The constant 1 is there just because something has to be indexed. If you have a (small) column that you can use instead, do it.