Re: Query analyse

The first query is able to use the index on nr_proponente, because the
condition involves that column directly, the second query is not,
because the index only contains the values of nt_proponente, not results
of trunc(..)/....

Try replacing that condition with something like
pa.nr_proponente BETWEEN op.nr_proponente AND op.nr_proponente + 0.00001

I hope, it helps...

Dima

Elielson Fontanezi wrote:

> Good morning!
>
> 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.1
>
> I would like some suggestions on how to speed up a query.
>
> Both of the queries below are identical except that one of them
> use the *trunc* function.
>
> You can see that the TRUNC function rise hardly up the query
> response time in the second query.
> That shouldn´t be happen. Only because a trunc function?
>
> What can I be in that case?
> What does it happen?
>
> Sure, there are indexes:
>
> CREATE INDEX idx_proposta_2 ON proposta USING btree
> (in_situacao_proposta);
> CREATE INDEX idx_proposta_4 ON proposta USING btree (nr_proponente);
>
> And pa.nr_proponente is fk and op.nr_proponte is pk.
>
> These are the queries:
>
> 1o. That is ok.
>
> 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 (pa.nr_proponente = op.nr_proponente)
> where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC;
>
> DEBUG: QUERY STATISTICS
> ! system usage stats:
> ! 0.015904 elapsed 0.000000 user 0.020000 system sec
> ! [0.010000 user 0.020000 sys total]
> ! 0/0 [0/0] filesystem blocks in/out
> ! 143/42 [353/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: 88 read, 0 written, buffer hit
> rate = 89.19
> %
> ! Local blocks: 0 read, 0 written, buffer hit
> rate = 0.00%
> ! Direct blocks: 0 read, 0 written
> 2o. But I need to use the trunc function:
>
> 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
>