CODEWITHSUNDEEP
postgresqlbooleandatabase-indexes
Văn Duy Nguyễn
Văn Duy Nguyễn

Reputation: 721

PostgreSQL: Create index for boolean column

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

enter image description here

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:

enter image description here

=> 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?

Upvotes: 72

Views: 49568

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

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.

Upvotes: 194

Related Questions