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?
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 |