Date: Fri, 25 Jul 2003 13:26:57 -0700 (PDT)
From: Jonathan Bartlett <johnnyb@eskimo.com>
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
Cc: "'Stephan Szabo'" <sszabo@megazone.bigpanda.com>,
	pgsql-general <pgsql-general@postgresql.org>,
	pgsql-sql <pgsql-sql@postgresql.org>
Subject: Function index qeustion
In-Reply-To: <A799F7647794D311924A005004ACEA9709699788@cprodamibs249.prodam>
Message-ID: <Pine.GSU.4.44.0307251325080.14162-100000@eskimo.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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
>