Re: help with query!!!

On Mon, 14 Apr 2003, mdc@keko.com.ar wrote:

> hi all
>
>  i�m have one query (see below) and not is possible to
> me optimize, so moving to postgres release 7.3.2 but
> i�cant 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"= '02' and
> "numeroVia" = 1 and
> "fechaHora" = '2003-0403 17:34:06.92'::timestamp and
> "medioPago" = 'Efectivo' and
> "tipoTransito"= 'Normal' and
> categoria='01'

I think it's only currently going to consider the codigoEstacion='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.