Обсуждение: RES: ERROR: DefineIndex: index function must be marked iscachable
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:sszabo@megazone.bigpanda.com]
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.
Questions: 1) If you have an index on a cacheable function, does PostgreSQL use the index instead of calculating the results? 2) How does PostgreSQL know when to recompute the function? Jon On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > 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:sszabo@megazone.bigpanda.com] > 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 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
Jonathan Bartlett <johnnyb@eskimo.com> writes:
> 1) If you have an index on a cacheable function, does PostgreSQL use the
> index instead of calculating the results?
Not in general --- only for an indexscan lookup.
> 2) How does PostgreSQL know when to recompute the function?
Never. That's what the iscachable flag means: you are promising that
the function's output for given input never changes. If you aren't
prepared to make that promise, you cannot index the function.
regards, tom lane