Message-ID: <3F21554B.5020604@openratings.com>
Date: Fri, 25 Jul 2003 12:05:31 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
Cc: pgsql-sql <pgsql-sql@postgresql.org>,
	pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Query analyse
References: <A799F7647794D311924A005004ACEA9709699779@cprodamibs249.prodam>
In-Reply-To: <A799F7647794D311924A005004ACEA9709699779@cprodamibs249.prodam>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

The first query is able to use the index on nr_proponente, because the=20
condition involves that column directly, the second query is not,=20
because the index only contains the values of nt_proponente, not results=20
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!
>=20=20
>     First of all, my envoronment is:
>         Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST=20
> 2001 i686 unknown
>         pg_ctl (PostgreSQL) 7.2.1
>=20=20
>     I would like some suggestions on how to speed up a query.
>=20=20
>     Both of the queries below are identical except that one of them=20
> use the *trunc* function.
>=20=20
>     You can see that the TRUNC function rise hardly up the query=20
> response time in the second query.
>     That shouldn=B4t be happen. Only because a trunc function?
>=20=20
>     What can I be in that case?
>     What does it happen?
>=20=20
>     Sure, there are indexes:
>=20=20
> CREATE INDEX idx_proposta_2 ON proposta USING btree=20
> (in_situacao_proposta);
> CREATE INDEX idx_proposta_4 ON proposta USING btree (nr_proponente);
>=20=20
>     And pa.nr_proponente is fk and op.nr_proponte is pk.
>=20=20
>     These are the queries:
>=20=20
> 1o.  That is ok.
>=20=20
> 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 =3D 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=20
> rate =3D 89.19
> %
> !       Local  blocks:          0 read,          0 written, buffer hit=20
> rate =3D 0.00%
> !       Direct blocks:          0 read,          0 written
> 2o. But I need to use the trunc function:
>=20=20
> 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 =3D 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=20
> rate =3D 13.23
> %
> !       Local  blocks:          0 read,          0 written, buffer hit=20
> rate =3D 0.00%
> !       Direct blocks:          0 read,          0 written
>=20=20