Select in FOR LOOP Performance - Mailing list pgsql-performance

    I face problem when running the following pgplsql
function. The problem is it takes more than 24hours to
complete
    the calculation. The EMP table has about 200,000
records. I execute the function through psql  "select
calculate()";
    (There is no cyclic link inside the data).

    Computer used: IBM xSeries 225, RAM 1GB, SCSI 36GB
    O/S : RedHat Linux Enterprise 3.0 AS
    PostgreSQL version 8.0.1
    fsync=false

    I would very appreciate if anyone can help to find
out what the problem is, or any others way to improve
the performance
    of the function.

    Is there any difference between select in FOR LOOP
with CURSOR in term of performance ?

    EMP Table
        GEN  char(3),
        CODE varchar(20),
        PARENT varchar(20),
        POSITION INT4 DEFAULT 0,
        PG NUMERIC(15,2) DEFAULT 0,
        P  NUMERIC(15,2) DEFAULT 0,
        QUA CHAR(1) DEFAULT '0',
    .
    .
    .
       create index EMP_GEN on EMP (GEN);
       create index EMP_CODE on EMP (CODE);
       create index EMP_PARENT on PARENT (PARENT);

    Sample EMP DATA:
  GEN   CODE   PARENT     POSITION    P    PG   QUA
  ===============================================
  000   A001   ****       3           100   0   '1'
  001   A002   A001       2            50   0   '1'
  001   A003   A001       1            50   0   '1'
  001   A004   A001       1            20   0   '1'
  002   A005   A003       2            20   0   '1'
  002   A006   A004       3            30   0   '1'
       ...
       ...


    for vTMP_ROW in select CODE,PARENT,POSITION from
EMP order by GEN desc loop
        vCODE     := vTMP_ROW.CODE;
        vPARENT   := vTMP_ROW.PARENT;
        nPOSITION := vTMP_ROW.POSITION;

        update EMP set PG=PG+P where CODE = vCODE;

        select into vCURR_ROW PG,POSITION from EMP
where CODE = vCODE;

        nPG       := vCURR_ROW.PG;
        nPOSITION := vCURR_ROW.POSITION;

        vUPL := vPARENT;

        loop
           select into vUPL_ROW
CODE,PARENT,POSITION,P,QUA from EMP where CODE = vUPL;
           if found then
              if vUPL_ROW.POSITION > nPOSITION and
vUPL_ROW.QUA = ''1'' then
                 update EMP set PG=PG+nPG where CODE =
vUPL;
                 exit;
              end if;
           else
              exit;
           end if;
           vUPL := vUPL_ROW.PARENT;
        end loop;
    end loop;

    .
    .
    .

Thank You





__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/