Strange behavior for boolean predicates and partial indexes
От | Patrick Clery |
---|---|
Тема | Strange behavior for boolean predicates and partial indexes |
Дата | |
Msg-id | 200503260436.19236.etc@phpforhire.com обсуждение исходный текст |
Ответы |
Re: Strange behavior for boolean predicates and partial indexes
|
Список | 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?
В списке pgsql-bugs по дате отправления: