Buenos días. Acudo a esta lista con el fin de buscar orientación sobre
optimización de rendimiento sobre una consulta que se hace sobre una
aplicación que construimos y que no está dando los resultados esperados en
cuanto a consulta.
La SQL dinámica que se genera es esta:
¨select distinct
CC.acceso,CC.ext_acceso,MAX(CTT.FECHA_ISO||COALESCE(CTT.HORA_ISO_INICIO,''))
from (( (( (( ((DOCUMENT CT01
INNER JOIN CENTRAL CC ON
(CT01.ACCESO = CC.ACCESO AND CT01.EXT_ACCESO = CC.EXT_ACCESO) and
( CT01.JERARQUIA = '1' ) ))
INNER JOIN (( Ocurrencias CT02 inner join palabras p0 on
(CT02.cod_palabra = p0.cod_palabra
and p0.palabra like 'LEY%' AND p0.campo = 'DESCRIPTOR'))
INNER join ( Ocurrencias o1 inner join palabras p1 on
(o1.cod_palabra = p1.cod_palabra
and p1.palabra like 'VIVIENDAS%' AND p1.campo = 'DESCRIPTOR'))
on (CT02.acceso = o1.acceso and CT02.ext_acceso = o1.ext_acceso)
)
on (CT01.acceso=o1.acceso and CT01.ext_acceso = o1.ext_acceso)))
INNER JOIN ( Ocurrencias o2 inner join palabras p2 on
(o2.cod_palabra = p2.cod_palabra
and p2.palabra like 'MINISTERIO%' AND p2.campo = 'AUTOR'))
on (CT01.acceso=o2.acceso and CT01.ext_acceso = o2.ext_acceso)))
INNER JOIN (CODDESC CT04 INNER JOIN DESCRIPT DE04 ON
(CT04.COD_DESC = DE04.CODESC) AND DE04.TIPO = 'CN' and (
DE04.DESCRIPTOR = 'VIGENTE' AND DE04.TIPO = 'CN') ) on
(CC.acceso = CT04.acceso and CC.ext_acceso = CT04.ext_acceso ))
)
, document ctt
where (CC.nivel_reg <> 's' and CC.nivel_reg <> 'mv')
AND (CC.TIPO_LITER = 'S')
AND (CC.CODIGO IS NULL OR CC.CODIGO IN ('','U0207000000*'))
and CC.acceso = ctt.acceso
and CC.ext_acceso = ctt.ext_acceso
and CC.ext_acceso > 0
AND CC.ACCESO IN
(select distinct CC.acceso
from (( ((DOCUMENT CT01
INNER JOIN CENTRAL CC ON
(CT01.ACCESO = CC.ACCESO AND CT01.EXT_ACCESO = CC.EXT_ACCESO) and
( CT01.FECHA_ISO >= '20120101' ) ))
INNER JOIN DOCUMENT CT02 on
(CC.acceso = CT02.acceso and CC.ext_acceso = CT02.ext_acceso and (
CT02.FECHA_ISO <= '20130531' ) ))
)
, document ctt
where (CC.nivel_reg <> 's' and CC.nivel_reg <> 'mv')
AND (CC.TIPO_LITER = 'S')
AND (CC.CODIGO IS NULL OR CC.CODIGO IN ('','U0207000000*'))
and CC.acceso = ctt.acceso
and CC.ext_acceso = ctt.ext_acceso
and CC.ext_acceso = 0)
GROUP BY CC.ACCESO,CC.EXT_ACCESO
ORDER BY 3 DESC,1 DESC,2 DESC
;
Las tabla OCURRENCIAS posee más de 6 millones de registros, CENTRAL y
DOCUMENT alrededor de 500 mil.
El explain de la consulta es este:
¨"Unique (cost=11088.35..11088.36 rows=1 width=44)"
" -> Sort (cost=11088.35..11088.35 rows=1 width=44)"
" Sort Key: (max(((ctt.fecha_iso)::text ||
(COALESCE(ctt.hora_iso_inicio, ''::character varying))::text))), cc.acceso,
cc.ext_acceso"
" -> HashAggregate (cost=11088.32..11088.34 rows=1 width=44)"
" -> Nested Loop (cost=7241.03..11088.31 rows=1 width=44)"
" -> Nested Loop (cost=7241.03..11079.96 rows=1
width=137)"
" Join Filter: (((ct04.acceso)::text =
(cc.acceso)::text) AND (ct04.ext_acceso = cc.ext_acceso))"
" -> Nested Loop (cost=41.17..3478.30 rows=1
width=100)"
" -> Nested Loop (cost=41.17..3475.86
rows=8 width=108)"
" -> Hash Join (cost=41.17..3471.62
rows=1 width=80)"
" Hash Cond: (o1.cod_palabra =
p1.cod_palabra)"
" -> Nested Loop
(cost=32.84..3462.15 rows=300 width=88)"
" -> Nested Loop
(cost=32.84..3362.10 rows=23 width=60)"
" -> Nested Loop
(cost=32.84..3350.80 rows=31 width=40)"
" -> Nested
Loop (cost=32.84..3330.60 rows=54 width=20)"
" ->
Index Scan using palabra on palabras p2 (cost=0.00..8.29 rows=1 width=8)"
"
Index Cond: (((palabra)::text >= 'MINISTERIO'::text) AND ((palabra)::text <
'MINISTERIP'::text) AND ((campo)::text = 'AUTOR'::text))"
"
Filter: ((palabra)::text ~~ 'MINISTERIO%'::text)"
" ->
Bitmap Heap Scan on ocurrencias o2 (cost=32.84..3311.14 rows=893 width=28)"
"
Recheck Cond: (o2.cod_palabra = p2.cod_palabra)"
"
-> Bitmap Index Scan on pk_ocurrencias (cost=0.00..32.62 rows=893
width=0)"
"
Index Cond: (o2.cod_palabra = p2.cod_palabra)"
" -> Index
Scan using pk_central on central cc (cost=0.00..0.36 rows=1 width=20)"
" Index
Cond: (((cc.acceso)::text = (o2.acceso)::text) AND (cc.ext_acceso >
0::numeric) AND (cc.ext_acceso = o2.ext_acceso))"
"
Filter: (((cc.nivel_reg)::text <> 's'::text) AND ((cc.nivel_reg)::text <>
'mv'::text) AND ((cc.codigo IS NULL) OR ((cc.codigo)::text = ANY
('{"",U0207000000*}'::text[]))) AND ((cc.tipo_liter)::text = 'S'::text))"
" -> Index Scan
using pk_document on document ct01 (cost=0.00..0.35 rows=1 width=20)"
" Index Cond:
(((ct01.acceso)::text = (cc.acceso)::text) AND (ct01.ext_acceso =
cc.ext_acceso))"
" Filter:
(ct01.jerarquia = 1::numeric)"
" -> Index Scan using
idx_oc_accesoextacceso on ocurrencias o1 (cost=0.00..4.28 rows=5 width=28)"
" Index Cond:
((o1.ext_acceso = cc.ext_acceso) AND ((o1.acceso)::text =
(cc.acceso)::text))"
" -> Hash (cost=8.29..8.29
rows=3 width=8)"
" -> Index Scan using
palabra on palabras p1 (cost=0.00..8.29 rows=3 width=8)"
" Index Cond:
(((palabra)::text >= 'VIVIENDAS'::text) AND ((palabra)::text <
'VIVIENDAT'::text) AND ((campo)::text = 'DESCRIPTOR'::text))"
" Filter:
((palabra)::text ~~ 'VIVIENDAS%'::text)"
" -> Index Scan using
idx_oc_accesoextacceso on ocurrencias ct02 (cost=0.00..4.17 rows=5
width=28)"
" Index Cond: ((ct02.ext_acceso
= cc.ext_acceso) AND ((ct02.acceso)::text = (cc.acceso)::text))"
" -> Index Scan using pk_palabras on
palabras p0 (cost=0.00..0.29 rows=1 width=8)"
" Index Cond: (p0.cod_palabra =
ct02.cod_palabra)"
" Filter: (((p0.palabra)::text ~~
'LEY%'::text) AND ((p0.campo)::text = 'DESCRIPTOR'::text))"
" -> Hash Join (cost=7199.86..7601.56 rows=6
width=37)"
" Hash Cond: ((ct04.acceso)::text =
("ANY_subquery".acceso)::text)"
" -> Nested Loop (cost=5.19..406.83
rows=13 width=20)"
" -> Index Scan using descriptor on
descript de04 (cost=0.00..8.28 rows=1 width=8)"
" Index Cond:
((descriptor)::text = 'VIGENTE'::text)"
" Filter: ((tipo)::text =
'CN'::text)"
" -> Bitmap Heap Scan on coddesc
ct04 (cost=5.19..397.20 rows=108 width=27)"
" Recheck Cond: (ct04.cod_desc =
de04.codesc)"
" -> Bitmap Index Scan on
cod_desc (cost=0.00..5.16 rows=108 width=0)"
" Index Cond:
(ct04.cod_desc = de04.codesc)"
" -> Hash (cost=7192.18..7192.18 rows=200
width=17)"
" -> HashAggregate
(cost=7190.18..7192.18 rows=200 width=17)"
" -> Subquery Scan
"ANY_subquery" (cost=7179.20..7188.96 rows=488 width=17)"
" -> HashAggregate
(cost=7179.20..7184.08 rows=488 width=14)"
" -> Nested Loop
(cost=0.00..7177.98 rows=488 width=14)"
" -> Nested
Loop (cost=0.00..4715.96 rows=251 width=48)"
" ->
Nested Loop (cost=0.00..3238.96 rows=148 width=40)"
"
-> Index Scan using fecha_iso01 on document ct01 (cost=0.00..2183.44
rows=104 width=20)"
"
Index Cond: ((fecha_iso)::text >= '20120101'::text)"
"
Filter: (ext_acceso = 0::numeric)"
"
-> Index Scan using pk_document on document ct02 (cost=0.00..10.14 rows=1
width=20)"
"
Index Cond: (((ct02.acceso)::text = (ct01.acceso)::text) AND
(ct02.ext_acceso = 0::numeric))"
"
Filter: ((ct02.fecha_iso)::text <= '20130531'::text)"
" ->
Index Scan using pk_central on central cc (cost=0.00..9.95 rows=2
width=20)"
"
Index Cond: (((cc.acceso)::text = (ct01.acceso)::text) AND (cc.ext_acceso =
0::numeric))"
"
Filter: (((cc.nivel_reg)::text <> 's'::text) AND ((cc.nivel_reg)::text <>
'mv'::text) AND ((cc.codigo IS NULL) OR ((cc.codigo)::text = ANY
('{"",U0207000000*}'::text[]))) AND ((cc.tipo_liter)::text = 'S'::text))"
" -> Index
Scan using pk_document on document ctt (cost=0.00..9.78 rows=2 width=20)"
" Index
Cond: (((ctt.acceso)::text = (cc.acceso)::text) AND (ctt.ext_acceso =
0::numeric))"
" -> Index Scan using pk_document on document ctt
(cost=0.00..8.34 rows=1 width=44)"
" Index Cond: (((ctt.acceso)::text =
(cc.acceso)::text) AND (ctt.ext_acceso = cc.ext_acceso))"
No se ven sequence scan y se nota que el gasto fuerte en join de ocurrencia
y en la busqueda por fecha. La cfg del servidor es la de defecto menos los
siguientes valores:
shared_buffers = 1GB # min 128kB
effective_cache_size = 2048MB
Es postgres 8.4. Cualquier ayuda es bienvenida de antemano.
Atte.
Leonardo Castillo L.
