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
>