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

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

From: "Silvio Quadri" <silvioq(at)gmail(dot)com>
To: "Yasset Perez Riverol" <yasset(dot)perez(at)biocomp(dot)cigb(dot)edu(dot)cu>
Cc: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: Esta es la estrucutra de las tablas.....
Date: 2008-01-28 16:26:38
Message-ID: 61dc71dc0801280826x1bdfc83btae82783af7e84b0c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
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



-- 
Silvio Quadri

In response to

Responses

pgsql-es-ayuda by date

Next:From: Silvio QuadriDate: 2008-01-28 16:36:05
Subject: [OT] Sugerencia para los administradores de la lista
Previous:From: Daniel FerrerDate: 2008-01-28 16:23:47
Subject: RE: Problema de Performance

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