Re: [GENERAL] Indexes and loops - Mailing list pgsql-general

Hello.

I have a few questions:

1)  JobStatusTest1 function has only one request and JobStatusTest2 function has as many as six requests.

Why function JobStatusTest2 is faster? 

JobStatusTest1 : 981.596 ms

JobStatusTest2 : 849.133 ms

2) Two functions JobStatusTest3 and JobStatusTest4 perform the same actions. But one of the function performs the same steps in the cycle.

Why is the speed of the functions so incredibly much different?

JobStatusTest3 : 1430.777 ms

JobStatusTest4 : 2.386 ms

best
Arsen Arutyunyan
=========================================================================================

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);

Hello.
I have a few questions:
1)  JobStatusTest1 function has only one request and JobStatusTest2 function has as many as six requests.
Why function JobStatusTest2 is faster? 

JobStatusTest1 : 981.596 ms
JobStatusTest2 : 849.133 ms

2) Two functions JobStatusTest3 and JobStatusTest4 perform the same actions. But one of the function performs the same steps in the cycle.
Why is the speed of the functions so incredibly much different?

JobStatusTest3 : 1430.777 ms
JobStatusTest4 : 2.386 ms

best
Arsen Arutyunyan