Postgres while loop really slow

Question

I got this sproc where i get the volume depending on a "FlowVolume" it could be 1, 10, 100. The only way that I found to make this work is the current implementation, but is really slow. The other way is to have intervals in the "Timestamp" it could be every 12 seconds, every 2 mins or every 20 mins intervals.

CREATE OR REPLACE FUNCTION "getBLTPoints"(IN id integer)
RETURNS TABLE(times text, bio text, particles text) AS

$BODY$
DECLARE
currVol real;
currbio integer;
currpart integer;
times timestamp with time zone;
totalvol integer;
profileid integer;
analysisopt integer;
analysisvol integer;
counter integer;
BEGIN
--Getting the right profile to get the line,autosample,analysis volume
select "ProfileID"into profileid from "Samples" where "ID" = id;
--Getting the mode that the system was in when sample was done
select "optBaseVolume" into analysisopt from "Profiles" where "ID" = profileid;
if(analysisopt = 0) then
    analysisvol = 1;
elseif(analysisopt = 1) then
    analysisvol = 10;
else
    analysisvol = 100;
end if;
counter = analysisvol;
--Getting the totalvolume to run it in the while loop until it reaches the totalvolume
select floor("FlowVolume")::integer into totalvol from "Results" where "SampleID" = id order by "FlowVolume" desc limit 1;

WHILE counter <= totalvol LOOP
    RETURN QUERY
    select extract('epoch' from "Timestamp")::text,"BioAccumulated"::text, "TotalParticlesAccum"::text from 
    "Results" where "SampleID" = id and "FlowVolume" = counter; 

    counter = counter + analysisvol;

END LOOP;
END;

Pavel Stehule · Accepted Answer · 2014-09-09 20:31:27Z

2

using a RETURN QUERY inside any cycle cannot be fast. WHILE cycle is fast as your nested query is fast. But you can use significantly faster technique .. you can generate a counter series by function generate_series, and you can join this series to your "Result" table:

RETURN QUERY SELECT extract('epoch' FROM "Timestamp")::text,
                    "BioAccumulated"::text,
                    "TotalParticlesAccum"::text
                FROM "Result"
                     JOIN generate_series(analysisvol, totalvol, analysisvol) g(v)
                     ON "Result"."FlowVolume" = g.v
               WHERE "SampleID" = id;

answered Sep 9, 2014 at 20:31

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ubaldo Quintero Over a year ago

you lost me in the generate_series, can you elaborate on that please.

Collectives™ on Stack Overflow

Postgres while loop really slow

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related