Re: partial index on varchar-coloumn in 7.4.1

Hi,

Ah. Thanks. I wouldn't have thought of that. That's a solution I can
work with (if not a very elegant one).

Regard,
Martin.




Am 07.01.2004 um 16:29 schrieb Tom Lane:

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