From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: "mdc@keko.com.ar" <mdc@keko.com.ar>, pgsql-sql@postgresql.org
Subject: Re: help with query!!!
Date: Mon, 14 Apr 2003 19:41:48 -0300
User-Agent: KMail/1.5
References: <3e9b3112.22ad.16838@keko.com.ar>
In-Reply-To: <3e9b3112.22ad.16838@keko.com.ar>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg=pgp-sha1; boundary="Boundary-02=_tkzm+Rbrn/2Ub7o";
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200304141941.49407.franco@akyasociados.com.ar>

--Boundary-02=_tkzm+Rbrn/2Ub7o
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: signed data
Content-Disposition: inline

do you work for keko? I'm glad to see people from argentina using postgresq=
l.

I see you have created many indexes; first of all you should drop them, jus=
t=20
leave the primary key. The more indexes you've, the more work the database=
=20
has to do to keep them up to date.

When you've done that, you have to *ALWAYS* reemember  to ANALYZE your tabl=
es=20
after your create an index; try doing a:
ANALYZE VERBOSE transitos;

and then, use again the explain delete ... inside a transaction to see the=
=20
results, like this:

BEGIN;
EXPLAIN
   DELETE FROM transitos
   WHERE=20
      codigoEstacion=3D'02' AND
      numeroVia=3D1 AND
      fechaHora=3D'2003-0403 17:34:06.92'::TIMESTAMP AND
      medioPago=3D'Efectivo' AND
      tipoTransito=3D'Normal' AND
      categoria=3D'01'
ROLLBACK;

if it's using the index now, then stop reading.

If it's still doing a sequential scan on the table, try this:
SET enable_seqscan TO OFF;
BEGIN;
EXPLAIN
   DELETE FROM transitos
   WHERE=20
      codigoEstacion=3D'02' AND
      numeroVia=3D1 AND
      fechaHora=3D'2003-0403 17:34:06.92'::TIMESTAMP AND
      medioPago=3D'Efectivo' AND
      tipoTransito=3D'Normal' AND
      categoria=3D'01'
ROLLBACK;

and check the results (actual time). If the actual time is lower (it means=
=20
that it SHOULD be using indexes), you should tweak the cpu_index_tuple_cost
configuration param to adjust it to your system needs.

If you didn't understand anything, you have my email, so write me and I'll=
=20
explain you in spanish.... my english is not so good either :P

On Monday 14 April 2003 19:07, mdc@keko.com.ar wrote:
> hi all
>
>  i=B4m have one query (see below) and not is possible to
> me optimize, so moving to postgres release 7.3.2 but
> i=B4cant optimize the query an don't understood why
>
> additional data.
> the table shape
>
>                  Table "public.Transitos"
>      Column      |            Type             |
> Modifiers
> -----------------+-----------------------------+-----------
>  codigoEstacion  | character(2)                | not
> null
>  numeroVia       | smallint                    | not
> null
>  fechaHora       | timestamp(3) with time zone | not
> null
>  medioPago       | character varying(50)       | not
> null
>  tipoTransito    | character(20)               | not
> null
>  categoria       | character(20)               | not
> null
>  controlTransito | character varying(50)       | not
> null
>  controlPago     | character varying(50)       | not
> null
>  descripcion     | character varying(150)      | not
> null
> Indexes: transitos_pkey primary key btree
> ("codigoEstacion", "numeroVia", "fechaHora",
> "medioPago", "tipoTransito", categoria),
>          i_t2 btree ("codigoEstacion", "numeroVia",
> "fechaHora", "medioPago", "tipoTransito", categoria),
>          i_transitos btree ("codigoEstacion",
> "numeroVia", "fechaHora", "medioPago", "tipoTransito",
> categoria),
>          it_3 btree ("codigoEstacion", "numeroVia",
> "fechaHora", "tipoTransito", "medioPago", categoria)
>
> the querie:
>
> explain delete from "Transitos"
> where "codigoEstacion"=3D '02' and
> "numeroVia" =3D 1 and
> "fechaHora" =3D '2003-0403 17:34:06.92'::timestamp and
> "medioPago" =3D 'Efectivo' and
> "tipoTransito"=3D 'Normal' and
> categoria=3D'01'
>
>
> result:
>  Seq Scan on "Transitos"  (cost=3D0.00..10612.03 rows=3D1
> width=3D6)
>    Filter: (("codigoEstacion" =3D '02'::bpchar) AND
> ("numeroVia" =3D 1) AND ("fechaHora" =3D ('2003-04-03
> 17:34:06.92'::timestamp without time zone)::timestamp
> with time zone) AND ("medioPago" 'Efectivo'::character varying) AND
> ("tipoTransito" 'Normal'::bpchar) AND (categoria =3D '01'::bpchar)) (2 ro=
ws)
>
>
>
>
> question: why them perform one seq scan ? if you see
> have index covering where expression ?
> any ideas?
>
> TIA
>
> PD: SORRY FOR MY ENGLISH IS NOT MY MOTHER LANGUAGE =3D;o)
>
> ---------------------------------------
> =BFTodav=EDa no naveg=E1s con Keko?
> Hac=E9 click aqu=ED: http://www.keko.com.ar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--Boundary-02=_tkzm+Rbrn/2Ub7o
Content-Type: application/pgp-signature
Content-Description: signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+mzktz/PwDAvtu7oRAmN2AJ9Yhl8GRz7YKS3tebZTmyaP8dY57wCfQXMO
KE5W0IcZaCC7KTNcWJGDcYw=
=C/Dz
-----END PGP SIGNATURE-----

--Boundary-02=_tkzm+Rbrn/2Ub7o--