Re: Optimizar Update

Hola Horacio.

> Hola,

>> On 7/09/2023, at 2:41 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

>Saca el explain plan, algo me dice que tu problema esta relacionado a un indice de un trigger que esta generando un full scan. ( me paso en el pasado ).

En estos momentos no es posible un explain, es muy pesados y el sistema se vuelve muy lento para los usuarios, el fin de semana voy a sacar explain del UPDATE e INSERT.

>Favor de tirar las definiciones de las tablas relacionadas y los FK a los que apuntan las otras tablas.

>\d tabla1
>\d tabla2

>El final me dice información sobre los FK si es que existen.

Estas son las tablas que son FK de la tabla bodega a la que estoy actualizando

CREATE TABLE items
(
  item character varying(15) NOT NULL,
  descripcion character varying(100),
  clase_producto character varying(6),
  alterno1 character varying(15),
  unidad_medida character varying(6),
  costo_promedio numeric(11,5) NOT NULL DEFAULT 0,
  familia character varying(25),
  es_fabricado BOOLEAN, 
  es_comprado BOOLEAN,
    es_fantasma BOOLEAN, 
  CONSTRAINT pk_items PRIMARY KEY (item),
  CONSTRAINT items_indicadores_compatibles_chk CHECK (NOT (es_fabricado AND es_comprado) AND NOT (es_comprado AND es_fantasma))
)

CREATE TABLE id_bodegas
(
  bodega character varying(3) NOT NULL,
  descripcion character varying(60),
  ciudad character varying(20),
  lugar character varying(10),
  tipo_bodega character varying(10),
  ubicacion_default character varying(4),
  CONSTRAINT pk_id_bodegas PRIMARY KEY (bodega)
)

>Lo otro, si hay posibilidad de hacer todo como SQL mucho mejor.

>Si generas el CVS desde la misma base, tendría mas sentido hacer todas las operaciones SQL.

La generación del scv es de una aplicación aparte y viene de una tabla de visual foxpro

Saludos.
Mauricio

>
> 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.
>
> Anticipo mis agradecimientos
>
> Mauricio
>
> Tablas
>
> CREATE TABLE bodegas  /*la tabla es mucho más ancha*/
>
> (
>
>  bodega character varying(3) NOT NULL,
>  item character varying(15) NOT NULL,
>  buffer numeric(12,3) DEFAULT 0,
>  pedidos_clientes numeric(12,3) DEFAULT 0,
>  comprometido_pedido numeric(10,3) DEFAULT 0,
>  CONSTRAINT pk_bodegas PRIMARY KEY (bodega, item),
>  CONSTRAINT fk_bodegas_id_bodegas FOREIGN KEY (bodega)
>  REFERENCES id_bodegas (bodega) MATCH FULL
>      ON UPDATE NO ACTION ON DELETE NO ACTION,
>  CONSTRAINT fk_bodegas_item FOREIGN KEY (item)
>      REFERENCES items (item) MATCH SIMPLE
>      ON UPDATE NO ACTION ON DELETE NO ACTION
> )
>
> CREATE INDEX idx_bodegas_bodega_fecha
>  ON bodegas
>  USING btree
>  (bodega COLLATE pg_catalog."default", fecha_conteo);
> CREATE INDEX idx_bodegas_item_bodega
>  ON bodegas
>  USING btree
>  (item COLLATE pg_catalog."default", bodega COLLATE pg_catalog."default");
>
> 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.
>
> 
>
> 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)
> )
>
> 
> 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;
>
>