Strange behavior for boolean predicates and partial indexes

From: Patrick Clery <etc(at)phpforhire(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Strange behavior for boolean predicates and partial indexes
Date: 2005-03-26 11:36:19
Message-ID: 200503260436.19236.etc@phpforhire.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2005-03-26 18:37:28 Re: Strange behavior for boolean predicates and partial indexes
Previous Message Tom Lane 2005-03-25 16:09:30 Re: [BUGS] CC Date format code defaults to current centry