Re: Baja performance en inserts masivos con bytea

Fri, 09 Jun 2023 09:24:52 -0700

select data
    from pg_catalog.pg_largeobject
    where loid = l_oid
    order by pageno

Prueba a quitar ese  "order by pageno" y revisa si obtienes los datos correctos. Para cada insert haces un orderby de todos los registros y esto de te hace demorar mucho.


De: guillermo...@gmail.com
Enviados: 9 de junio de 2023 10:58
Para: pgsql-es-ay...@postgresql.org
Asunto: Baja performance en inserts masivos con bytea

Hola amigos cómo están? les pido ayuda, tengo una muy baja performance en un proceso masivo que tengo que hacer, lamentablemente es postgres 9.2 ya que dependemos de un 3ro.

Entiendo si por la versión no pueden o no quieren aportar.

Cómo los datos originales tenían una columna lo (large object) los tengo que pasar a bytea, para eso utilizo la función:
CREATE OR REPLACE FUNCTION public.bytea_import(IN l_oid oid, OUT p_result bytea) RETURNS bytea AS
$BODY$
declare
 r record;
begin
 p_result := E'\\x';
 for r in ( select data
    from pg_catalog.pg_largeobject
    where loid = l_oid
    order by pageno ) loop
   p_result = p_result || r.data;
 end loop;
end;
$BODY$
LANGUAGE plpgsql;

El explain de la query dentro de la función es:
"Index Scan using pg_largeobject_loid_pn_index on pg_largeobject  (cost=0.00..1307.84 rows=2238 width=1093)"
"  Index Cond: (loid = 123123123::oid)"

Creo una tabla similar a la original solo que la columna de tipo lo, ahora es bytea y luego recorro la tabla gande con una columna de tipo lo para convertirla en bytea , lo hago con la siguiente función:
CREATE OR REPLACE FUNCTION insertar_tca() RETURNS VOID AS $$
DECLARE
   ini_oid INT;
   min_oid INT;
   max_oid INT;
   batch_size INT := 10000; -- Tamaño del lote para cada actualización
BEGIN
   SELECT MIN(oid), MAX(oid) INTO min_oid, max_oid FROM tca;
   ini_oid := min_oid;

   WHILE min_oid <= max_oid
   LOOP
INSERT INTO tca_bytea
SELECT org_codigo, tcc_codigo, hca_numero, dac_codigo, hac_numero, hca_anio,
      hac_anio, bytea_import(tca_texto)
FROM tca
WHERE oid >= min_oid AND oid < min_oid + batch_size;
RAISE NOTICE '[%] Registros actualizados tca desde % hasta % - Total %', current_timestamp,min_oid, min_oid + batch_size, min_oid + batch_size - ini_oid;

min_oid := min_oid + batch_size;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

Tengo un índice creado en la tabla origen por la columna oid y el explain del select principal de esa función es:
"Bitmap Heap Scan on tca  (cost=171.68..14074.21 rows=12854 width=30)"
"  Recheck Cond: ((oid >= 123123123::oid) AND (oid < 123133123::oid))"
"  ->  Bitmap Index Scan on idx_oid_tca  (cost=0.00..168.47 rows=12854 width=0)"
"        Index Cond: ((oid >= 123123123::oid) AND (oid < 123133123::oid))"


El problema es que demora muchísimo!!! para hacer la primera vuelta de ciclo, es decir los primeros 10000 registros demoró poco mas de 1 hora y la tabla tiene 2.5 millones de registros :-(
He probado también de hacerlo con una columna adicional en la misma tabla y con sentencia UPDATE, también probé de hacerlo sin particionarlo, en todos los casos va demasiado lento.

Estoy haciendolo en un server linux con 128Gb de RAM y 32 nucleos y algunos de los parámetros de conf son:
# Memory Configuration
shared_buffers = 512MB
effective_cache_size = 96GB
work_mem = 459MB
maintenance_work_mem = 6GB

# Checkpoint Related Configuration
checkpoint_completion_target = 0.9
wal_buffers = -1
checkpoint_segments = 16

# Network Related Configuration
listen_addresses = '*'
max_connections = 100

# Storage Configuration
random_page_cost = 1.1
effective_io_concurrency = 200

Les agradezco mucho si me pueden tirar ideas o comentarios de que puede estar pasando.
Abrazo

Reply via email to