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