[SQL] Please advice on query optimization - Mailing list pgsql-sql

From Muhannad Shubita
Subject [SQL] Please advice on query optimization
Date
Msg-id CAJQCA5GrSczfLEhSgf+xV3xLQdUrnfsFSGrJ7yqCrfq2iPwt=g@mail.gmail.com
Whole thread Raw
Responses Re: [SQL] Please advice on query optimization  (Samed YILDIRIM <samed@reddoc.net>)
List pgsql-sql
Good day,

I have a table with biometric info about employees (let's call it bioemployee):

id serial not null -- primary key
employee_id  -- foreign key references employee table
day char(8) -- YY-MM-DD
sign_in TIMESTAMP
total_hours INTEGER

I want to display details grouped by day & employee Id, for example:


Day Employee Total-Hours First-Sign-In Last-Sign-In

20/4 emp1 4 8:22 3:25
21/4 emp1 7 9:00 4:11
21/4 emp2 2 11:00 01:11


I created a pgsql function to get the details through the below query:

select employee_id, day, sum(total_hours) as total_hours, (select name from employee where id = employee_id) as emp_name
from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;

now the problem is with getting First-Sign-In  &  Last-Sign-In per group (employee & day), I have currently implemented it in a FOR loop:

for RECORD in query LOOP
--First-Sign-In
select sign_in from bioemployee where employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in LIMIT 1;
--Last-Sign-In
select sign_in from bioemployee where employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in DESC LIMIT 1;
return next json_build_object(first_sign_in, last_sign_in, ..rest of details);
END LOOP

but If I had 100 employees over a span of 30 days, this would be 6000 queries inside the loop! which I am sure you would agree is an overkill


is there a better way to do this? 


Thanks.

pgsql-sql by date:

Previous
From: Alvin Díaz
Date:
Subject: Re: [SQL] Best way to store Master-Detail Data
Next
From: Samed YILDIRIM
Date:
Subject: Re: [SQL] Please advice on query optimization