Thread: Problem with functional indexes

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


The complete information about the case comes bellow:

CREATE TABLE AgendasBusca (
       codAgendaBusca       SERIAL NOT NULL,
       codBuscaModelo       INT4 NULL,
       hora                 TIMESTAMP NOT NULL,
       dias                 CHAR(7) NOT NULL,
       semanas              CHAR(5) NOT NULL,
       ultimaExecucao       TIMESTAMP NULL,
       PRIMARY KEY (codAgendaBusca),
       FOREIGN KEY (codBuscaModelo)
                             REFERENCES Buscas
);

CREATE OR REPLACE FUNCTION dataFormatada(TIMESTAMP) RETURNS TEXT AS '
  SELECT to_char($1, ''DD/MM/YYYY HH24:MI'');
' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX AgendasBusca_ultimaFormatada_ix on
AgendasBusca(dataFormatada(ultimaExecucao));

->
-> The SQL Query I am trying to execute using the indexes above, is the
following:
->

SELECT
  *
FROM
  AgendasBusca
WHERE
  (to_char(current_timestamp, 'DD/MM/YYYY') || ' 18:45') <>
  dataFormatada(ultimaExecucao)

EXPLAIN ANALYSE resturns:

Seq Scan on agendasbusca  (cost=0.00..146.47 rows=19 width=44) (actual
time=49.90..581.93 rows=19 loops=1)
   Filter: ((to_char(('now'::text)::timestamp(6) with time zone,
'DD/MM/YYYY'::text) || ' 18:45'::text) <> dataformatada(ultimaexecucao))
 Total runtime: 582.66 msec


Thanks in advance,

--
Diogo de Oliveira Biazus
diogo@ikono.com.br
Ikono Sistemas e Automação
http://www.ikono.com.br

On Fri, Feb 21, 2003 at 16:04:52 -0300,
  Diogo de Oliveira Biazus <diogo@ikono.com.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.

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

> On Fri, Feb 21, 2003 at 16:04:52 -0300,
>   Diogo de Oliveira Biazus <diogo@ikono.com.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';