Martin Hampl <Martin.Hampl@gmx.de> writes:
> Do partial indexes not work for varchar?
Works for me:
regression=# create table token(word varchar(30));
CREATE TABLE
regression=# CREATE INDEX word_idx on token (word) where not (word = 'the');
CREATE INDEX
regression=# explain select * from token where word = 'abc' and not (word = 'the');
QUERY PLAN
------------------------------------------------------------------------
Index Scan using word_idx on token (cost=0.00..17.02 rows=5 width=33)
Index Cond: ((word)::text = 'abc'::text)
Filter: ((word)::text <> 'the'::text)
(3 rows)
You may have unrealistic expectations about the planner's ability to
prove that the index predicate condition is implied by the query
WHERE clause. This will not use the index:
regression=# explain select * from token where word = 'abc';
QUERY PLAN
-------------------------------------------------------
Seq Scan on token (cost=0.00..22.50 rows=5 width=33)
Filter: ((word)::text = 'abc'::text)
(2 rows)
You know and I know that "word = 'abc'" implies "not (word = 'the')",
but the planner cannot make that deduction. The pred_test() routine
doesn't really have any intelligence about conditions involving NOT.
regards, tom lane
