PostgreSQL: Create index for boolean column

Question

I have a table that has one boolean column.

productid integer isactive boolean 

When I execute the query

SELECT productid        FROM      product       WHERE ispublish     LIMIT 15 OFFSET  0 

After that, I created an index for the ispublish column:

CREATE INDEX idx_product_ispublish ON product USING btree (ispublish) 

and re-execute

SELECT productid           FROM         product          WHERE ispublish        LIMIT 15 OFFSET  0 

The result:

=> No difference

I've been tried the following, but the results are the same:

CREATE INDEX idx_product_ispublish ON product USING btree (ispublish)  CREATE INDEX idx_product_ispublish ON product USING btree (ispublish)  CREATE INDEX idx_product_ispublish ON product (ispublish) WHERE ispublish is TRUE 

Who can explain that to me?

Answer 1

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;