Re: Panic Index!!!!

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