1) Crea un indice por cada campo que vayas a filtrar con frecuencia: rbd,reg_cod,ano_pago, ind_reli,rut_sost ; es decir no crees indices compuestos ya que estos exigen que la consulta se haga por todos los campos. 2) Si la tabla tiene foráneas pertenecientes a otra tabla, estas deben tener índice 3) No hagas un IN por un sólo valor, es más rápido usar = que IN 4) Me parece que esa tabla debe estar en su propio tablespace o en un tablespace para tablas grandes.
>________________________________ > De: Sergio Valdes Hurtado <[email protected]> >Para: Lista PostgreSql <[email protected]> >Enviado: Jueves 21 de junio de 2012 15:13 >Asunto: [pgsql-es-ayuda] Creación de Indices > > >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. > > >
