query optimization - Mailing list pgsql-general

hi guys

  i need advice for query optimization,take too long
time.
  any coments are welcomed :).
  best
   MDC


SELECT c.actuacion_car AS c_actuacion,
    c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
    FROM caratult AS c INNER JOIN extractt AS t1 ON
(c.id_extracto_car = t1.id_extracto)
INNER JOIN repartit AS r1 ON (c.id_reparticion_uc =
r1.id_reparticion)
INNER JOIN repartit AS r2 ON (t1.id_reparticion_ext =
r2.id_reparticion)
INNER JOIN repartit AS r3 ON (c.id_reparticion_car =
r3.id_reparticion)
LEFT JOIN carintet AS i ON (c.actuacion_car =
i.actuacion_int)
LEFT JOIN repartit AS r5 ON (i.id_reparticion_i =
r5.id_reparticion)
LEFT JOIN repartit AS r6 ON (i.id_reparticion_s =
r6.id_reparticion)
 LEFT JOIN carcallt AS l ON (c.actuacion_car =
l.actuacion_cal)
LEFT JOIN callest AS ll9 ON (l.id_calle_cal =
ll9.id_calle)
LEFT JOIN callest AS ll10 ON (l.id_calle1_cal =
ll10.id_calle)
 LEFT JOIN callest AS ll11 ON (l.id_calle2_cal =
ll11.id_calle)
LEFT JOIN callest AS ll12 ON (l.id_esquina_cal =
ll12.id_calle)
 LEFT JOIN pasest AS p ON (c.actuacion_car =
p.act_principal)
LEFT JOIN repartit AS r7 ON (p.id_repart_origen =
r7.id_reparticion)
 LEFT JOIN repartit AS r8 ON (p.id_repart_destino =
r8.id_reparticion)
LEFT JOIN repartit AS r9 ON (p.id_reparticion_u =
r9.id_reparticion)
WHERE letra(i.nota_iniciadora) = 'NO' AND
 anio(i.nota_iniciadora) = '2005' AND
 numero(i.nota_iniciadora) = '12' AND
repart(i.nota_iniciadora) = 'DGRH'
 LIMIT 101

TABLE DEFINITION
CREATE TABLE caratult
(
  actuacion_car char(24) NOT NULL,
  id_reparticion_uc int4 NOT NULL,
  fecha_inicio timestamp NOT NULL,
  tipo_actuacion char(1) NOT NULL,
  id_extracto_car int4,
  act_extramunicipal char(35),
  observaciones varchar(250),
  comentario1 varchar(250) NOT NULL,
  comentario2 varchar(250),
  comentario3 varchar(250),
  si_calle char(1) NOT NULL,
  verdadera char(1) NOT NULL,
  orden_pago char(10),
  fac_tipo char(2),
  fac_anio numeric(4),
  fac_nro numeric(8),
  fac_importe numeric(13),
  anexos varchar(50),
  recibo_suelto char(1) NOT NULL,
  id_actuacion_car int4 NOT NULL,
  id_reparticion_car int4 NOT NULL,
  id_secuencia_car int4 NOT NULL,
  fecha_inicio_real date NOT NULL,
  fts_comentario tsvector,
  fts_observaciones tsvector,
  CONSTRAINT pk_caratult PRIMARY KEY (actuacion_car),
  CONSTRAINT fx_actuacit FOREIGN KEY
(id_actuacion_car)
      REFERENCES actuacit (id_actuacion) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_extracto FOREIGN KEY (id_extracto_car)
      REFERENCES extractt (id_extracto) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_reparticion_car FOREIGN KEY
(id_reparticion_car)
      REFERENCES repartit (id_reparticion) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_reparticion_uc FOREIGN KEY
(id_reparticion_uc)
      REFERENCES repartit (id_reparticion) MATCH
SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT fx_secuencia1 FOREIGN KEY
(id_secuencia_car)
      REFERENCES secuenct (id_secuencia) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE caratult OWNER TO postgres;
GRANT ALL ON TABLE caratult TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE caratult TO GROUP devel;



QUERY PLAN RESULT

Limit  (cost=0.00..31798.82 rows=4 width=457)
  ->  Nested Loop Left Join  (cost=0.00..31798.82
rows=4 width=457)
        ->  Nested Loop Left Join
(cost=0.00..31774.69 rows=4 width=461)
              ->  Nested Loop Left Join
(cost=0.00..31750.61 rows=4 width=465)
                    ->  Nested Loop Left Join
(cost=0.00..31726.53 rows=4 width=469)
                          ->  Nested Loop Left Join
(cost=0.00..31558.52 rows=1 width=457)
                                ->  Nested Loop Left
Join  (cost=0.00..31552.53 rows=1 width=461)
                                      ->  Nested Loop
Left Join  (cost=0.00..31546.54 rows=1 width=465)
                                            ->  Nested
Loop Left Join  (cost=0.00..31540.55 rows=1 width=469)
                                                  ->
Nested Loop Left Join  (cost=0.00..31534.56 rows=1
width=473)

 ->  Nested Loop Left Join  (cost=0.00..31528.53
rows=1 width=457)

       ->  Nested Loop Left Join  (cost=0.00..31522.51
rows=1 width=461)

             ->  Nested Loop  (cost=0.00..31516.49
rows=1 width=465)

                   ->  Nested Loop
(cost=0.00..31510.47 rows=1 width=469)

                         ->  Nested Loop
(cost=0.00..31504.64 rows=1 width=469)

                               ->  Nested Loop
(cost=0.00..31498.62 rows=1 width=473)

                                     ->  Nested Loop
(cost=0.00..31492.60 rows=1 width=477)

                                           ->  Seq
Scan on carintet i  (cost=0.00..31486.57 rows=1
width=36)


Filter: ((substr((nota_iniciadora)::text, 1, 2) =
'NO'::text) AND (substr((nota_iniciadora)::text, 3, 4)
= '2005'::text) AND
("replace"(substr((nota_iniciadora)::text, 7, 6), '
'::text, ''::text) = '12'::text) AND
(substr((nota_iniciadora)::text, 16, 9) =
'DGRH'::text))

                                           ->  Index
Scan using ix1_caratult on caratult c
(cost=0.00..6.01 rows=1 width=469)

                                                 Index
Cond: (c.actuacion_car = "outer".actuacion_int)

                                     ->  Index Scan
using pk_repartit on repartit r1  (cost=0.00..6.01
rows=1 width=4)

                                           Index Cond:
("outer".id_reparticion_uc = r1.id_reparticion)

                               ->  Index Scan using
pk_repartit on repartit r3  (cost=0.00..6.01 rows=1
width=4)

                                     Index Cond:
("outer".id_reparticion_car = r3.id_reparticion)

                         ->  Index Scan using
pk_extractt on extractt t1  (cost=0.00..5.82 rows=1
width=8)

                               Index Cond:
("outer".id_extracto_car = t1.id_extracto)

                   ->  Index Scan using pk_repartit on
repartit r2  (cost=0.00..6.01 rows=1 width=4)

                         Index Cond:
("outer".id_reparticion_ext = r2.id_reparticion)

             ->  Index Scan using pk_repartit on
repartit r5  (cost=0.00..6.01 rows=1 width=4)

                   Index Cond:
("outer".id_reparticion_i = r5.id_reparticion)

       ->  Index Scan using pk_repartit on repartit r6
 (cost=0.00..6.01 rows=1 width=4)

             Index Cond: ("outer".id_reparticion_s =
r6.id_reparticion)

 ->  Index Scan using pk_carcallt on carcallt l
(cost=0.00..6.01 rows=1 width=44)

       Index Cond: ("outer".actuacion_car =
l.actuacion_cal)
                                                  ->
Index Scan using pk_callest on callest ll9
(cost=0.00..5.98 rows=1 width=4)

 Index Cond: ("outer".id_calle_cal = ll9.id_calle)
                                            ->  Index
Scan using pk_callest on callest ll10
(cost=0.00..5.98 rows=1 width=4)

Index Cond: ("outer".id_calle1_cal = ll10.id_calle)
                                      ->  Index Scan
using pk_callest on callest ll11  (cost=0.00..5.98
rows=1 width=4)
                                            Index
Cond: ("outer".id_calle2_cal = ll11.id_calle)
                                ->  Index Scan using
pk_callest on callest ll12  (cost=0.00..5.98 rows=1
width=4)
                                      Index Cond:
("outer".id_esquina_cal = ll12.id_calle)
                          ->  Index Scan using
pk_pasest on pasest p  (cost=0.00..167.43 rows=46
width=40)
                                Index Cond:
("outer".actuacion_car = p.act_principal)
                    ->  Index Scan using pk_repartit
on repartit r7  (cost=0.00..6.01 rows=1 width=4)
                          Index Cond:
("outer".id_repart_origen = r7.id_reparticion)
              ->  Index Scan using pk_repartit on
repartit r8  (cost=0.00..6.01 rows=1 width=4)
                    Index Cond:
("outer".id_repart_destino = r8.id_reparticion)
        ->  Index Scan using pk_repartit on repartit
r9  (cost=0.00..6.01 rows=1 width=4)
              Index Cond: ("outer".id_reparticion_u =
r9.id_reparticion)













___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar