Sample Header Ad - 728x90

Unexpected Seq Scan when doing query against boolean with value NULL

11 votes
1 answer
5175 views
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.
Asked by Simone Carletti (341 rep)
Oct 26, 2012, 03:22 PM
Last activity: Mar 3, 2016, 01:00 AM