Re: [pgsql-es-ayuda] [pgsql-es-ayuda] Función con select se ejecutar muy lenta comparada con la ejecución de select fuera de la función

Ante todo quiero agradecer los aportes y el tiempo desinteresado de los
integrantes de la lista en responder estos mensajes, entiendo que tenéis
razón y que al final el exceso de tiempo es tan sólo provocado por el
overhead de meter la consulta dentro de una función. Revisaremos el
diseño cómo sugiere Alvaro por supuesto, aunque he de decir que a veces
las cosas no son tan evidentes, ya que en el foro intentamos sintetizar.
Sólo para los que se encuentren en una situación parecida les indico en
profundidad el trabajo a realizar.

Realmente lo que estamos intentado es migrar hacia Postgresql una
datawarehouse bastante enorme que está en Oracle, para ello estamos
utilizando una herramienta Etl que está escriba en Java, el paso final
es rellenar una tabla en Postgres 9.6.1 que se llamará tickets, en la
que se guardan todos los tickets de venta de una cadena de supermercados
(millones y millones de transacciones), dicha tabla que soportará todos
estos tickets hay que rellenarla juntanto datos de distintas fuentes:
web services, excel, tablas Oracle, ficheros planos, otras bases de
datos postgresql, etc ..... casi todo lo que os podáis imaginar, de cada
uno de estas fuentes se extrae un dato y luego se juntan todos para
conformar un registro que será el guardado en la tabla tickets que está
en el nuevo Postgres datawarehouse. El programa en Etl es muy complejo y
el extraer, conformar y guardar toda esa información de tantos millones
de registros y fuentes de datos tan diversas hay que hacerlo en el
"menor" tiempo posible. Todas las partes del Etl están desarrolladas
pero al llegar a la parte en la cuál teníamos que "atacar" a un Postgres
del cuál necesitamos unos datos, pensamos que en vez de desarrollar una
a una todas las consultas necesarias en el Etl con Java además del
tratamiento de estos datos, sería mejor dejárselo al propio Postgres
metiéndolo todo en una función, ¿quién mejor que él para realizarlo?, la
secuencia sería esta:

- Leemos secuencialmente la tabla matriz en Oracle que contiene los
tickets de venta originales (aquí es dónde ponía la cifra en anteriores
emails de 800.000 registros y hay que rellenar los campos que faltan de
todas estas filas por eso la función se ejecuta tantas veces, una por
cada registro, pero en realidad la situación es mucho peor, estos
800.000 son sólo un día de venta, era por acotar un poco en el ejemplo
pero realmente queremos pasar 5 años)

- Por cada uno de estos registros el Etl lanza varias fases: llama a
distintos web services para obtener ciertos datos que serán añadidos
cómo campos en el registro que se está procesando actualmente, ficheros
excel, ficheros planos, y por último unos datos que están en tablas de
otro Postgresql distinto.

- A la hora de llamar a este otro postgres necesitamos 10
identificadores que obtenemos de hacer 10 consultas a 10 tablas: por
ejemplo el identificador de articulo de la tabla d_articulos (es la
select de ejemplo que puse en los anteriores emails), el de cliente, el
de tienda, etc, etc, además de hacer ciertas operaciones de cálculo con
ciertos campos que están en estos registros. Aquí es dónde decidimos que
lo mejor sería meter estas 10 consultas y su tratamiento de datos en una
sola función en Postgres y luego simplemente llamar a esa función desde
el Etl en Java para cada registro procesado que viene de Oracle, así
obtendríamos esos datos que nos faltaban para conformar el registro de
una forma más limpia y creíamos que eficiente.

- Después de esto y con todos los campos del registro que queremos ya
rellenos es cuándo se graba el registro en la tabla tickets del nuevo
datawarehouse en Postgres

Cómo podréis apreciar aunque los tiempos de los que hablábamos en los
anteriores emails son muy bajitos ya que hablábamos de ejemplos, esas
diferencias sobre tal volumen de procesamiento de registros y encima con
un tiempo limitado para hacerlo que impone el cliente nos supone
bastante, y de ahí el intento de reducirlo a la mínima expresión que era
el tiempo que nos devolvía las consultas "directamente desde la shell".
Cómo dije antes, ¡¡claro!! que podemos meter todas estas consultas y el
procesamiento en el Etl y hacerlas una a una desde Java y con
procesamiento de los datos incluido, pero nos pareció mucho más
engorroso y feo que simplemente llamar a una función en Postgres y
"voilá" todos nuestros datos listos.

Iremos moviendo las consultas y el procesamiento de datos al Etl, si por
el camino encontramos otra solución la expondré al foro por si alguna
vez alguien se ve en el mismo aprieto.

Saludos a todos y agradecido por el interés.

El 22-12-2016 15:15, Alvaro Herrera escribió:

> "José Alberto Sánchez Nieto (Trabajo)" escribió:
>
>> Esta función es llamada desde un programa en java, el caso es que se ejecuta entre 400.000 y 800.000 veces y claro, aunque las diferencias tal y cómo se han mostrado son pocas, al producirse en tantas ejecuciones sí que son muy significativas.
>
> Esto es mal diseño. Considera agregar otra función que en lugar de
> retornar los detalles de los registros uno a uno, te devuelva los
> 400.000 u 800.000 de una sola vez, para evitar el excesivo sobrecosto de
> invocar plpgsql tantas veces.
>
>> - Poner dentro de la función la select sin parámetros poniendo el valor directamente y me tarda más ó menos igual (0.230)
>
>> - Lo que sí me ha funcionado es la sugerencia de Alvaro, desde la shell poner el PREPARE y luego utilizar el EXPLAIN ANALIZE EXECUTE, con esto sí que los tiempos son exactamente iguales que lanzar la consulta desde la shell (0.045)
>
> Ambos resultados llevan a la misma conclusión: las consultas no son
> lentas, el problema es el sobrecosto de meterlas en plpgsql. Ya te
> comentaron eso antes.