Re: [SQL] [GENERAL] Query analyse

[Dmitry Tkach]

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
 




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly