From: Dmitry Tkach <dmitry@openratings.com>
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Query analyse
Date: Fri, 25 Jul 2003 12:05:31 -0400
Message-ID: <3F21554B.5020604@openratings.com> (raw)
In-Reply-To: <A799F7647794D311924A005004ACEA9709699779@cprodamibs249.prodam>
References: <A799F7647794D311924A005004ACEA9709699779@cprodamibs249.prodam>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, ElielsonF@prodam.sp.gov.br, pgsql-general@postgresql.org
Subject: Re: Query analyse
In-Reply-To: <3F21554B.5020604@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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