Re: Ayuda con rendimiento..

Alvaro aqui está el EXPLAIN ANALYZE...

"Unique (cost=59712.53..59712.55 rows=1 width=43) (actual
time=49776.505..49781.015 rows=640 loops=1)"
" -> Sort (cost=59712.53..59712.54 rows=1 width=43) (actual
time=49776.496..49777.891 rows=640 loops=1)"
" Sort Key: cc.ubicacion, cc.acceso, cc.ext_acceso"
" -> Nested Loop (cost=16873.87..59712.52 rows=1 width=43) (actual
time=48030.771..49766.249 rows=640 loops=1)"
" -> Hash Join (cost=16873.87..59706.38 rows=1 width=47)
(actual time=47972.312..49571.621 rows=1308 loops=1)"
" Hash Cond: ((ctt.acceso)::text = (ct01.acceso)::text)"
" Join Filter: (ctt.ext_acceso = ct01.ext_acceso)"
" -> Hash Join (cost=10696.15..51572.10 rows=521747
width=25) (actual time=3265.770..13165.598 rows=523797 loops=1)"
" Hash Cond: (ctt.cod_titulo = tit.cod_titulo)"
" -> Seq Scan on codtit ctt (cost=0.00..22194.38
rows=521747 width=29) (actual time=13.744..6075.327 rows=523797 loops=1)"
" Filter: ((portada)::text = '1'::text)"
" -> Hash (cost=5723.51..5723.51 rows=286051
width=4) (actual time=3215.280..3215.280 rows=286061 loops=1)"
" -> Seq Scan on titulos
tit (cost=0.00..5723.51 rows=286051 width=4) (actual time=46.705..2322.251
rows=286061 loops=1)"
" -> Hash (cost=6177.71..6177.71 rows=1 width=147)
(actual time=35152.578..35152.578 rows=1308 loops=1)"
" -> Nested Loop (cost=47.33..6177.71 rows=1
width=147) (actual time=256.105..35141.762 rows=1308 loops=1)"
" -> Nested Loop (cost=47.33..6170.66
rows=1 width=104) (actual time=243.086..32712.669 rows=1308 loops=1)"
" -> Nested
Loop (cost=47.33..6163.52 rows=1 width=79) (actual time=240.636..32547.128
rows=2021 loops=1)"
" -> Nested
Loop (cost=47.33..6148.07 rows=1 width=50) (actual time=240.538..30925.652
rows=991 loops=1)"
" -> Nested
Loop (cost=47.33..6007.37 rows=22 width=25) (actual
time=154.375..12087.982 rows=75369 loops=1)"
" -> Index Scan
using tipodesc01 on descript de03 (cost=0.00..8.28 rows=1 width=4) (actual
time=56.178..74.196 rows=1 loops=1)"
" Index Cond:
(((tipo)::text = 'PAR'::text) AND ((tipo)::text = 'PAR'::text))"
" Filter:
((descriptor)::text = 'SANTA LUCIA'::text)"
" -> Bitmap Heap
Scan on coddesc ct03 (cost=47.33..5968.52 rows=2446 width=29) (actual
time=98.164..11580.321 rows=75369 loops=1)"
" Recheck
Cond: (ct03.cod_desc = de03.codesc)"
" -> Bitmap
Index Scan on cod_desc (cost=0.00..46.72 rows=2446 width=0) (actual
time=78.551..78.551 rows=75369 loops=1)"
" Index
Cond: (ct03.cod_desc = de03.codesc)"
" -> Index Scan using
document_pk on document ct02 (cost=0.00..6.38 rows=1 width=25) (actual
time=0.239..0.239 rows=0 loops=75369)"
" Index Cond:
(((ct03.acceso)::text = (ct02.acceso)::text) AND (ct03.ext_acceso =
ct02.ext_acceso))"
" Filter:
((fecha_iso)::text ~~ '2000%'::text)"
" -> Index Scan using
coddesc_pk on coddesc ct04 (cost=0.00..15.38 rows=5 width=29) (actual
time=1.303..1.612 rows=2 loops=991)"
" Index Cond:
(((ct04.acceso)::text = (ct03.acceso)::text) AND (ct04.ext_acceso =
ct03.ext_acceso))"
" -> Index Scan using document_pk on
document ct01 (cost=0.00..7.13 rows=1 width=25) (actual time=0.064..0.067
rows=1 loops=2021)"
" Index Cond:
(((ct04.acceso)::text = (ct01.acceso)::text) AND (ct04.ext_acceso =
ct01.ext_acceso))"
" Filter: ((numero)::text ~~
'%1%'::text)"
" -> Index Scan using central_pk on central
cc (cost=0.00..7.03 rows=1 width=43) (actual time=1.834..1.840 rows=1
loops=1308)"
" Index Cond: (((ct01.acceso)::text =
(cc.acceso)::text) AND (ct01.ext_acceso = cc.ext_acceso))"
" Filter: (((nivel_reg)::text <>
's'::text) AND ((tipo_liter)::text = 'U11'::text) AND ((codigo IS NULL) OR
((codigo)::text = ANY (('{"",U0240000000*}'::character varying[])::text[]))))"
" -> Index Scan using descriptor_pk on descript
de04 (cost=0.00..6.13 rows=1 width=4) (actual time=0.137..0.138 rows=0
loops=1308)"
" Index Cond: (ct04.cod_desc = de04.codesc)"
" Filter: (((tipo)::text = 'EOPN'::text) AND
(((descriptor)::text <> 'VALIDADO'::text) OR ((tipo)::text <> 'EOPN'::text)))"
"Total runtime: 49818.338 ms"

Voy a tomar en cuenta lo que me dices del work_men, voy a bajarlo. Con
respecto al vacuum tu me recomiendas que para unas se mantenga automático y
para otras no, dejame probar y te comento. Gracias.

Atte.
Leonardo Castillo
Hacer Sistemas C.A.

Oficina: +58(274)2527198, 2528808
Movil: +58(416)4718010