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;