col IS NULL is a possible candidate for a (default) b-tree index search. The manual:
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:
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).
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.