Problem with functional indexes

From: Diogo de Oliveira Biazus <diogo(at)ikono(dot)com(dot)br>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with functional indexes
Date: 2003-02-21 19:04:52
Message-ID: 3E567854.3050101@ikono.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)ikono(dot)com(dot)br
Ikono Sistemas e Automação
http://www.ikono.com.br

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-02-21 19:11:41 Re: A problem with sequences...
Previous Message Greg Copeland 2003-02-21 19:00:48 Re: Slow update - index problem?