Query Speed

hi,

Sorry, actually the query is not a straight one and we
use a plpgsql function which is then called in java.

The following is the exact function we use:

----

CREATE FUNCTION loom1(date,date) RETURNS INTEGER As '

DECLARE

    c1 RECORD;
    c2 RECORD;

    from1 ALIAS FOR $1;
    to1 ALIAS FOR $2;

        prod_length REAL := 0;
    waste_loom REAL := 0;
    waste_cut REAL := 0;
        work_hrs REAL := 0;
        tot_waste REAL := 0;
        waste_per REAL := 0;

        nprod_length REAL := 0;
        nwaste_loom REAL := 0;
    nwaste_cut REAL := 0;
        nwork_hrs REAL := 0;
        ntot_waste REAL := 0;
        nwaste_per REAL := 0;
        opn VARCHAR(50);


BEGIN

    delete from vb_loom_report1;
        delete from vb_loom_abs_report1;
        lock table vb_loom_details;

-- c1 loop
FOR c1 IN select distinct(operator_code) from
vb_loom_details where date1>=from1 and date1<=to1 LOOP

        --  c2 loop
        FOR c2 IN select * from vb_loom_details where
date1>=from1 and date1<=to1 and
operator_code=c1.operator_code LOOP

    prod_length := prod_length + c2.production_length;
        waste_loom  := waste_loom  + c2.waste_in_loom;
        waste_cut   := waste_cut   +
c2.waste_in_cutting;
        work_hrs    := work_hrs    + c2.working_hours;

        nprod_length := nprod_length +
c2.production_length;
        nwaste_loom  := nwaste_loom  +
c2.waste_in_loom;
        nwaste_cut   := nwaste_cut   +
c2.waste_in_cutting;
        nwork_hrs    := nwork_hrs    +
c2.working_hours;

        END LOOP;  -- end c2 loop

        tot_waste   := tot_waste + (waste_loom +
waste_cut);
        waste_per   :=( (tot_waste/prod_length) / 1000
) * 100;

select operator_name into opn from vb_mas_operator
where section_name=''LOOM SECTION'' and
opcode=c1.operator_code;


insert into vb_loom_report1 values
(opn,round(work_hrs,2),round(prod_length,2),round(waste_loom/1000,2),round(waste_cut/1000,2),round(waste_per,2));


        prod_length:= 0;
        waste_loom := 0;
    waste_cut  := 0;
        work_hrs   := 0;
        tot_waste  := 0;
        waste_per  := 0;


END LOOP;  -- end c1 loop

        ntot_waste   := ntot_waste + (nwaste_loom +
nwaste_cut);
        nwaste_per   := ((ntot_waste/nprod_length) /
1000) * 100;

insert into vb_loom_abs_report1 values
(round(nwork_hrs,2),round(nprod_length,2),round(nwaste_loom/1000,2),round(nwaste_cut/1000,2),round(nwaste_per,2));

RETURN 1;

END;

' LANGUAGE 'plpgsql';

----
for which the time taken differs for the local machine
and a node. I have already indexed the table with the
date field. Also, what is 'VACUUM FULL ANALYZE'. I do
'VACUUM ANALYZE' and tried the query. But no
difference. Please do the needful. thanks.

bhuvana.


__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com