Unexpected Seq Scan when doing query against boolean with value NULL

Question

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM.

CREATE INDEX index_table_on_auto_renew ON table USING btree (auto_renew);

When I query the field for a boolean value, PG uses the index as expected.

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" = 'f'
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table  (cost=51.65..826.50 rows=28039 width=186)
   Filter: (NOT auto_renew)
   ->  Bitmap Index Scan on index_domains_on_auto_renew  (cost=0.00..44.64 rows=2185 width=0)
         Index Cond: (auto_renew = false)
(4 rows)

When the value is NULL, a sequential scan is used.

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" IS NULL
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on table  (cost=0.00..1094.01 rows=25854 width=186)
   Filter: (auto_renew IS NULL)
(2 rows)

I'm curious to know the reason behind this choice.

Answer 1

Generally, col IS NULL is a possible candidate for a (default) b-tree index search. The manual:

Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.

To get proof, disable sequential scans (in a test session only!):

SET enable_seqscan = OFF;

I quote the manual here:

enable_seqscan (boolean)

Enables or disables the query planner's use of sequential scan plan types. It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available. The default is on.

Then try again:

EXPLAIN ANALYZE SELECT * FROM tbl WHERE auto_renew IS NULL;

This will probably result in a bitmap index scan that is slower than a sequential scan on the table.

Reset, or close the session (the setting is session-local).

RESET enable_seqscan;

Indexes on boolean columns are only useful in certain cases. The planner only uses an index if it expects that to be faster. Calculations are based on your cost settings and the statistics gathered by ANALYZE. If a sizable portion of the table matches your condition (around 5% or more, it depends), it is typically faster to do a full table scan instead.

This leaves the rare value in a boolean column as the only useful candidate for a plain index. And it is typically more efficient to create a (more specialized) partial index instead for this - which is cheaper to maintain, smaller, faster and used more readily if query condition match.

If you have lots of queries looking for rows with auto_renew IS NULL and the NULL case is not very common (and / or you need a certain sort order) then this index would help to find / sort these rows quickly:

CREATE INDEX index_tbl_tbl_id_auto_renew_null ON tbl (tbl_id)
WHERE auto_renew IS NULL;

The condition of the partial index has to be repeated in the WHERE clause of a query more or less exactly to make the query planner realize the index is applicable.

The indexed column (tbl_id) is an arbitrary pick. The important part is the WHERE clause. This particular index would be most effective for queries with ORDER BY tbl_id or an additional filter or join on tbl_id. You could make it a multicolumn index. Boolean columns are often more useful in combination with others.

Aside: ORMs are crutches that regularly fail to get the full potential out of your RDBMS.