SQL: inner join same table slow performance-CodePudding

I have the following query:

select distinct on ("checkinout"."pin", "dia") 
"checkinout"."id", "checkinout"."pin", "checkinout"."SN", 
to_char(checkinout.checktime, 'YYYY-MM-DD') AS dia, 
"userinfo"."name", "userinfo"."lastname", 
"personnel_positions"."name" as "cargo", 
"departments"."DeptName" as "departamento", 
"checkin"."checktime" as "checktime_in", 
"checkout"."checktime" as "checktime_out" 
from "checkinout" 
inner join "userinfo" 
    on "checkinout"."pin" = "userinfo"."badgenumber" 
inner join "departments" 
    on "departments"."DeptID" = "userinfo"."defaultdeptid" 
    and "departments"."supdeptid" in (61, 65) 
inner join "iclock" 
    on "checkinout"."sn_name" = "iclock"."sn" 
    and "iclock"."id" in (13, 19, 30, 31) 
inner join "checkinout" as "checkout" 
    on "checkinout"."pin" = checkout.pin 
    and to_char(checkout.checktime, 'YYYY-MM-DD hh24:mi') > to_char(checkinout.checktime, 'YYYY-MM-DD hh24:mi') 
    and extract(epoch from (checkout.checktime::timestamp - checkinout.checktime::timestamp)) < 61200 
inner join "checkinout" as "checkin" 
    on "checkinout"."pin" = checkin.pin 
    and to_char(checkin.checktime, 'YYYY-MM-DD hh24:mi') < to_char(checkout.checktime, 'YYYY-MM-DD hh24:mi') 
    and to_char(checkin.checktime, 'YYYY-MM-DD hh24:mi') >= to_char(checkinout.checktime, 'YYYY-MM-DD hh24:mi') 
    and extract(epoch from (checkin.checktime::timestamp - checkinout.checktime::timestamp)) < 61200 
left join "personnel_positions" 
    on "userinfo"."position_id" = "personnel_positions"."id" 
where "checkinout"."checktime" BETWEEN '2022-02-01 00:00:00-04' AND '2022-02-15 23:59:00-04'
and "departments"."supdeptid" = 65;

The inner join is on the same table twice. This seems to make the query take forever.

inner join "checkinout" as "checkout" 
    on "checkinout"."pin" = checkout.pin 
    and to_char(checkout.checktime, 'YYYY-MM-DD hh24:mi') > to_char(checkinout.checktime, 'YYYY-MM-DD hh24:mi') 
    and extract(epoch from (checkout.checktime::timestamp - checkinout.checktime::timestamp)) < 61200 
inner join "checkinout" as "checkin" 
    on "checkinout"."pin" = checkin.pin 
    and to_char(checkin.checktime, 'YYYY-MM-DD hh24:mi') < to_char(checkout.checktime, 'YYYY-MM-DD hh24:mi') 
    and to_char(checkin.checktime, 'YYYY-MM-DD hh24:mi') >= to_char(checkinout.checktime, 'YYYY-MM-DD hh24:mi') 
    and extract(epoch from (checkin.checktime::timestamp - checkinout.checktime::timestamp)) < 61200

The truth is that I can't think of another way to calculate the parameters - for the inner join - to the same table.

Any ideas?

CodePudding user response：

Add an index that helps the DBMS do the join quickly:

create index idx on checkinout(pin, checktime);

With this index the DBMS can look for rows with the same pin and even have the checktime at hand to do the calcaulation needed for the join.

As you said there aren't any indexes on that table, I'd suggest you also add an index to find the rows where "checkinout"."checktime" BETWEEN '2022-02-01 00:00:00-04' AND '2022-02-15 23:59:00-04':

create index idx2 on checkinout(checktime, pin, sn_name);

Here we offer the DBMS an index to find rows in the given time range and give it the pin at hand that it'll need to self join the table. I also added sn_name for the join to the iclock table.

CodePudding user response：

Creating an index will be helpful for making the query faster. So, create an index on the joining condition and where clause.

Syntax:

create index index_name on table_name (column_name1, column_name2)