Re: Esta es la estrucutra de las tablas.....

> 2008/1/28, Yasset Perez Riverol <yasset(dot)perez(at)biocomp(dot)cigb(dot)edu(dot)cu>:
> > Despues del VACUMM:
> >
> > EXPLAIN select tabla1.id, tabla2.id from tabla1 INNER JOIN tabla3 ON
> > (tabla3.id_tabla1 = tabla1.id) INNER JOIN tabla2 ON (tabla2.id =
> > tabla3.id_tabla2);
> > QUERY PLAN
> >
> > -------------------------------------------------------------------------
> >------- Hash Join (cost=313052.04..757887.84 rows=2134672 width=96)
> > Hash Cond: ((tabla3.id_tabla1)::text = (tabla1.id)::text)
> > -> Hash Join (cost=1.99..289972.69 rows=2134672 width=96)
> > Hash Cond: ((tabla3.id_tabla2)::text = (tabla2.id)::text)
> > -> Seq Scan on tabla3 (cost=0.00..232237.53 rows=9703053
> > width=96)
> > -> Hash (cost=1.44..1.44 rows=44 width=48)
> > -> Seq Scan on tabla2 (cost=0.00..1.44 rows=44 width=48)
> > -> Hash (cost=195175.69..195175.69 rows=5412669 width=48)
> > -> Seq Scan on tabla1 (cost=0.00..195175.69 rows=5412669
> > width=48)
> > (9 rows)
> >
> > La estructura de las tablas son las siguientes:
> >
> >
> > \d tabla3
> >
> > Table "public.tabla3"
> > Column | Type | Modifiers
> > --------------+------------------------+-----------
> > id_tabla2 | character varying(15) |
> > id_tabla1 | character varying(15) |
> > other_field | character varying(300) |
> > id_tabla4 | integer |
> >
> > Foreign-key constraints:
> > "tabla3_id_tabla1_fkey" FOREIGN KEY (id_tabla1) REFERENCES tabla1(id)
> > ON
> > UPDATE CASCADE ON DELETE CASCADE
> > "tabla3_id_tabla4_fkey" FOREIGN KEY (id_tabla4) REFERENCES
> > mol_tabla4(id)
> > ON UPDATE CASCADE ON DELETE CASCADE
> > "tabla3_id_tabla2_fkey" FOREIGN KEY (id_tabla2) REFERENCES tabla2(id)
> > ON
> > UPDATE CASCADE ON DELETE CASCADE
> >
> >
> >
> > \d tabla1
> > Table "public.tabla1"
> > Column | Type | Modifiers
> > -------------+------------------------+-----------
> > id | character varying(15) | not null
> > id_2 | character varying(12) |
> > format | character varying(300) |
> > porperty3 | character varying(100) |
> > Indexes:
> > "tabla1_pkey" PRIMARY KEY, btree (id)
> > "tabla1_unique" UNIQUE, btree (id_2, format)
> >
> > \d tabla2
> > Table "public.tabla2"
> > Column | Type | Modifiers
> > -------------+------------------------+-----------
> > id | character varying(15) | not null
> > name | character varying(100) |
> > description | text |
> > website | character varying(100) |
> > email | character varying(100) |
> > Indexes:
> > "tabla2_pkey" PRIMARY KEY, btree (id)
> >
> >
> > Alguna idea de como bajar este tiempo. (Maquina Dual AMD Athlon 2.4, 2 GB
> > de
> > RAM)
> >
> >
> > Sldos Yasset
>
> Agregale un índice en la tabla3 por el campo id_tabla1. Hacé Vacuum y
> mostrá el explain nuevamente.
> Silvio

Este es el nuevo explain despues de haber creado los indices y haber hecho
VACUUM:

EXPLAIN select tabla1.id, tabla2.id from tabla1 INNER JOIN tabla3 ON
(tabla3.id_tabla1 = tabla1.id) INNER JOIN tabla2 ON (tabla2.id =
tabla3.id_tabla2);
QUERY PLAN
--------------------------------------------------------------------------------
Hash Join (cost=313052.04..757887.84 rows=2134672 width=96)
Hash Cond: ((tabla3.id_tabla1)::text = (tabla1.id)::text)
-> Hash Join (cost=1.99..289972.69 rows=2134672 width=96)
Hash Cond: ((tabla3.id_tabla2)::text = (tabla2.id)::text)
-> Seq Scan on tabla3 (cost=0.00..232237.53 rows=9703053 width=96)
-> Hash (cost=1.44..1.44 rows=44 width=48)
-> Seq Scan on tabla2 (cost=0.00..1.44 rows=44 width=48)
-> Hash (cost=195175.69..195175.69 rows=5412669 width=48)
-> Seq Scan on tabla1 (cost=0.00..195175.69 rows=5412669 width=48)
(9 rows)