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
