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

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