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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
