Re: partial index on varchar-coloumn in 7.4.1

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