Thanks a lot! The complete solution is here!
1st. The function wich substitute the trunc() function CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' DECLARE v_nr_proponente ALIAS FOR $1; BEGIN return TRUNC(v_nr_proponente/100000,0)*100000; END; ' LANGUAGE 'plpgsql' WITH isCachable; 2nd. The index on that function CREATE INDEX bt_proposta_f01 ON proposta USING BTREE (func_cod_secretaria(nr_proponente)); 3rd. The anlysis of both queries: the old and new one. a) the old query: --------------------------------------------------------------- DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (trunc(pa.nr_proponente/100000,0)*100000 = op.nr_proponente) where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC; DEBUG: QUERY STATISTICS ! system usage stats: ! 104.665005 elapsed 10.090000 user 0.420000 system sec ! [10.100000 user 0.420000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 141/50 [352/180] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 7408 read, 0 written, buffer hit rate = 13.23 % ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written --------------------------------------------------------------- b) the new query --------------------------------------------------------------- DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (op.nr_proponente = func_cod_secretaria(pa.nr_proponente)) where pa.in_situacao_proposta <> 'E'; DEBUG: query: SELECT TRUNC( $1 /100000,0)*100000 DEBUG: QUERY STATISTICS ! system usage stats: ! 0.130885 elapsed 0.020000 user 0.010000 system sec ! [0.020000 user 0.020000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 168/68 [369/172] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 142 read, 1 written, buffer hit rate = 88.10 % ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written --------------------------------------------------------------- -----Mensagem original----- De: Stephan Szabo [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 25 de julho de 2003 16:47 Para: Elielson Fontanezi Cc: pgsql-general; pgsql-sql Assunto: Re: [GENERAL] ERROR: DefineIndex: index function must be marked iscachable On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > Who can help me on that? > > First of all, my envoronment is: > Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 > i686 unknown > pg_ctl (PostgreSQL) 7.2. You should definately move to the highest 7.2 release (7.2.4 I think) which shouldn't require a restore (although you should back up first in any case). I think there were some reasonably important fixes between 7.2.1 and 7.2.4. > CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' > DECLARE > v_nr_proponente ALIAS FOR $1; > BEGIN > return TRUNC(v_nr_proponente/100000,0)*100000 > END; > ' LANGUAGE 'plpgsql' Add WITH (iscachable) after LANGUAGE 'plpgsql' to make the function usable in the functional index unless I'm misremembering the old syntax. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster