help with query!!!

From: "mdc(at)keko(dot)com(dot)ar" <mdc(at)keko(dot)com(dot)ar>
To: pgsql-sql(at)postgresql(dot)org
Subject: help with query!!!
Date: 2003-04-14 22:07:14
Message-ID: 3e9b3112.22ad.16838@keko.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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'

result:
Seq Scan on "Transitos" (cost=0.00..10612.03 rows=1
width=6)
Filter: (("codigoEstacion" = '02'::bpchar) AND
("numeroVia" = 1) AND ("fechaHora" = ('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 = '01'::bpchar))
(2 rows)

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 =;o)

---------------------------------------
Todava no navegs con Keko?
Hac click aqu: http://www.keko.com.ar

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Franco Bruno Borghesi 2003-04-14 22:41:48 Re: help with query!!!
Previous Message Franco Bruno Borghesi 2003-04-14 17:16:08 Re: connecting to a differnt DB in PL/pgSQL