Skip site navigation (1) Skip section navigation (2)

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

From: marcelo Cortez <jmdc_marcelo(at)yahoo(dot)com(dot)ar>
To: Yasset Perez Riverol <yasset(dot)perez(at)biocomp(dot)cigb(dot)edu(dot)cu>, Silvio Quadri <silvioq(at)gmail(dot)com>
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: Esta es la estrucutra de las tablas.....
Date: 2008-01-28 17:52:56
Message-ID: 616667.26429.qm@web32115.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
Yasset 


 SOlo para hacer pruebas pone el seq_scan en off en el

postgresql.conf y fijate si hay diferencias.
salu2
mdc

--- Yasset Perez Riverol
<yasset(dot)perez(at)biocomp(dot)cigb(dot)edu(dot)cu> escribió:

> > 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)
> 
> --
> TIP 10: no uses HTML en tu pregunta, seguro que
> quien responda no podrá leerlo
> 



      Tarjeta de crédito Yahoo! de Banco Supervielle.
Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar 

In response to

pgsql-es-ayuda by date

Next:From: Rodriguez FernandoDate: 2008-01-28 18:09:40
Subject: Re: Problema de Performance
Previous:From: Francisco TrinidadDate: 2008-01-28 17:45:03
Subject: Re: campo money no presenta valores >1000

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group