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