pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction index qeustion
2+ messages / 2 participants
[nested] [flat]
* Function index qeustion
@ 2003-07-25 20:26 Jonathan Bartlett <johnnyb@eskimo.com>
2003-07-25 23:41 ` Re: Function index qeustion Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Jonathan Bartlett @ 2003-07-25 20:26 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; pgsql-general <pgsql-general@postgresql.org>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Function index qeustion
2003-07-25 20:26 Function index qeustion Jonathan Bartlett <johnnyb@eskimo.com>
@ 2003-07-25 23:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-07-25 23:41 UTC (permalink / raw)
To: Jonathan Bartlett <johnnyb@eskimo.com>; +Cc: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; pgsql-general <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-25 23:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-25 20:26 Function index qeustion Jonathan Bartlett <johnnyb@eskimo.com>
2003-07-25 23:41 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox