Problem with functional indexes

Поиск
Список
Период
Сортировка
От Diogo de Oliveira Biazus
Тема Problem with functional indexes
Дата
Msg-id 3E567854.3050101@ikono.com.br
обсуждение исходный текст
Ответы Re: Problem with functional indexes
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Greg Copeland
Дата:
Сообщение: Re: Slow update - index problem?
Следующее
От: Dmitry Tkach
Дата:
Сообщение: Re: A problem with sequences...