Re: [pgsql-es-ayuda] Creación de Indices

Bueno, supongo que no querrás que lea todo eso...

Ah, sí querías?

primer pauta: si vas a preguntar por algo, que ese algo tenga índice, o
sea, si tu where es:
where rut_sost = 69020100 and ano_pago in(2008, 2009, 2010, 2011) and
ind_reli in ('N','S')

creale índice a rut_sost, ano_pago y a ind_reli

Cuando termines de hacer eso por cada where, fijate cuánto tardan los
querys, y la seguimos.

El 21 de junio de 2012 17:13, Sergio Valdes Hurtado
<svh(dot)pgsql(at)gmail(dot)com>escribió:

> 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.
>

--
Manuel