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
Last activity: Mar 3, 2016, 01:00 AM