Re: Optimizar Update

On Thu, Sep 7, 2023 at 3:42 PM mauricio pullabuestan
<jmauriciopb(at)yahoo(dot)es> wrote:
>
> Buenas tardes.
>
> Tengo una aplicación que corro en la madrugada, crea un archivo *.scv a partir de una tabla de Visual Foxpro (1400000 registros) un directorio en Ubuntu y luego ejecuta una función en postgres que copia los datos del archivo scv a una tabla UNLOGGED en Postgresql 9.6, para luego actualizar e insertar en una tabla de producción, el problema que tengo es que todo el proceso toma alrededor de 100 minutos, de los cuales el UPDATE toma más 99% del tiempo, las 2 tablas tienen como llave campos bodega, ítem con el mismo tipo y longitud
>
> Necesito optimizar este proceso
>
> Cuál es el riesgo de desactivar un trigger y luego de procesar volverlo a activar, sabiendo que no tiene incidencia dentro de este proceso y la concurrencia es mínima.
>

Si usas ALTER TABLE DISABLE TRIGGER USER, no hay riesgo... siempre que
sepas que el trigger no hace nada importante

>
> Trigger
>
> 1 trigger de auditoria para Insert, Edit, Delete, crea un registro en una tabla con datos de los cambios.
>
> 2 trigger por truncate
>
> 3 trigger por delete que verifica que no tenga existencia u ordenes
>
> 4 trigger before por Insert, llena algunos campos con un par de select a tablas muy pequeñas
>
> 5 trigger after por trigger que inserta en un registro en una tabla.
>

aunque aqui mencionas varios triggers, sobre que tabla están estos
triggers? el 5to es sobre UPDATE?

>
>
> CREATE UNLOGGED TABLE data_igualar.item_bodega_pedidos_spp
> (
> item character varying(15) NOT NULL,
> loctid character varying(3) NOT NULL,
> lsoaloc numeric(12,3),
> orderpt numeric(12,3),
> icacct character varying(24),
> rclacct character varying(24),
> iclacct character varying(24),
> gllink character varying(3),
> com_pedido numeric(10,3),
> CONSTRAINT item_bodega_pedidos_spp_pkey PRIMARY KEY (loctid, item)
> )
>

aqui yo crearía un índice sobre item para ayudar al DELETE que haces
justo después del COPY

>
> Función
>
> CREATE FUNCTION item_bodega_fvp_pg()
> RETURNS void AS
> $BODY$
>
> BEGIN
>
> SET work_mem = '500MB';
> TRUNCATE item_bodega_pedidos_spp;
> COPY item_bodega_pedidos_spp FROM '/home/pasa_vfp_pg/bodega_migra.csv' DELIMITER ',' CSV HEADER;
>
> DELETE FROM item_bodega_pedidos_spp t
>
> WHERE NOT EXISTS (SELECT 1 FROM items i Where i.item = t.item);
>
>
> -- ALTER TABLE bodegas DISABLE TRIGGER USER;
> UPDATE bodegas b
> Set pedidos_clientes = COALESCE(t.lsoaloc, 0)
> , buffer = COALESCE(t.orderpt, 0)
> , comprometido_pedido = COALESCE(t.com_pedido, 0)
> FROM ditem_bodega_pedidos_spp t
> WHERE (b.bodega, b.item) = (t.loctid, t.item);
>
> -- ALTER TABLE bodegas ENABLE TRIGGER USER;
>
>
> /* registros insertados son muy esporádicos */
>
> INSERT INTO bodegas
> ( item, bodega, pedidos_clientes, buffer, cuenta_inventarios, cuenta_compras, cuenta_ajuste
> , codigo_integracion, comprometido_pedido)
> Select
> t.item, t.loctid, COALESCE(t.lsoaloc, 0), COALESCE(t.orderpt, 0), COALESCE(t.icacct, ''), COALESCE(t.rclacct, ''), COALESCE(t.iclacct, '')
> , COALESCE(t.gllink, ''), COALESCE(t.com_pedido, 0)
> From item_bodega_pedidos_spp t
> Where NOT EXISTS( Select 1
> From bodegas b
> WHERE (b.bodega, b.item) = (t.loctid, t.item));
>
> TRUNCATE item_bodega_pedidos_spp;
> RESET work_mem;
>
> END;
>
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
>

un explain analyze de los UPDATE e INSERT dentro de la función sería
interesante. puedes obtenerlos usando auto_explain o puedes ponerlos
en una sentencia preparada y sacar el explain de la sentencia
preparada. algo así:

"""
prepare foo as
UPDATE bodegas b
Set pedidos_clientes = COALESCE(t.lsoaloc, 0)
, buffer = COALESCE(t.orderpt, 0)
, comprometido_pedido = COALESCE(t.com_pedido, 0)
FROM ditem_bodega_pedidos_spp t
WHERE (b.bodega, b.item) = (t.loctid, t.item);

explain analyze execute foo;
"""

--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL