Re: Baja performance en inserts masivos con bytea

No puedes insertar los datos en el tipo de dato de destino ?

Evita funciones have todo por SQL las funciones te matan el performance siempre.

Regards,
Horacio Miranda

> On 9/06/2023, at 10:58 AM, Guillermo E. Villanueva <guillermovil(at)gmail(dot)com> wrote:
>
> 
> 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
>