Re: Indexes and loops

Hi

2016-12-27 19:05 GMT+01:00 Арсен Арутюнян <arutar(at)bk(dot)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
>
>