Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 13 down vote accepted

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

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 table WHERE auto_renew IS NULL;

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

Reset, or close session (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 calculates it'll be faster. (Calculations are based on your cost settings and the statistics gathered by ANALYZE, btw.) If a sizable portion of the table matches your condition (around 5% or more, it depends), it is regularly 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 regularly more efficient to create a (more specialized) partial index instead for such a case - which are cheaper, smaller, more effective and used more readily (if the condition matches!).

Like, 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_table_table_id_auto_renew ON tbl (tbl_id)
WHERE auto_renew IS NULL;

Remember that the condition of the partial index has to be repeated in the WHERE clause of a query more or less precisely to make the query planner realize both are compatible.

This particular index would be most effective for queries with ORDER BY table_id or an additional filter table_id = 123. There are also multi-column indices. Boolean columns are often more useful in combination with others.

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

share|improve this answer
    
Brilliant answer, thank you Erwin. I'm sad I can't upvote it twice. –  Simone Carletti Oct 29 '12 at 10:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.