Skip site navigation (1) Skip section navigation (2)

Re: partial index on varchar-coloumn in 7.4.1

From: Martin Hampl <Martin(dot)Hampl(at)gmx(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: partial index on varchar-coloumn in 7.4.1
Date: 2004-01-08 10:28:07
Message-ID: 59CA42C0-41C5-11D8-B2CE-000393674318@gmx.de (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-novice
Cool :)

Martin.

Am 07.01.2004 um 23:35 schrieb Tom Lane:

> I wrote:
>> Martin Hampl <Martin(dot)Hampl(at)gmx(dot)de> writes:
>>> Do partial indexes not work for varchar?
>
>> 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.
>
> Actually, this was easier to fix than I thought.  As of CVS tip:
>
> 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' ;
>                                QUERY PLAN
> ----------------------------------------------------------------------- 
> -
>  Index Scan using word_idx on token  (cost=0.00..17.07 rows=5 width=33)
>    Index Cond: ((word)::text = 'abc'::text)
> (2 rows)
>
> regression=#
>
> There's still no intelligence about NOT in the theorem prover, but it
> turns out that it's not seeing NOT.  By the time the expressions get to
> the point of being compared, NOT (a=b) has been folded to a<>b, and it
> turned out to be fairly straightforward to extend the existing logic to
> reason about such cases.  The above example requires a process like
> "a = x implies a <> y if x <> y" (where x and y are constants, so the
> "if" part can be checked).  This fits right in with what the code could
> do already, which was cases like "a > x implies a > y if x > y".
> So it'll work more naturally in 7.5.
>
> 			regards, tom lane
>


In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2004-01-08 14:44:47
Subject: Re: pgsql 7.0 recovery to 7.4
Previous:From: Marcin GilDate: 2004-01-08 07:16:40
Subject: pgsql 7.0 recovery to 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group