Buenas tardes. Disculpas por la demora en presentar los planes de ejecución, no disponía del servidor para hacer pruebas, dispongo de un par de días para hacer pruebas.
Estos son los planes de ejecución para Insert y Update sin triggers. INSERT INTO control_inventarios.bodegas ( item, bodega, pedidos_clientes, buffer, cuenta_inventarios, cuenta_compras, cuenta_ajuste , codigo_integracion, migracion, 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, ''), 'SI', COALESCE(t.com_pedido, 0) From data_igualar.item_bodega_pedidos_spp t Where NOT EXISTS( Select 1 From control_inventarios.bodegas b WHERE (b.bodega, b.item) = (t.loctid, t.item)); Insert on bodegas (cost=0.98..323092.49 rows=39623 width=829) (actual time=85155.859..85155.859 rows=0 loops=1) Buffers: shared hit=1353965 read=127633 -> Merge Anti Join (cost=0.98..323092.49 rows=39623 width=829) (actual time=85155.858..85155.858 rows=0 loops=1) Merge Cond: (((t.loctid)::text = (b.bodega)::text) AND ((t.item)::text = (b.item)::text)) Buffers: shared hit=1353965 read=127633 -> Index Scan using item_bodega_pedidos_spp_pkey on item_bodega_pedidos_spp t (cost=0.43..74343.72 rows=1391960 width=65) (actual time=0.011..22143.172 rows=1390858 loops=1) Buffers: shared hit=764717 read=26695 -> Index Only Scan using pk_bodegas on bodegas b (cost=0.55..224111.08 rows=1403235 width=17) (actual time=0.206..60780.134 rows=1390972 loops=1) Heap Fetches: 0 Buffers: shared hit=589248 read=100938 Planning time: 228.546 ms Execution time: 85156.048 ms UPDATE control_inventarios.bodegas b Set pedidos_clientes = COALESCE(t.lsoaloc, 0) , buffer = COALESCE(t.orderpt, 0) , comprometido_pedido = COALESCE(t.com_pedido, 0) FROM data_igualar.item_bodega_pedidos_spp t WHERE (b.bodega, b.item) = (t.loctid, t.item); Update on bodegas b (cost=668840.64..778246.71 rows=856806 width=263) (actual time=702073.405..702073.405 rows=0 loops=1) Buffers: shared hit=11571339 read=899822 dirtied=448622 written=252959, temp read=35885 written=35885 -> Merge Join (cost=668840.64..778246.71 rows=856806 width=263) (actual time=15323.956..30457.909 rows=1390858 loops=1) Merge Cond: (((t.loctid)::text = (b.bodega)::text) AND ((t.item)::text = (b.item)::text)) Buffers: shared hit=773358 read=447480 dirtied=1220 written=14640, temp read=35885 written=35885 -> Index Scan using item_bodega_pedidos_spp_pkey on item_bodega_pedidos_spp t (cost=0.43..74343.72 rows=1391960 width=32) (actual time=0.010..3917.604 rows=1390858 loops=1) Buffers: shared hit=767917 read=23495 written=14640 -> Materialize (cost=668840.21..675402.34 rows=1312426 width=201) (actual time=15323.922..20939.004 rows=1390972 loops=1) Buffers: shared hit=5441 read=423985 dirtied=1220, temp read=35885 written=35885 -> Sort (cost=668840.21..672121.27 rows=1312426 width=201) (actual time=15323.919..20563.204 rows=1390972 loops=1) Sort Key: b.bodega, b.item Sort Method: external merge Disk: 286984kB Buffers: shared hit=5441 read=423985 dirtied=1220, temp read=35885 written=35885 -> Seq Scan on bodegas b (cost=0.00..442550.26 rows=1312426 width=201) (actual time=0.012..2679.820 rows=1390972 loops=1) Buffers: shared hit=5441 read=423985 dirtied=1220 Planning time: 2.387 ms Execution time: 702124.969 ms Mauricio. Cuenca-Ecuador En viernes, 8 de septiembre de 2023, 01:14:04 GMT-5, Jaime Casanova <jcasa...@systemguards.com.ec> escribió: On Thu, Sep 7, 2023 at 3:42 PM mauricio pullabuestan <jmaurici...@yahoo.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
