X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4628D474E44
	for <pgsql-sql@postgresql.org>; Mon, 14 Apr 2003 19:29:34 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 5B183475FEE
	for <pgsql-sql@postgresql.org>; Mon, 14 Apr 2003 19:29:03 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id E3E98D600; Mon, 14 Apr 2003 16:29:42 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id D58CD5C04; Mon, 14 Apr 2003 16:29:42 -0700 (PDT)
Date: Mon, 14 Apr 2003 16:29:42 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: "mdc@keko.com.ar" <mdc@keko.com.ar>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: help with query!!!
In-Reply-To: <3e9b3112.22ad.16838@keko.com.ar>
Message-ID: <20030414162515.B46447-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=X-UNKNOWN
Content-Transfer-Encoding: QUOTED-PRINTABLE
X-Spam-Status: No, hits=-14.0 required=5.0
	tests=BAYES_00,EMAIL_ATTRIBUTION,IN_REP_TO,PLING_PLING,
	TO_ADDRESS_EQ_REAL autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/213
X-Sequence-Number: 12914


On Mon, 14 Apr 2003, 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'

I think it's only currently going to consider the codigoEstacion=3D'02'
as indexable which may not be selective enough to make it consider
the index.  The 1 is being read as an int4 (see discussions in archives)
rather than a smallint and so you should probably cast it explicitly
(1::smallint).  Also fechaHora is timestamp with time zone, but I
believe the right side of that is timestamp without time zone.  You may
need to change that as well.