Re: help with query!!! - Mailing list pgsql-sql

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