Re: partial index on varchar-coloumn in 7.4.1

Martin Hampl <Martin(dot)Hampl(at)gmx(dot)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