[GENERAL] Decrease the time required function

[Karel Riverón]

Hi everyone,

I have a PL/pgSQL function that it takes 4 seconds to execute. This is 
my function:

CREATE OR REPLACE FUNCTION listarcasosrecuperados(escenario_id integer)
  RETURNS SETOF caso_real AS
$BODY$

DECLARE

    criterios CURSOR FOR SELECT * FROM criterio;

    casos_reales CURSOR FOR SELECT * FROM caso_real;

    sum_impactos NUMERIC DEFAULT 0;

    sum_impacto_modulo NUMERIC DEFAULT 0;

    impacto NUMERIC DEFAULT 0;

    valor_caso_real_criterio NUMERIC DEFAULT 0;

    valor_caso_escenario_criterio NUMERIC DEFAULT 0;

    s NUMERIC DEFAULT 0.0;

    c RECORD;

    cr RECORD;

    crc RECORD;

    cec RECORD;

    casos_escenarios_criterios RECORD;

    casos_reales_criterios RECORD;

BEGIN

/*
 * RECORRER CURSOR DE CRITERIOS Y
 * SUMATORIA DE LOS IMPACTOS DE LOS CRITERIOS
 */
OPEN criterios;

LOOP FETCH criterios into c;

IF NOT FOUND THEN
    EXIT;
ELSE
    sum_impactos := sum_impactos + c.impacto;
END IF;

END LOOP;

CLOSE criterios;

/*
 * OBTENER CRITERIOS DEL CASO ESCENARIO PASADO POR PARAMETRO
 */

SELECT * INTO casos_escenarios_criterios FROM caso_escenario_criterio 
WHERE caso_escenario_id = $1;

/*
 * RECORRER CURSOR DE CASOS REALES *
 */
BEGIN
OPEN casos_reales;

LOOP FETCH casos_reales into cr;

IF NOT FOUND THEN
    EXIT;
ELSE
    sum_impacto_modulo := 0;

    impacto := 0;

    valor_caso_real_criterio := 0;

    valor_caso_escenario_criterio := 0;

    /*
    * OBTENER CRITERIOS DEL CASO REAL EN CUESTIÓN
    */

    SELECT * INTO casos_reales_criterios FROM caso_real_criterio WHERE 
caso_real_id = cr.id;

    OPEN criterios;

    LOOP FETCH criterios into c;

    IF NOT FOUND THEN
        EXIT;
    ELSE

        SELECT c_r_c.id, valor INTO crc
        FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_
        WHERE c_.id = c_r_c.criterio_id
        AND c_.id = c.id
        AND c_r_c.caso_real_id = c_r.id
        AND c_r.id = cr.id;

        valor_caso_real_criterio := crc.valor;

        SELECT c_e_c.id, valor INTO cec
        FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_
        WHERE c_.id = c_e_c.criterio_id
        AND c_.id = c.id
        AND c_e_c.caso_escenario_id = c_e.id
        AND c_e.id = escenario_id;

        valor_caso_escenario_criterio := cec.valor;

        impacto := c.impacto;

        sum_impacto_modulo := sum_impacto_modulo + impacto * (1 - 
abs(valor_caso_real_criterio - valor_caso_escenario_criterio)/5);

    END IF;

    END LOOP;

    CLOSE criterios;

    s := sum_impacto_modulo / sum_impactos;

    IF s >= 0.75 THEN
           RETURN NEXT cr;
        END IF;

END IF;

END LOOP;

CLOSE casos_reales;

END;

END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION listarcasosrecuperados(integer)
  OWNER TO postgres;

I need to decrease the time required function. Please, anyone helpme.

Regards, Karel Riverón
Students Scientific Council
Informatics Science University