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 по дате отправления: