From: | Alejandro Carrillo <fasterzip(at)yahoo(dot)es> |
---|---|
To: | Sergio Valdes Hurtado <svh(dot)pgsql(at)gmail(dot)com>, Lista PostgreSql <pgsql-es-ayuda(at)postgresql(dot)org> |
Subject: | Re: [pgsql-es-ayuda] Creación de Indices |
Date: | 2012-06-21 21:06:06 |
Message-ID: | 1340312766.86378.YahooMailNeo@web171005.mail.ukl.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
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 <svh(dot)pgsql(at)gmail(dot)com>
>Para: Lista PostgreSql <pgsql-es-ayuda(at)postgresql(dot)org>
>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.
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2012-06-21 21:19:46 | Re: Re: [pgsql-es-ayuda] Creación de Indices |
Previous Message | Alejandro Carrillo | 2012-06-21 20:57:17 | bytes en tabla |