Обсуждение: 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';