I have a partial index that contains a predicate to check for whether the
field deleted is false or not:
CREATE INDEX people_essays_any_essaytype_idx
ON people_essays (person_id)
WHERE NOT deleted;
The following query does NOT use the index:
EXPLAIN ANALYZE
SELECT *
FROM people_essays
WHERE person_id = 1
AND deleted IS FALSE;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on people_essays (cost=0.00..10225.85 rows=4 width=67) (actual
time=110.205..417.113 rows=4 loops=1)
Filter: ((person_id = 1) AND (deleted IS FALSE))
Total runtime: 417.203 ms
(3 rows)
EXPLAIN ANALYZE
SELECT *
FROM people_essays
WHERE person_id = 1
AND deleted = FALSE;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using people_essays_uniq on people_essays (cost=0.00..18.06
rows=4 width=67) (actual time=35.094..35.971 rows=4 loops=1)
Index Cond: (person_id = 1)
Filter: (deleted = false)
Total runtime: 36.070 ms
(4 rows)
EXPLAIN ANALYZE
SELECT *
FROM people_essays
WHERE person_id = 1
AND NOT deleted;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using people_essays_any_essaytype_idx on people_essays
(cost=0.00..18.05 rows=4 width=67) (actual time=0.034..0.047 rows=4 loops=1)
Index Cond: (person_id = 1)
Filter: (NOT deleted)
Total runtime: 0.136 ms
(4 rows)
Though the index was created with "NOT deleted", shouldn't the planner
evaluate "IS FALSE" as the same if "= FALSE" works?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
