Hi 2016-12-27 19:05 GMT+01:00 Арсен Арутюнян <aru...@bk.ru>:
> 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); > > > Use plpgsql profiler and you will see https://bitbucket.org/openscg/plprofiler Usually method described in JobStatusTest3 is pretty wrong - but there are exceptions everywhere. regards Pavel > 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 > >