Re: help with query!!!

On Mon, 14 Apr 2003, mdc(at)keko(dot)com(dot)ar wrote:

> hi all
>
> im have one query (see below) and not is possible to
> me optimize, so moving to postgres release 7.3.2 but
> icant 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.