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!!!
Date: Mon, 14 Apr 2003 16:29:42 -0700 (PDT)
Message-ID: <20030414162515.B46447-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3e9b3112.22ad.16838@keko.com.ar>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, mdc@keko.com.ar
Subject: Re: help with query!!!
In-Reply-To: <20030414162515.B46447-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox