Greeting all,
I have a query that looks like:
EXPLAIN ANALYZE
SELECT empRec.lastName, empRec.FirstName
FROM schedXEmp
INNER JOIN empRec ON
empRec.selfManagerId = 1 and empRec.employeeNumber = schedXEmp.employeeNumber
WHERE schedXEmp.schedId = 22480 AND deactivatedTS > NOW ()
ORDER BY empRec.lastName, empRec.FirstName;
I am getting:
Sort (cost=633.26..633.27 rows=1 width=136) (actual time=570.691..570.692 rows=1 loops=1)"
Sort Key: emprec.lastname, emprec.firstname"
Sort Method: quicksort Memory: 25kB"
-> Nested Loop (cost=0.00..633.25 rows=1 width=136) (actual time=532.276..570.664 rows=1 loops=1)"
Join Filter: (schedxemp.employeenumber = emprec.employeenumber)"
Rows Removed by Join Filter: 106"
-> Seq Scan on emprec (cost=0.00..4.34 rows=1 width=144) (actual time=0.012..0.207 rows=107 loops=1)"
Filter: (selfmanagerid = 1)"
-> Seq Scan on schedxemp (cost=0.00..628.43 rows=39 width=8) (actual time=5.124..5.323 rows=1 loops=107)"
Filter: ((schedid = 22480) AND (deactivatedts > now()))"
Rows Removed by Filter: 23405"
Total runtime: 570.753 ms"
Although this lag might not seem important, it does cause an issue as the query is called about 250 in a single page, causing timeout.
Within the join, if I remove the empRec.selfManagerId = 1 and portion, then everything execute in less than 30ms, much better.
In my DDL I do have an index defined as:
CREATE INDEX emprec_sm_enum_ndx ON employeekeeper.emprec
USING btree (selfmanagerid, employeenumber);
empRec has very little data, 110 records. For now all values in empRec.selfManagerId = 1 (so not needed for now), things will change as pple join the application.
schedxemp is for a many-to-many relation. It holds a bit less than 25K records.
At this point I can't really think what I can try to isolate/solve the problem.
This index should help:
CREATE INDEX ON schedxemp (schedid, deactivatedts);