Обсуждение: help with query!!!
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
nullnumeroVia | smallint | not
nullfechaHora | timestamp(3) with time zone | not
nullmedioPago | character varying(50) | not
nulltipoTransito | character(20) | not
nullcategoria | character(20) | not
nullcontrolTransito | character varying(50) | not
nullcontrolPago | character varying(50) | not
nulldescripcion | 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)
---------------------------------------
�Todav�a no naveg�s con Keko?
Hac� click aqu�: http://www.keko.com.ar
do you work for keko? I'm glad to see people from argentina using postgresql.
I see you have created many indexes; first of all you should drop them, just
leave the primary key. The more indexes you've, the more work the database
has to do to keep them up to date.
When you've done that, you have to *ALWAYS* reemember to ANALYZE your tables
after your create an index; try doing a:
ANALYZE VERBOSE transitos;
and then, use again the explain delete ... inside a transaction to see the
results, like this:
BEGIN;
EXPLAIN DELETE FROM transitos WHERE codigoEstacion='02' AND numeroVia=1 AND fechaHora='2003-0403
17:34:06.92'::TIMESTAMPAND medioPago='Efectivo' AND tipoTransito='Normal' AND categoria='01'
ROLLBACK;
if it's using the index now, then stop reading.
If it's still doing a sequential scan on the table, try this:
SET enable_seqscan TO OFF;
BEGIN;
EXPLAIN DELETE FROM transitos WHERE codigoEstacion='02' AND numeroVia=1 AND fechaHora='2003-0403
17:34:06.92'::TIMESTAMPAND medioPago='Efectivo' AND tipoTransito='Normal' AND categoria='01'
ROLLBACK;
and check the results (actual time). If the actual time is lower (it means
that it SHOULD be using indexes), you should tweak the cpu_index_tuple_cost
configuration param to adjust it to your system needs.
If you didn't understand anything, you have my email, so write me and I'll
explain you in spanish.... my english is not so good either :P
On Monday 14 April 2003 19:07, 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'
>
>
> 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)
>
> ---------------------------------------
> ¿Todavía no navegás con Keko?
> Hacé click aquí: http://www.keko.com.ar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
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.
"mdc@keko.com.ar" <mdc@keko.com.ar> writes:
> numeroVia | smallint | not null
> fechaHora | timestamp(3) with time zone | not null
> 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'
You're casting the constant compared to fechaHora to timestamp, which is
the wrong thing (timestamp != timestamp with timezone); and you're not
casting the constant compared to numeroVia to smallint. Each of these
type mismatches will prevent an indexscan.
If I were you I'd declare numeroVia as int, not smallint, because you're
getting no space savings from smallint anyway. Then you could just
write
delete from "Transitos"
where "codigoEstacion"= '02' and
"numeroVia" = 1 and
"fechaHora" = '2003-0403 17:34:06.92' and
"medioPago" = 'Efectivo' and
"tipoTransito"= 'Normal' and
categoria='01'
and it should do what you want.
regards, tom lane
hi guys > numeroVia | smallint | not null > fechaHora | timestamp(3) with time zone | not null > 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' >You're casting the constant compared to fechaHora to >timestamp, which is >the wrong thing (timestamp != timestamp with timezone); and >you're not >casting the constant compared to numeroVia to smallint. >Each of these >type mismatches will prevent an indexscan. ok , first thanks a lot for your responses, wellnot my are doing this wrong castings, they are from ODBC driver , my analisys was from server side, i mean, this information is send from my aplication via ODBC to postmaster . For research propouses start the postmaster with debug level 3 and later ,recreate this queries with additional explain clauses for analisis, the ::timestamp in syntax not in my own code ok ? . well questions i plan to change this castings , modifing the database definition numeroVia smallint -> to int4 fechaHora timeStamp(3) with timezone -> timeStamp(3) * * milliseconds are very important to me, the aplication register events at real time and millisecond are difference into 2 events , application is multithread . last question, is posible to make this changes whitout download the database at all ? , the alter table ..... do it? Tia MDC pd: Sorry for my english , again ;) --------------------------------------- �Todav�a no naveg�s con Keko? Hac� click aqu�: http://www.keko.com.ar