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 <guillermo...@gmail.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 >
