Estimados,
tenemos una base de datos con tablas heredadas y a las cuales tenemos que
hacer una serie de consultas y lamentablemente estas tablas no tienen
ningún índice, no tienen llave primaria, no tienen foreing key. Esto hac
eque algunas consultas sean muy lentas y quisiera pedirles ayuda para crear
algunos índices que me ayuden a mejorar el rendimiento.
A continuación les muestro los detalles:
Sistema Operativo: Windows 7 Professional 64 bits
Equipo: PC HP con Intel i5 con 4 GB RAM, dedicado a la base de datos
solamente
Postgresql: PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit
detalles de tabla temp_orden_pago:
CREATE TABLE public.temp_orden_pago (
rbd int4 NOT NULL,
depend int4 NOT NULL,
agr_subv int4 NULL,
asi_mes1 numeric(12,4) NULL,
asi_mes2 numeric(12,4) NULL,
asi_mes3 numeric(12,4) NULL,
asi_prom numeric(12,4) NULL,
matri int4 NULL,
sum_agr int4 NULL,
mes_pago int4 NOT NULL,
ano_pago int4 NOT NULL,
ind_reli char(1) NOT NULL,
com_cod int4 NULL,
pro_cod int4 NULL,
reg_cod int4 NULL,
com_nom char(45) NULL,
pro_nom char(45) NULL,
reg_nom char(45) NULL,
dp_cod int4 NULL,
nom_esta char(45) NULL,
nom_sost char(45) NULL,
rut_sost int4 NULL,
cod_banc char(3) NULL,
cod_plaz char(4) NULL,
cod_sucu char(3) NULL,
dir_banc char(45) NULL,
num_cuen char(11) NULL,
ser_cheq int4 NULL,
sub_esco int4 NULL,
des_fico int4 NULL,
sist_beca int4 NULL,
asi_inte numeric(14,4) NULL,
fac_inte numeric(7,4) NULL,
mto_inte int4 NULL,
por_zona int4 NULL,
mto_zona int4 NULL,
asi_rura_k_4 numeric(14,4) NULL,
asi_rura_5_4 numeric(14,4) NULL,
fac_rura_k_4 numeric(7,4) NULL,
fac_rura_5_4 numeric(7,4) NULL,
mto_rura_k_4 int4 NULL,
mto_rura_5_4 int4 NULL,
piso_rura int4 NULL,
zona_piso int4 NULL,
pag_pend int4 NULL,
mto_disc int4 NULL,
mto_esco int4 NULL,
des_esco int4 NULL,
mto_rein int4 NULL,
mto_rete int4 NULL,
mto_mult int4 NULL,
otr_mtos int4 NULL,
sub_liqu int4 NULL,
aju_reli int4 NULL,
liq_apag int4 NULL,
des_difi int4 NULL,
adi_espe int4 NULL,
no_doce int4 NULL,
val_use numeric(14,4) NULL,
ind_jecd char(1) NULL,
cant_use_aisl int4 NULL,
let_esta char(1) NULL,
num_esta int4 NULL,
dv_rbd char(1) NULL,
emi_cheque int4 NULL,
mto_19598 int4 NULL,
mto_subv1 int4 NULL,
mto_subv2 int4 NULL,
prof_enca int4 NULL,
apo_sost int4 NULL,
gls_factor char(60) NULL,
gls_nive char(60) NULL,
fac_use numeric(7,5) NULL,
cod_ense int4 NULL,
subv_mant int4 NULL,
rut_part int4 NULL,
mto_tot_aju int4 NULL,
gls_aju char(45) NULL,
tip_aju int4 NULL,
asi_rura_b_a numeric(14,4) NULL,
asi_rura_m_a numeric(14,4) NULL,
fac_rura_b_a numeric(7,4) NULL,
fac_rura_m_a numeric(7,4) NULL,
mto_rura_b_a int4 NULL,
mto_rura_m_a int4 NULL,
mto_subv3 int4 NULL,
mto_subv4 int4 NULL
)
WITHOUT OIDS
TABLESPACE pg_default;
CREATE INDEX ind_rbd_ano_mes
ON public.temp_orden_pago USING btree (rbd int4_ops, ano_pago int4_ops,
mes_pago int4_ops);
*Nota: el índice no fue creado explicitamente*
Cantidad de Registros: 18.000.000 aprox. actualmete, pero todos los meses
se hace una carga de actualización que implica borrar los datos del año y
cargarlos nuevamente. Los registros del año actualmente son 500.000 aprox
(100.00 por mes), pero cuando finalize junio, serán 200.000 por mes, por lo
tanto pasaran a ser casi 1.200.000 los del año 2012 y asi creceran
mensualmente hasta fin de año.
Luego de cada proceso de carga (que se efectúa con Pentahoo Data
Integration) se realiza Vacuum, luego Vacuum Analyze y por último Vacuum
Reindex, esto se realiza con la herramienta de mantención del pgAdmin III.
Consultas más comunes a la tabla:
A continuación les detallo las consultas más comunes a la tabla y los
resultados que me dan los explain analyze de cada una de ellas
explain analyze
SELECT reg_cod, pro_cod, com_cod, rbd, depend, mes_pago, ano_pago,
ind_reli,
sum(asi_prom) asi_prom, sum(matri) matri, max(sub_esco) sub_esco,
max(des_fico) des_fico, max(sist_beca) sist_beca, max(mto_inte) mto_inte,
max(mto_zona) mto_zona, max(mto_rura_k_4) mto_rura_k_4,
max(mto_rura_5_4) mto_rura_5_4, max(piso_rura) piso_rura,
max(liq_apag) liq_apag, max(des_difi) des_difi, max(adi_espe) adi_espe,
max(no_doce) no_doce,
max(prof_enca) prof_enca,
max(mto_rura_b_a) mto_rura_b_a, max(mto_rura_m_a) mto_rura_m_a
FROM public.temp_orden_pago
where rbd in(26343) and ano_pago in(2008, 2009, 2010, 2011, 2012) and
ind_reli in ('N','S')
group by reg_cod, pro_cod, com_cod, rbd, depend, mes_pago, ano_pago,
ind_reli;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=3240.55..3240.96 rows=41 width=100) (actual
time=1335.871..1336.048 rows=86 loops=1)
-> Bitmap Heap Scan on temp_orden_pago (cost=32.18..3215.48 rows=401
width=100) (actual time=63.203..1334.162 rows=128 loops=1)
Recheck Cond: ((rbd = 26343) AND (ano_pago = ANY
('{2008,2009,2010,2011,2012}'::integer[])))
Filter: (ind_reli = ANY
('{N,S}'::bpchar[]))
-> Bitmap Index Scan on ind_rbd_ano_mes (cost=0.00..32.08
rows=805 width=0) (actual time=43.269..43.269 rows=248 loops=1)
Index Cond: ((rbd = 26343) AND (ano_pago = ANY
('{2008,2009,2010,2011,2012}'::integer[])))
Total runtime: 1336.525
ms
Publicado en http://explain.depesz.com/s/K27
explain analyze
SELECT reg_cod, pro_cod, com_cod, rbd, depend, mes_pago, ano_pago,
ind_reli,
sum(asi_prom) asi_prom, sum(matri) matri, max(sub_esco) sub_esco,
max(des_fico) des_fico, max(sist_beca) sist_beca, max(mto_inte) mto_inte,
max(mto_zona) mto_zona, max(mto_rura_k_4) mto_rura_k_4,
max(mto_rura_5_4) mto_rura_5_4, max(piso_rura) piso_rura,
max(liq_apag) liq_apag, max(des_difi) des_difi, max(adi_espe) adi_espe,
max(no_doce) no_doce,
max(prof_enca) prof_enca,
max(mto_rura_b_a) mto_rura_b_a, max(mto_rura_m_a) mto_rura_m_a
FROM public.temp_orden_pago
where reg_cod = 8 and ano_pago in(2009, 2010, 2011) and ind_reli in
('N','S')
group by reg_cod, pro_cod, com_cod, rbd, depend, mes_pago, ano_pago,
ind_reli;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=2070005.22..2105610.25 rows=53948 width=100) (actual
time=218902.502..220502.597 rows=107672 loops=1)
-> Sort (cost=2070005.22..2071353.90 rows=539470 width=100) (actual
time=218902.469..219675.109 rows=647573 loops=1)
Sort Key: reg_cod, pro_cod, com_cod, rbd, depend, mes_pago,
ano_pago, ind_reli
Sort Method: external merge Disk:
62896kB
-> Seq Scan on temp_orden_pago (cost=0.00..1959634.49
rows=539470 width=100) (actual time=143.948..208686.562 rows=647573
loops=1)
Filter: ((ind_reli = ANY ('{N,S}'::bpchar[])) AND (reg_cod =
8) AND (ano_pago = ANY ('{2009,2010,2011}'::integer[])))
Total runtime: 220545.706 ms
Publicado en http://explain.depesz.com/s/UXlB
explain analyze
SELECT reg_cod, pro_cod, com_cod, rbd, depend, mes_pago, ano_pago,
ind_reli,
sum(asi_prom) asi_prom, sum(matri) matri, max(sub_esco) sub_esco,
max(des_fico) des_fico, max(sist_beca) sist_beca, max(mto_inte) mto_inte,
max(mto_zona) mto_zona, max(mto_rura_k_4) mto_rura_k_4,
max(mto_rura_5_4) mto_rura_5_4, max(piso_rura) piso_rura,
max(liq_apag) liq_apag, max(des_difi) des_difi, max(adi_espe) adi_espe,
max(no_doce) no_doce,
max(prof_enca) prof_enca,
max(mto_rura_b_a) mto_rura_b_a, max(mto_rura_m_a) mto_rura_m_a
FROM public.temp_orden_pago
where rut_sost = 69020100 and ano_pago in(2008, 2009, 2010, 2011) and
ind_reli in ('N','S')
group by reg_cod, pro_cod, com_cod, rbd, depend, mes_pago, ano_pago,
ind_reli;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1982169.96..1982170.53 rows=57 width=100) (actual
time=244620.682..244622.391 rows=880 loops=1)
-> Seq Scan on temp_orden_pago (cost=0.00..1982134.52 rows=567
width=100) (actual time=760.008..244589.980 rows=4886 loops=1)
Filter: ((ind_reli = ANY ('{N,S}'::bpchar[])) AND (rut_sost =
69020100) AND (ano_pago = ANY ('{2008,2009,2010,2011}'::integer[])))
Total runtime: 244622.957 ms
Publicado en http://explain.depesz.com/s/Cf5
Alguien me puede orientar para ver como puedo mejorar estos resultados (si
ello es posible)
Gracias de antemano.
--
Sergio Valdés H.
