Thread: Panic Index!!!!
Hi this is my code of sql select
SELECT
a.f_codigo_cliente,
a.f_fecha_inicio_vigencia,
a.f_fecha_fin_vigencia,
b.f_nombre,
b.f_apellido,
e.f_chassis,
e.f_placa,
e.f_registro,
e.f_color,
e.f_year,
g.f_descripcion_marca,
f.f_descripcion_modelo
FROM
t_poliza_vehiculos a,
t_clientes b,
t_vehiculos_asegurados d,
t_vehiculos e,
t_agentes c,
t_marcas_vehiculos g,
t_modelos f
WHERE
a.f_codigo_cliente = b.f_codigo_cliente AND
a.f_agente = c.f_codigo_agente AND
a.f_wholenum = d.f_wholenum AND
d.f_idvehiculo = e.f_keyvehiculo AND
e.f_modelo = f.f_idmodelo AND
f.f_idmarca = g.f_codigo_marca AND
a.f_wholenum = 'POL000001';
This select get 20 seconds to doing. My 2 first table has 500000 reords each
one
Explain ////////////////
Merge Join (cost=79.44..7127.72 rows=226 width=347)
Merge Cond: ("outer".f_wholenum = "inner".f_wholenum)
-> Nested Loop (cost=0.00..7025.77 rows=1807 width=116)
-> Nested Loop (cost=0.00..17.13 rows=1 width=44)
Join Filter: ("outer".f_agente = "inner".f_codigo_agente)
-> Index Scan using t_poliza_vehiculos_f_wholenum_idx on
t_poliza_vehiculos a (cost=0.00..17.07 rows=5 width=40)
Index Cond: (f_wholenum = 'POL000001'::bpchar)
-> Seq Scan on t_agentes c (cost=0.00..0.00 rows=1 width=4)
-> Index Scan using f_id on t_clientes b (cost=0.00..6986.05
rows=1807 width=72)
Index Cond: ("outer".f_codigo_cliente = b.f_codigo_cliente)
-> Sort (cost=79.44..79.76 rows=125 width=231)
Sort Key: d.f_wholenum
-> Hash Join (cost=43.53..75.09 rows=125 width=231)
Hash Cond: ("outer".f_codigo_marca = "inner".f_idmarca)
-> Seq Scan on t_marcas_vehiculos g (cost=0.00..20.00
rows=1000 width=43)
-> Hash (cost=43.47..43.47 rows=25 width=188)
-> Hash Join (cost=18.15..43.47 rows=25 width=188)
Hash Cond: ("outer".f_idvehiculo =
"inner".f_keyvehiculo)
-> Seq Scan on t_vehiculos_asegurados d
(cost=0.00..20.00 rows=1000 width=28)
-> Hash (cost=18.14..18.14 rows=5 width=160)
-> Nested Loop (cost=0.00..18.14 rows=5
width=160)
-> Seq Scan on t_vehiculos e
(cost=0.00..1.01 rows=1 width=113)
-> Index Scan using
t_modelos_f_idmodelo_idx on t_modelos f (cost=0.00..17.07 rows=5 width=47)
Index Cond: ("outer".f_modelo =
f.f_idmodelo)
/////////////////////////
*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo
comun"
*-------------------------------------------------------*
_________________________________________________________________
Las mejores tiendas, los precios mas bajos, entregas en todo el mundo,
YupiMSN Compras: www.yupimsn.com/compras
On Friday 05 September 2003 18:07, Edwin Quijada wrote:
> Hi this is my code of sql select
>
[snip]
> This select get 20 seconds to doing. My 2 first table has 500000 reords
> each one
>
> Explain ////////////////
> Merge Join (cost=79.44..7127.72 rows=226 width=347)
> Merge Cond: ("outer".f_wholenum = "inner".f_wholenum)
> -> Nested Loop (cost=0.00..7025.77 rows=1807 width=116)
> -> Nested Loop (cost=0.00..17.13 rows=1 width=44)
> Join Filter: ("outer".f_agente = "inner".f_codigo_agente)
> -> Index Scan using t_poliza_vehiculos_f_wholenum_idx on
> t_poliza_vehiculos a (cost=0.00..17.07 rows=5 width=40)
> Index Cond: (f_wholenum = 'POL000001'::bpchar)
Index scan here - so that's good.
> -> Seq Scan on t_agentes c (cost=0.00..0.00 rows=1 width=4)
> -> Index Scan using f_id on t_clientes b (cost=0.00..6986.05
> rows=1807 width=72)
Index scan here too.
> Index Cond: ("outer".f_codigo_cliente = b.f_codigo_cliente)
> -> Sort (cost=79.44..79.76 rows=125 width=231)
> Sort Key: d.f_wholenum
> -> Hash Join (cost=43.53..75.09 rows=125 width=231)
> Hash Cond: ("outer".f_codigo_marca = "inner".f_idmarca)
> -> Seq Scan on t_marcas_vehiculos g (cost=0.00..20.00
> rows=1000 width=43)
Is 1000 rows a reasonable estimate here?
> -> Hash (cost=43.47..43.47 rows=25 width=188)
> -> Hash Join (cost=18.15..43.47 rows=25 width=188)
> Hash Cond: ("outer".f_idvehiculo =
> "inner".f_keyvehiculo)
> -> Seq Scan on t_vehiculos_asegurados d
> (cost=0.00..20.00 rows=1000 width=28)
And 1000 here too.
Couple of things to try: run "analyse" to redo the statistics and see if that
helps.
If not, have you changed the configuration settings - the default ones are
very low. You can find more at
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
--
Richard Huxton
Archonet Ltd
"Edwin Quijada" <listas_quijada@hotmail.com> writes:
> This select get 20 seconds to doing. My 2 first table has 500000 reords each
> one
The row counts mentioned in your explain output seem suspiciously small.
Have you ever ANALYZEd or VACUUMed these tables?
regards, tom lane
> >Couple of things to try: run "analyse" to redo the statistics and see if that >helps. > >If not, have you changed the configuration settings - the default ones are >very low. You can find more at >http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php > >-- > Richard Huxton > Archonet Ltd Also try running an 'explain analyze' instead of just an explain it will give us more information. --- Adam Kavan --- akavan@cox.net
On Fri, Sep 05, 2003 at 01:38:23PM -0400, Tom Lane wrote: > "Edwin Quijada" <listas_quijada@hotmail.com> writes: > > This select get 20 seconds to doing. My 2 first table has 500000 reords each > > one > > The row counts mentioned in your explain output seem suspiciously small. > Have you ever ANALYZEd or VACUUMed these tables? We got him up and running on the spanish list. Appropiate clue-installing tool will be applied as necessary. (Maybe people should not be so fast to post on more than one list at the same time...) -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "The eagle never lost so much time as when he submitted to learn from the crow." (William Blake, citado por Nobody)