From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: mdc@keko.com.ar <mdc@keko.com.ar>
To: pgsql-sql@postgresql.org
Subject: Re: help with query!!!
Date: Mon, 14 Apr 2003 19:41:48 -0300
Message-ID: <200304141941.49407.franco@akyasociados.com.ar> (raw)
In-Reply-To: <3e9b3112.22ad.16838@keko.com.ar>
References: <3e9b3112.22ad.16838@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'::TIMESTAMP AND
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'::TIMESTAMP AND
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
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: franco@akyasociados.com.ar, mdc@keko.com.ar
Subject: Re: help with query!!!
In-Reply-To: <200304141941.49407.franco@akyasociados.com.ar>
* 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