Re: Problem with functional indexes

On Fri, 21 Feb 2003, Bruno Wolff III wrote:

> On Fri, Feb 21, 2003 at 16:04:52 -0300,
> Diogo de Oliveira Biazus <diogo(at)ikono(dot)com(dot)br> wrote:
> > Hi everybody,
> > I'm having a problem with functional indexes.
> >
> > When I compare the function index using the "=" operator, it uses the index;
> > Otherwise, if I use the "<>" operator it uses SeqScan...even when i set
> > enable_seqscan to off.
> >
> > Ex.:
> > SELECT * FROM MyTable WHERE myFunction(myField) = 'just testing';
> > -> Works just fine.
> > SELECT * FROM MyTable WHERE myFunction(myField) <> 'just testing';
> > -> All I get is SeqScan...
>
> I don't believe <> functions are generally going to be able to use indexes.
> However, you really don't want to. The figure I have seen on these lists
> is that if you are going to hit over 10% of the records a sequencial
> scan is probably going to be faster.

The general rule of thumb is that the where condition in the select and
the create index need to match. So, creating a functional index like:

create index test on table (field2) where function (field2) <> 'someval';

should work for

select * from table where function(field2) <> 'someval';