CREATE TABLE test_job(id serial,primary key(id));
insert into test_job (id) values (1);
CREATE TABLE test_status(id serial,primary key(id));
insert into test_status (id) values (1),(2),(4),(8),(16),(32);
CREATE TABLE test_task(id serial,job_id integer references test_job on delete cascade,status_id integer references test_status on delete cascade,primary key(id));
CREATE INDEX CONCURRENTLY test_job_idx on test_job(id);
CREATE INDEX CONCURRENTLY test_status_idx on test_status(id);
CREATE INDEX CONCURRENTLY test_task_idx on test_task(id);
CREATE INDEX CONCURRENTLY test_task_idx_cnt1 on test_task(id,job_id) where status_id=1;
CREATE INDEX CONCURRENTLY test_task_idx_cnt2 on test_task(id,job_id) where status_id=2;
CREATE INDEX CONCURRENTLY test_task_idx_cnt3 on test_task(id,job_id) where status_id=4;
CREATE INDEX CONCURRENTLY test_task_idx_cnt4 on test_task(id,job_id) where status_id=8;
CREATE INDEX CONCURRENTLY test_task_idx_cnt5 on test_task(id,job_id) where status_id=16;
CREATE INDEX CONCURRENTLY test_task_idx_cnt6 on test_task(id,job_id) where status_id=32;


insert into test_task (id,job_id,status_id) values (generate_series(1,100000,1),1,1);
insert into test_task (id,job_id,status_id) values (generate_series(100001,600000,1),1,2);
insert into test_task (id,job_id,status_id) values (generate_series(600001,1000000,1),1,4);
insert into test_task (id,job_id,status_id) values (generate_series(1000001,1700000,1),1,8);
insert into test_task (id,job_id,status_id) values (generate_series(1700001,2500000,1),1,16);
insert into test_task (id,job_id,status_id) values (generate_series(2500001,3000000,1),1,32);


CREATE OR REPLACE FUNCTION JobStatusTest1(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
BEGIN
    FOR CurrentQuery IN select count(test_task.id) as counter, status_id from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID group by status_id LOOP
        raise notice 'Conter:% Status:%', CurrentQuery.counter, CurrentQuery.status_id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

explain analyze select * from JobStatusTest1(1);

CREATE OR REPLACE FUNCTION JobStatusTest2(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
counter integer;
BEGIN
    select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=1;
    raise notice 'Conter:% Status:%', counter, 1;
    select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=2;
    raise notice 'Conter:% Status:%', counter, 2;
    select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=4;
    raise notice 'Conter:% Status:%', counter, 4;
    select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=8;
    raise notice 'Conter:% Status:%', counter, 8;
    select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=16;
    raise notice 'Conter:% Status:%', counter, 16;
    select count(test_task.id) into counter from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=32;
    raise notice 'Conter:% Status:%', counter, 32;
END;
$$ LANGUAGE plpgsql;

explain analyze select * from JobStatusTest2(1);

CREATE OR REPLACE FUNCTION JobStatusTest3(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
taskid integer;
BEGIN
    FOR CurrentQuery IN select id from test_status LOOP
        SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=CurrentQuery.id ORDER BY test_task.id limit 1;
        raise notice 'TaskID:% Status:%', taskid, CurrentQuery.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

explain analyze select * from JobStatusTest3(1);

CREATE OR REPLACE FUNCTION JobStatusTest4(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
taskid integer;
BEGIN
      SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=1 ORDER BY test_task.id limit 1;
      raise notice 'TaskID:% Status:%', taskid, 1;
      SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=2 ORDER BY test_task.id limit 1;
      raise notice 'TaskID:% Status:%', taskid, 2;
      SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=4 ORDER BY test_task.id limit 1;
      raise notice 'TaskID:% Status:%', taskid, 4;
      SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=8 ORDER BY test_task.id limit 1;
      raise notice 'TaskID:% Status:%', taskid, 8;
      SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=16 ORDER BY test_task.id limit 1;
      raise notice 'TaskID:% Status:%', taskid, 16;
      SELECT test_task.id into taskid from test_job inner join test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and test_task.status_id=32 ORDER BY test_task.id limit 1;
      raise notice 'TaskID:% Status:%', taskid, 32;
END;
$$ LANGUAGE plpgsql;

explain analyze select * from JobStatusTest4(1);


